Combining tables - UNUSUAL REQUEST

[Parthenon]

Hopefully not too unusual, but I am not very familiar with
Access!

I have many tables, each with one month's worth of data.
These tables have the exact same column fields, but
obviously different data underneath.

I'd like to combine these tables with 12 months of data
into ONE table for the whole year. Basically just append
my January table with the rest of the months.

The tricky part for me is to not add NEW COLUMNS, just
more data under the current columns.

Any ideas? Sorry if this is a simple request.

Thanks in advance!

 

['69 Camaro]

Hi.

It's not an unusual request at all. It's very common for new Access
developers to realize that their original table design needs some refinement
in order to accommodate the needs of the growing database application.
That's how database developers get "experience." When designing new
database structures, they know from experience that certain table designs
will not be expandable, as in your case.

The tricky part for me is to not add NEW COLUMNS, just
more data under the current columns.

If you want to append all of the records to the January table, then the
table structure will need to add either one or two more columns: one column
for the month and possibly one column for the year (in case you may want the
expand the database structure to be even more flexible in the future).
Otherwise, you won't be able to differentiate which month (and year) the
data was retrieved from if you append all records to the January table.

An alternative is to keep the records in the 12 tables, but to create a
UNION query which retrieves all records from each of these tables and
creates a calculated column indicating the month, depending upon which table
holds the records. This can become unwieldy, especially if you add "month"
tables to the database for the next year's data. A single table holding the
data would be preferable, as you have already decided.

Your only other alternative is to denormalize your database by using a
multivalued column, and that would introduce anomolies and data retrieval
problems, so it is definitely not recommended.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Paul Johnson]

Sorry if this echoes Gunny's response, but it offers a little more detail,
too. I had it written, about to send it when I took my kids to a birthday
party and ended up staying for the whole thing. I guess you could wait for
replies on a Friday night!

You could use a UNION query, and forget about building another table that
copies values from separate tables. Of course, I wonder why there are
twelve separate tables in the first place. Since you say the tables have
the exact same columns, you can do a very simple "SELECT *" query uniting
the tables.

Simple Union query:

SELECT *
FROM JanuaryTable
UNION
SELECT *
FROM FebruaryTable
UNION
SELECT *
FROM MarchTable
UNION
SELECT *
FROM AprilTable
UNION
SELECT *
FROM MayTable
UNION
SELECT *
FROM JuneTable
UNION
SELECT *
FROM JulyTable
UNION
SELECT *
FROM AugustTable
UNION
SELECT *
FROM SeptemberTable
UNION
SELECT *
FROM OctoberTable
UNION
SELECT *
FROM NovemberTable
UNION
SELECT *
FROM DecemberTable
ORDER BY SomeField;

HTH

Paul

 

[Douglas J. Steele]

<Picky>
Assuming the tables are, in fact, identical, you probably will need some way
of identifying what month each one came from:

SELECT *, "January" As EffMonth
FROM JanuaryTable
UNION
SELECT *, "February" As EffMonth
FROM FebruaryTable
UNION
SELECT *, "March" As EffMonth
FROM MarchTable
UNION
</Picky>

 

[Paul Johnson]

Yes, the query will need that if there isn't a date column in the tables. I
may have assumed too much...

Paul Johnson