Query Help

[carbon_13]

I need to combine multiple queries into one possible query so that I may place these queries into a single report. How would I go about doing this.
The number of queries to be combined are 12. About six of these quereies perform a count function while filtering for certain material, and the other queries perform average and min functions on certain records.

I have another prolbem (minor) when the query does the average and it has decimal number that I do not want. When in design view I click on the field and properties and choose 0 decimal but it still shows up. Any thoughts.

I have been looking for answers but can't really find any. By the way I am no expert in database. Any help would be good.
Thanks

 

[neileg]

I suspect that at least some of what you need can be done in the report, grouping and summing and so on. You can also format the data in the report and set the number of decimal places.

 

[apr pillai]

I am not sure about the data structure that you are working on, i.e. the output of your queries like number of records and whether all of them have a common field to join etc. but try this changing your original plan to link your report to a combined query.

Instead create two Tables, one Temporary Table and the other one for linking directly to your Report. Plan the output fields for the report and create equal field names for both tables. Add (append) the individual Query (all 12 of them one by one) output to the temporary table with its corresponding field names.

Finaly, create a Total Query on the temporary table Grouping the values on the common field like partnumber and summing up the values of other fields and add the output to the Table planned to link with the Report. Design your Report on this Table and maintain it as a report table. Even if some field on the Report Table end up with no values when you run the Queries later on your Report will work.

You can automate this action by putting the append queries into a macro. Don't forget to create deletion queries for the Tables to remove records of earlier runs before appending records, if you plan for re-runs.

http://msaccesstips.com