One report from two tables

[Dr_Worm]

OK, here’s what I want to do,

In a database I’ve been asked to modify there are a number of tables. One of them is the main table which data is imported into from a csv file. It has information such as:
ProjectName, Number, Type, Scheme. This information is needed for a report, but is only needed in the report header. The detail part of the report needs to get it’s information from another table.

For example;
If the “type” field in the main table has the data “K7A” the report would fill out the detail section with the records in the table “K7A”,
If the “type” field in the main table has the data “K8C” the report would fill out the detail section with the records in the table “K8C”.

Can this be done?

I’m using Access 97. We will be upgrading later this year

 

[Andrew Fergus]

[Heavily edited given I didn't read the question correctly when I first answered]

Hi

Is all of the project detail held in differing tables? (i.e. one for each project) If so, then you might want to have a read of some of the recent posts regarding database normalisation and not using multiple tables to store similar groups of data.

How many tables are there?

A way around this problem might be to append all of the project detail data into one table and base your queries on the one 'master' table. If the data does not lend itself to this (i.e. differing formats and fields etc) then other than creating one query for each report and one report for each project, then I'm not sure what else you could do.

HTH, Andrew.

 

[SydneyGeek]

Following on from Andrew, let's assume that you can relate the data via key fields to build one large query with all the data for the report.
If you want to build reports based only on one key value such as Project Type, make that a parameter in the query. When you run the report you'll be prompted for the parameter, and the report will show with ontly that data.

OK, to build the report use the Wizard and group the records on the key field (Project Type in this example). As you go through the screens you can choose to sort by one or more fields, add subtotals and grand totals, and choose the layout. I usually go for Landscape, Left aligned.

Once the Wizard is finished, see if the result is vaguely what you need. If it is, you can tweak the design to get the aesthetics right, and play around with which headers to use. Down the track, you can build a simple search form where users pick the Project Type from a combo or list box, press a button which hides the form and launches the report (filtered on teh value of the combo / list box). You can then add code so that closing the report also closes the hidden form.

A bit vague, but more detail on tables / relationships would give more ammo to work with.

Denis