Report Idea

[RonnieDobbs]

I need some help with a report. Basically it is coming from one big table, which has assignments from different locations. I need to query the information for each location and display the results in a report. The report should just have sums of different criteria queries. For instance:

STD feestandard? etc. etc.
France-- 2 9 8 5
Germany-- 9 10 9 4

Would I have to make different criteria queries for each location? I have 20 some odd locations???

 

[Crowley16]

well, it's perfectly possible to put 20+ different criterias in a query... but I suppose you can also make a list of the criterias in a table and use that...

the sql code for the report should look something like:

SELECT location, std, feestandard... FROM tblName WHERE location = 'France' OR location = 'Germany'...

if I'm understanding your requirements right that is...

Procrastinate Now!

 

[scriverb]

You could create a UNION query which combines multiple Selects with their own criteria for each location. The resulting recordset would then be taken into your report for printing and totalying.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[RonnieDobbs]

A question on these union queries...Is there a way to enter an assignment number, and have access query 6 tables (which all have their own seperate input forms) providing information about that assignment.

To clarify, An assignment could be put on hold, the resumed, then completed. This would be entered in three different forms/tables. Then I would like to query this???

Thanks, Bob, is that a MSU Spartan? Go blue Go, just kidding, went to CMU

 

[scriverb]

Sure multiple tables may be linked to each other using Primary Keys and foreign keys or matching fields in the secondary tables. These links should be setup as Left joins to the master table so that a record is not required in the secondary table. A record may not be there so you don't want the row to be eliminated with an inner join.

As for the criteria, each of the selects may reference the selection criteria from a form. Just leave the form in an open state and execute the UNION query from a button on the form. Then in the criteria portion of the query make reference to the form field with the following format:

[red]FORMS![frmName]![ControlName][/red]

Do that in each of the individual selects and you should be all set.

Post back if you need further information.

Go Chips but not next week.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]