multiple table report

[dedub]

First post guys, hope you can help me out.

OK, I have 10 tables in a database,
all 10 have the same fields, 'last' and 'first'
each table has a different amount of records "names" in those fields
I want to print a report that has all 10 tables worth of names grouped by table.
Thats where it gets hard (at least for me) a query does no good because of the different amount of records in each table. I tried a union query and that does a good job grouping the names but then I loose the source... tables. Im stuck.
There has to be a way you can drag and drop a text.box onto a report in design view and tell it to pull from a specific table, then do that 9 more times.
thanks in advance

 

[Smart]

Create a query that selects the info you want from table1 then create a query as above but based on table 2 etc for all the tables
Create a report based on Query1 then create a report based on query 2 etc

Finally create another final report and add each of the above reports as a sub report to it

Or you could create a field on the report and in the data source use a dlookup on the table then create as many fields as you require and set the data source to the dlookup of table2 , table 3 etc

 

[dedub]

Hey Smart, that sure does help and Ill give it a shot. Thanks for your help and if it worked out Ill edit my post with a (RESOLVED)

take care

ds

 

[Pat Hartman]

You can include a "source" field in the union query:
Select "tbl1" As Source, fld1, fld2, fld3 From tbl1
Union Select "tbl2" As Source, fld1, fld2, fld3 From tbl2
Union Select "tbl3" As Source, fld1, fld2, fld3 From tbl3
Union Select "tbl4" As Source, fld1, fld2, fld3 From tbl4;

 

[dedub]

Hey Pat, with that many post it's no suprise you came up with that. I tried the Union query but I did not have the right options included. Thanks a million, it works!

ds

 

[KurtL]

I need to create a very similar report. How do i even create a union query. I am a complete noob to Access.

Thanks
Kurt

 

[Pat Hartman]

Open a new query. Close the table/query box that pops up without selecting anything. Change the query type (query menu) to SQL specific/Union.