Data from multiple queries

  • Thread starter Thread starter Scruff57
  • Start date Start date
S

Scruff57

I have 5 different queries that contain employee data by date and results in a calculated field. I want to generate a report that has each of these calculated field per employee. I tried putting together a report with just these calculated fields, and it didn't work. I was thinking of perhaps trying to create a union query that combines all the calculated fields. my other option would be to scrap the calculated fields in the queries and just put the data on the report and let the calculations be done on the report. Anyway below is an example of what I have, is a union query the way to go is there a better way?

tableName
employeeID employeeName

table1
employeeID date data1 data2

table2
employeeID date data3 data4

table3
employeeName date data5 data6

query1 generates for date range
employeeID data1 data2 data1/data2 (calculated)

query2 generates for date range
employeeID data3 data4 data3/data4 (calculated)

query3 generates for date range
employeeName data5 data6 data5/data6 (calculated)

Desired Result
Date EmployeeID EmployeeName calc field from query1 calc field from query2 calc field from query3

Is it possible?
 
I have 5 different queries that contain employee data by date and results in a calculated field. I want to generate a report that has each of these calculated field per employee. I tried putting together a report with just these calculated fields, and it didn't work. I was thinking of perhaps trying to create a union query that combines all the calculated fields. my other option would be to scrap the calculated fields in the queries and just put the data on the report and let the calculations be done on the report.
Click to expand...

I'd suggest the latter; Reports have a quite powerful "layering"
feature using the Sorting and Grouping dialog. You can have details,
sub-subtotals, subtotals, and grand totals all on the same report with
only one query by setting appropriate grouping levels. It's usually
not necessary to come up with one grand master query which does all
the calculations for you!
 
Back
Top