Pulling data from several identical tables and returning the results to one big query (1 Viewer)

Hi folks, happy Friday 13th.

I'm hoping you can help me build a query that will return data from several identical tables.
I have six tables each containing the following fields:

ID - SalesExec - Venue - CustDOB - AppDate
1 - John Doe - New York - 01/01/1984 - 01/01/2002

Click to expand...

I use the following query to pull data from one of the tables:

SELECT M.ID, MV.Venue, LTRIM(MS.Forename) + ' ' + LTRIM(MS.Surname) AS Name, AR1.Range AS Age1, AR2.Range AS Age2, M.AppDate
FROM dbo.TableOne_Main M CROSS JOIN
dbo.tblMain_AgeRanges_A AR1 CROSS JOIN
dbo.tblMain_AgeRanges_B AR2 INNER JOIN
dbo.tblMainVenue MV ON M.Venue = MV.ID INNER JOIN
dbo.tblMainSalesExec MS ON M.SalesExec = MS.ID
WHERE (DATEDIFF(yyyy, M.CustDOB, GETDATE()) BETWEEN AR1.Low AND AR1.High) AND (DATEDIFF(yyyy, M.CustDOB, GETDATE()) BETWEEN AR2.Low AND AR2.High)
GROUP BY M.ID, MV.Venue, LTRIM(MS.Forename) + ' ' + LTRIM(MS.Surname), AR1.Range, AR2.Range, M.AppDate

Click to expand...

This query returns the following results (this is just one sample record):

ID - SalesExec - Venue - Age1 - Age2 - AppDate
=======================================
1 - John Doe - New York - 18/25 - 18/60 - 01/01/2002

Click to expand...

The AR1.Range, AR2.Range parts of the query group the CustDOB field into age ranges. In the sample result above (John Doe, aged 20) has been grouped into the 18/25 range and the 18/60 range.

As you can see, the query is quite simple, however, I have to run this query separately for each different table and then manually copy and paste the results into a spreadsheet!
I now need to build a query that will pull the data from all 6 tables and return the results in one big query. Can you guys help!?
Let me know if you need any further details about the query above.

Any help greatly appreciated. I'm chin deep in this one.

Joining the tables might not make sense. You could create your six individual queries and save them. Then write a union query to combine the results all together. If you want to get rid of the six individual queries, copy the SQL textof each one, replace the reference to the query name with the SQL text in the saved union query.

dcx693 said:

Joining the tables might not make sense. You could create your six individual queries and save them. Then write a union query to combine the results all together. If you want to get rid of the six individual queries, copy the SQL textof each one, replace the reference to the query name with the SQL text in the saved union query.

Click to expand...

Thanks Dcx, I managed to get this to work.

can I get this new UNION query to return an extra field which will show the table each record is comming from.

Example:

qryUnion_AllTalbles

ID - SalesExec - Venue - Age1 - Age2 - AppDate - Table
=====================================
1 - John Doe - New York - 18/25 - 18/60 - 01/01/2002 - Table1
1 - Jane Doe - Paris - 18/25 - 18/60 - 01/01/2002 - Table2
1 - George Smith - Dublin - 18/25 - 18/60 - 01/01/2002 - Table3

etc etc


Any pointers?

Sure. When you do a SELECT statement, you can pick from the available fields in the table/query or you can add your own values.

For example:
SELECT *, "Table1" AS ExtraField FROM Table1;

That will add a field to your query with "Table1" as the value.

Just integrate that type of statement into your union query.

Perfect, thanks dcx.

Hi.

I need to do the sam ething as you, but need more explicit details as too the union query.

There are 2 dozen tables, with 13 fields each. 12 field names will be the same in multiple tables, but not all (these are wage quarter named, ex 1/2006, 2/2006...).

I need to query by the common ssn and enter a field name , such as 2/2006, and whereever it exists, pull the values up.

Thanks in advance.

Russ