Hello all. My manager wants me to create a summary report from information in 5 different tables. The main key for all tables are Status_number.

1. Requirement table - count of records
2. Defects table - count of records (if any exists)
3. Metrics - sum of two hour fields
4. Peer Review - count of records
5. Time - sum of hours worked on status (if any exists)

I could probably get a query with 3 of the tables. But I couldn't combine the Defects and Time. Obviously I need almost a merge like query where I can take 1 query, merge with maybe 2 other queries to create this super query.

But is this possible in Access 97?

Another question I had is can I use a data file as a Record Source in a report?

Thanks

Create 5 queries that do the sum() or Count() results by Status_number. Then create a sixth query that joins the other 5 on status_number. Since not all of the tables will produce results for all values of Status_number, you'll need to use left joins. If none of the queries can be guarenteed to return a row for every status_number, you'll need to include the status_table definition table as the left-most table in the sixth query.

StatusTable Left Join qryRequirements
StatusTable Left Join qryDefects
StatusTable Left Join qryMetrics
StatusTable Left Join qryPeerReview
StatusTable Left Join qryTime