Re: Combining tables - UNUSUAL REQUEST
From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 09/24/04
- Next message: Douglas J. Steele: "Re: moving datat"
- Previous message: Chris Nebinger: "how do I enter 5.2 field size in access"
- In reply to: Parthenon: "Combining tables - UNUSUAL REQUEST"
- Next in thread: Paul Johnson: "Re: Combining tables - UNUSUAL REQUEST"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Sep 2004 16:56:48 -0700
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.)
"Parthenon" <anonymous@discussions.microsoft.com> wrote in message
news:0c9e01c4a271$0139dcf0$a501280a@phx.gbl...
> 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!
- Next message: Douglas J. Steele: "Re: moving datat"
- Previous message: Chris Nebinger: "how do I enter 5.2 field size in access"
- In reply to: Parthenon: "Combining tables - UNUSUAL REQUEST"
- Next in thread: Paul Johnson: "Re: Combining tables - UNUSUAL REQUEST"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
