Dec 15 2005, 09:48 AM
I have never used a union query and I'm not sure this is the route I need to go for what I want.
I have 3 tables.
1) tbl_medical: Customer, Number of EE's, Number of Lives, Total Number of EE's/Deps
2) tbl_dental: Customer, Number of EE's, Number of Lives, Total Number of EE's/Deps
3) tbl_combined: Customer, Number of EE's, Number of Lives, Total Number of EE's/Deps
I need to create a query that will list each customer and show the number of EE's, Number of LIves and Total Number of EE's/Deps.
Problem is that each table does not have the equal amount of customers.
Any help/suggestions will be greatly apprciated.
Dec 15 2005, 09:58 AM
What matters is not the number of records in each table. What matters is that you have the same number of fields in each select statement and that corresponding fields in each select statement have the same type. You are essentially taking records from different physical tables and putting them into the same logical table, so this makes sense.
To do this:
SELECT blah blah blah FROM tbl_medical
SELECT blah blah blah FROM tbl_dental
SELECT blah blah blah FROM tbl_combined
The column headings for the query will be taken from the first select statement. I have found it easier to use the same field names thruout the Union query.
I have also found it a LOT easier to use the Query Design to develop the SQL statements and copy and paste them into the SQL window for the UNION query.
Realize that unless you put a constant into each select statement, you will not know from which table each record comes.
You might also want to look into basing subreports on the different queries.
Edited by: fkegley on Thu Dec 15 9:59:26 EST 2005.
Dec 15 2005, 10:05 AM
Thank you very much. I'll try that.
Dec 15 2005, 11:25 AM
DUSTWEB, you're welcome. I am glad I was able to help.