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

Query across three history tables

P: n/a
RLN
Re: Access 2000

I have three history tables.
Each table contains 3 years worth of data.
All three tables have a date field in them (and autonum field).
Each table has the potential to contain over 100K rows each.
A common request the user receives is a request to pull all data written
in a given month. I need to write some sort of union query across all
three tables that would pull data for, say, only the month of December
of all nine years.

I got a union query to work with two tables.
How do I do it with three or more tables?

Thanks for the assistance.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
You should be able top just add more UNION statements:

SELECT Fld1, Fld2 FROM tbl1
UNION SELECT Fld1, Fld2 FROM tbl2
UNION SELECT Fld1, Fld2 FROM tbl3;
etc.....
On 23 Jun 2004 21:10:36 GMT, RLN <no*******@devdex.com> wrote:
Re: Access 2000

I have three history tables.
Each table contains 3 years worth of data.
All three tables have a date field in them (and autonum field).
Each table has the potential to contain over 100K rows each.
A common request the user receives is a request to pull all data written
in a given month. I need to write some sort of union query across all
three tables that would pull data for, say, only the month of December
of all nine years.

I got a union query to work with two tables.
How do I do it with three or more tables?

Thanks for the assistance.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 13 '05 #2

P: n/a
I got a union query to work with two tables.
How do I do it with three or more tables?

Thanks for the assistance.


SELECT field1, field2
FROM table1
UNION ALL
SELECT field1, field2
FROM table1...

will give you ALL the records, including duplicates (should there be any).

using a plain UNION will give you ONLY unique records.
Nov 13 '05 #3

P: n/a
RLN
Now I would like to take this union query a step further.
In each of these three test tables are two rows, each has a date in the
month of December, and TblTest2004 is the only table with 3 rows for the
month of March.
How can I pull just those 9 rows out of all three tables?
(6 total for 12/2003, 2004 & 2005, then also 3 for March in 2004 only?
The trick is that when this goes live, the user might not always know
what year the March data is located, but will only have the month.
I tried using the 'month' function as listed below, to no avail.
Suggestions?

SELECT ccdate, comments
FROM TblTest2003
UNION
SELECT ccdate, comments
FROM TblTest2004
UNION
SELECT ccdate, comments
FROM TblTest2005
where month(ccdate) =12 or month(ccdate = 3)

(I wasn't sure how to qualify the ccdate field from TblTest2003, ccdate
from TblTest2004, etc..etc.)

Suggestions are welcome.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
> In each of these three test tables are two rows, each has a date in the
month of December, and TblTest2004 is the only table with 3 rows for the
month of March.
How can I pull just those 9 rows out of all three tables?
(6 total for 12/2003, 2004 & 2005, then also 3 for March in 2004 only?
The trick is that when this goes live, the user might not always know
what year the March data is located, but will only have the month.
I tried using the 'month' function as listed below, to no avail.
Suggestions?

SELECT ccdate, comments
FROM TblTest2003
UNION
SELECT ccdate, comments
FROM TblTest2004
UNION
SELECT ccdate, comments
FROM TblTest2005
where month(ccdate) =12 or month(ccdate = 3)

(I wasn't sure how to qualify the ccdate field from TblTest2003, ccdate
from TblTest2004, etc..etc.)


If you are filtering the whole dataset (the result of the unions),
build another query on this one which does the filtering. otherwise,
you need to include a WHERE clause for *each* select statement in your
union query.
Nov 13 '05 #5

P: n/a
SELECT ccdate, comments
FROM TblTest2003 where ccdate = #12/03/04#
UNION
SELECT ccdate, comments
FROM TblTest2004 where ccdate = #12/03/04#
UNION
SELECT ccdate, comments
FROM TblTest2005 where ccdate =#12/04/05#;

This union query runs, but using the Query Designer (SQL view)
I need to try two other flavors of this query:
1. check to see if the month is 03 (March) or 12 (December)
(I tried "month(ccdate)", which did not work.

2. set this query up to launch two dialogs: "a begin month" and an "end
month", then pulls all the history for that range of months in between.
(user inputs "4", then "9", the query would pull all data for April thru
September for all three years.

---------------
Thanks.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

P: n/a
> This union query runs, but using the Query Designer (SQL view)
I need to try two other flavors of this query:
1. check to see if the month is 03 (March) or 12 (December)
(I tried "month(ccdate)", which did not work.
SELECT Table4.SomeDate, Month([SomeDate]) AS MonthNo
FROM Table4
WHERE (((Month([SomeDate])) In (3,12)));
2. set this query up to launch two dialogs: "a begin month" and an "end
month", then pulls all the history for that range of months in between.
(user inputs "4", then "9", the query would pull all data for April thru
September for all three years.

SELECT Table4.SomeDate, Month([SomeDate]) AS MonthNo
FROM Table4
WHERE (((Month([SomeDate])) Between [Enter a begin month:] And [Enter
an end month:]));
Nov 13 '05 #7

P: n/a

I am working to refine the selection process so that the date range gets
pulled across all three tables.
So far the tests are looking ok.
---------------
Thanks.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.