"Stacking" Identical Tables

[Keith & Dayna]

Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.

Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.

How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.

Either a quick answer or help with finding the correct terms to search
for would be much appreciated!

Thanks!
Keith

PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?


**** Example ****

TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N

TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y

Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07

 

[Guest]

What you want is a union query!

SELECT [field1], [field2]
UNION SELECT [field1],[field2];

Having said that why do you have duplicate tables for each month?

BAD NORMALISATION!

 

[Keith & Dayna]

Thanks for the help. I was given the monthly tables from the people
who provided the data. My guess is they did this to make the files
manageable when transferring them to me (about 300mb for each
month).

Thanks,
Keith

What you want is a union query!

SELECT [field1], [field2]
UNION SELECT [field1],[field2];

Having said that why do you have duplicate tables for each month?

BAD NORMALISATION!

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.

Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.

How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.

Either a quick answer or help with finding the correct terms to search
for would be much appreciated!

PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?

**** Example ****

TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N

TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y

Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07

 

[Keith & Dayna]

What about putting the table (or month) name into a new column? Do I
need to do that manually? (I have lots of months and want to automate
this)

What you want is a union query!

SELECT [field1], [field2]
UNION SELECT [field1],[field2];

Having said that why do you have duplicate tables for each month?

BAD NORMALISATION!

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.

Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.

How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.

Either a quick answer or help with finding the correct terms to search
for would be much appreciated!

PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?

**** Example ****

TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N

TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y

Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07

 

[Guest]

With separate tables you don't have a field for month!

In a field of each query type the following

Expr1: "January"

Then every row in the field will have "January" inserted into it.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

What about putting the table (or month) name into a new column? Do I
need to do that manually? (I have lots of months and want to automate
this)

What you want is a union query!

SELECT [field1], [field2]
UNION SELECT [field1],[field2];

Having said that why do you have duplicate tables for each month?

BAD NORMALISATION!

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.

Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.

How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.

Either a quick answer or help with finding the correct terms to search
for would be much appreciated!

PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?

**** Example ****

TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N

TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y

Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07

 

[Guest]

Combining the tables would be the thing to do. Here's how:
Create a new table with the structure of the existing tables. You can do
that by copying the struture only.
In the Database Window, Tables tab:
Select one of the tables.
Right Click and Select Copy
Point to anywhere in the white space and Right Click and Select Paste
Select Structure Only
Give the table a name.
Open the table in design mode.
Add a new field - I would suggest a date field. That way you will have both
Month and Year. It will also contain the day, but you can filter by year and
month of the date field. Also, do not use Now() or anything else that will
add a time component.

Create an Append Query that will take the data from the Jaunary table and
append it to the new table. Add a calculated field to the query to append
the a value to the new date field. Put the date in the calculated field.

#1/2/277# As YEAR_MONTH

Run the query to add January.

Change the query to use the February table.
Change the date in the query to #2/1/2007#

Repeat for each month.

--
Dave Hargis, Microsoft Access MVP

What about putting the table (or month) name into a new column? Do I
need to do that manually? (I have lots of months and want to automate
this)

What you want is a union query!

SELECT [field1], [field2]
UNION SELECT [field1],[field2];

Having said that why do you have duplicate tables for each month?

BAD NORMALISATION!

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.

Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.

How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.

Either a quick answer or help with finding the correct terms to search
for would be much appreciated!

PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?

**** Example ****

TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N

TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y

Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07

 

[Keith & Dayna]

As a follow up to those who browse through here in the future, the
following SQL routine works for this example case (if I had three
months):

select "Jan" as month, 2007 as year, * from TableJan07
UNION select "Feb" as month, 2007 as year, * from TableFeb07
UNION select "Mar" as month, 2007 as year, * from TableMar07

Thanks scubadiver!
- Keith


PS) Personally, I have found that this technique is REALLY slow for
my huge files. I might have to switch to Sybase IQ or something...

With separate tables you don't have a field for month!

In a field of each query type the following

Expr1: "January"

Then every row in the field will have "January" inserted into it.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

What about putting the table (or month) name into a new column? Do I
need to do that manually? (I have lots of months and want to automate
this)

What you want is a union query!
SELECT [field1], [field2]
UNION SELECT [field1],[field2];
Having said that why do you have duplicate tables for each month?
BAD NORMALISATION!
--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video
:
Thanks in advance for your patience, I have basic Access skills, but
don't know how to search the help menus and forums for this issue.
Suppose I have one table for each month. Each table has the same
structure (that is, all of the columns have the same types of data and
the same variable names). That is, one table has all the data for
January, the next table has information for February, and so on.
How do I write a query to stack all of these tables "on top" of each
other? I want one large query with everything.
Either a quick answer or help with finding the correct terms to search
for would be much appreciated!
Thanks!
Keith
PS) In my case, the resulting query will be several million rows.
There are only a few columns. Is that a problem?
**** Example ****
TableJan07:
ID YES_NO
1 Y
2 Y
3 N
4 Y
5 N
TableFeb07:
ID YES_NO
1 N
2 Y
3 N
4 Y
5 Y
Desired Query:
ID YES_NO MONTH
1 Y Jan07
2 Y Jan07
3 N Jan07
4 Y Jan07
5 N Jan07
1 N Feb07
2 Y Feb07
3 N Feb07
4 Y Feb07
5 Y Feb07