Automating a History Table

[Guest]

How do programically append each months records to a history table? Right
now, I use a command button that appends each month's records to the History
table. I need assistance, however, in including the month's date for each
group of records.

 

[TC]

You do not "append records to a history table". You store a date in
each record, so that all of your forms & reports can filter for records
for the last week, or the last month, or between Jan 13 and Mar 25 of
1998, or whatever they want.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Douglas J. Steele]

Do you really need to append the records to a history table? Why not put the
year and month as fields in the existing table, and change your queries to
only work with the current month.

 

[Guest]

Douglas,
Yes, I need a history table because in addition to pulling a Month to Date
report, I also pull a Year To Date report that includes wieghted
calculations. It's important that the history table does not have duplicate
information so I set the primary keys on Date and Store Number.

 

[Douglas J. Steele]

That doesn't explain why you need the data in a second table.

 

[TC]

Sharon, did you see my reply? Some of them are not getting through.

You do not "append records to a history table". You store a date in
each record, so that all of your forms & reports can filter for records
for the last week, or the last month, or for the records between Jan 13
and Mar 25 of 1998, or for whatever other period that you might want.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

My original data comes in as follows.

Import Format (50,000 records):
AcctType Jan Feb Mar Apr May ....
Store#1 101 500.00 250.00 300.00
202 400.00 100.00 45.00
500 150.00 10.00 100.00
Store #2.....

Each month, I take the data for that month and create a crosstab table using
the Acct Types as the columns. So the original data no longer exist as it
is imported. Each report is specific only to that month.

REPORT FORMAT (230 records)
Store # Acct 101 Acct 202 Acct500
Store#1 500 400 150
Store#2....
Store #3.....

 

[Douglas J. Steele]

So your problem is that you've got a non-normalized database design.

You really should normalize the data you're storing. Once you've done that,
it's fairly straight-forward to produce a report for only the current month,
even though you've got multiple months data in your table.