By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Query to get start date and end date from mulitple tables

P: 3
hello, I have 5 tables(Alpha, Bravo, Charlie, Service, Trans). What i am trying to do is be able to select a start date and ending date for a report and include fields from the 5 tables. this is my code:

SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Alpha]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Bravo]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Charlie]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Trans]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Service]


Is there a way so that when the query is ran, type in a start date and begin date only 1 time and it shows the proper records for the 5 tables.
I was trying to get the code above to work with "WHERE [Alpha].date, [Bravo].date, [Charlie].date, [Trans].date, [Service].date Between [Starting Date] And [Ending Date];
But this is not working. Any help is greatly appriciated
Dec 24 '06 #1
Share this Question
Share on Google+
7 Replies


100+
P: 1,646
hello, I have 5 tables(Alpha, Bravo, Charlie, Service, Trans). What i am trying to do is be able to select a start date and ending date for a report and include fields from the 5 tables. this is my code:

SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Alpha]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Bravo]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Charlie]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Trans]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Service]


Is there a way so that when the query is ran, type in a start date and begin date only 1 time and it shows the proper records for the 5 tables.
I was trying to get the code above to work with "WHERE [Alpha].date, [Bravo].date, [Charlie].date, [Trans].date, [Service].date Between [Starting Date] And [Ending Date];
But this is not working. Any help is greatly appriciated
Hi, are there any fields in this query that contain the same data through all five tables?
Dec 24 '06 #2

nico5038
Expert 2.5K+
P: 3,072
A query can be queried :-)
I would however use for the UNION:
SELECT "Alpha" as Origin, [NameX], [DateX], [Reason], [Action], [Company]
FROM [Alpha]
UNION SELECT "Bravo" as Origin, [NameX], [DateX], [Reason], [Action], [Company]
FROM [Bravo]
UNION SELECT "Charlie" as Origin, [NameX], [DateX], [Reason], [Action], [Company]
FROM [Charlie]
UNION SELECT "Trans" as Origin, [NameX], [DateX], [Reason], [Action], [Company]
FROM [Trans]
UNION SELECT "Service" as Origin, [NameX], [DateX], [Reason], [Action], [Company]
FROM [Service]

The Date is a reserved word and better not used as a fieldname. The Origin will allow you to trace the row's origin.

Now save the query like:
qryUNION

and define:

select * from qryUNION where [DateX] between [Enter Startdate: ] and [Enter Enddate: ]

Getting the idea ?

Nic;o)
Dec 25 '06 #3

NeoPa
Expert Mod 15k+
P: 31,273
Nico's solution will work for you fine.
However, if for any reason you want to include the whole thing in one SQL statement there are two ways :
1. Include the same WHERE clause in each leg of the UNION query.
2. Make your complex UNION query into a subquery and add the WHERE clause to that - similar to using a saved QueryDef (see Nico's solution).

1.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Name], [Date], [Reason], [Action], [Company]
  2. FROM [Alpha]
  3. WHERE [Date] Between [Enter StartDate: ] and [Enter EndDate: ]
  4. UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  5. FROM [Bravo]
  6. WHERE [Date] Between [Enter StartDate: ] and [Enter EndDate: ]
  7. UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  8. FROM [Charlie]
  9. WHERE [Date] Between [Enter StartDate: ] and [Enter EndDate: ]
  10. UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  11. FROM [Trans]
  12. WHERE [Date] Between [Enter StartDate: ] and [Enter EndDate: ]
  13. UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  14. FROM [Service]
  15. WHERE [Date] Between [Enter StartDate: ] and [Enter EndDate: ]
2.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Name], [Date], [Reason], [Action], [Company]
  2. FROM (SELECT [Name], [Date], [Reason], [Action], [Company]
  3.       FROM [Alpha]
  4.       UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  5.       FROM [Bravo]
  6.       UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  7.       FROM [Charlie]
  8.       UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  9.       FROM [Trans]
  10.       UNION SELECT [Name], [Date], [Reason], [Action], [Company]
  11.       FROM [Service]) AS subQ
  12. WHERE subQ.Date Between [Enter StartDate: ] and [Enter EndDate: ]
Dec 29 '06 #4

P: 3
Thank you all for your help. I still have 1 minor problem with the query. I used the code that NeoPa suggested:

SELECT [Name], [Date], [Reason], [Action], [Company]
FROM (SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Alpha]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Bravo]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Charlie]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Trans]
UNION SELECT [Name], [Date], [Reason], [Action], [Company]
FROM [Service]) AS subQ
WHERE subQ.Date Between [Enter StartDate: ] and [Enter EndDate: ]

This works great but when I run query I get asked for "Reason" along with the date. Any ideas how to stop it from asking for reason when the query is run.

Again, Thanks for the help.
Dec 30 '06 #5

NeoPa
Expert Mod 15k+
P: 31,273
Check that you have Reason available in your tables (all of them).
I suspect that at least one of them does not include that field.
Dec 30 '06 #6

NeoPa
Expert Mod 15k+
P: 31,273
BTW:
One of the nice things about the version you selected is that it can be displayed and even updated (to a certain extent) in the Access design window.
Dec 30 '06 #7

P: 3
Your right, i didnt include that field in one of my tables. It works perfect now. thanks for the help.
Dec 30 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.