Query across three history tables

[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!

[Robert Allyn]

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!

[Pieter Linden]

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.

[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!

[Pieter Linden]

> 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.

[IrishGuinness]

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!

[Pieter Linden]

> 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:]));

[IrishGuinness]

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!