HELP, query to list multiple records in a list from multiple queries,

[andygriff]

Good Day

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

what I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.



at the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work, but i'm sure theres a better way

any help would be greatly appreciated

[June7]

If Project is the 1 side of a 1-to-many relationship with each of the other tables, then the subreport approach is proper.

[andygriff]

that is correct, the project table is the key to all the other tables, i'm experimenting with a union query, would this work

[June7]

No, UNION is not appropriate for parent/child records. You have applied the correct approach.

[andygriff]

that's not want I wanted to hear, at the moment ive got a union query listing all records I need, what I have done is taken out the project table from the query and just linked the rest with union all, seems to be working

[June7]

Don't understand. UNION query is used to combine like data. How is staff data like markup data?

[andygriff]

they are both linked to the same project

[June7]

That doesn't mean the data is alike. UNION is used to combine up to 50 tables with like data. Example:

SELECT AccountNo, Amount, TransDate, "Cash" AS TransType FROM tblCash
UNION SELECT AccountNo, Amount, TransDate, "CC" FROM tblCC;