Moving Daily Data to "Archive"... 1

[bluenoser337]

I have an Access2000 DB with a single table recording parts as they are manufactured. At the end of the day, after printing the summary report, I would like to "add" the days production to another table that will be updated every day with the "totals" from that day. This table will be used for weekly, monthly, and annual reports so it is not necessary to search a table filled with individual parts records. The original table can then be emptied for the new day. My query for the report does all the necessary grouping but I'm not sure how to "add" this totalled information to another table. Also, I would also like to have some kind of check that the transfer has occurred successfully before emptying the original table. Any suggestions greatly appreciated!

 

[garethp]

hi
i'm not sure if i have the full handle on this but can you just clarify for me:

a)do you just want to clear down your daily table and drop it all in to your archive

or

B) archive the daily table across changing the totals of each product as they are produced so that you have continually summed production amount

I think your looking at (a) which means you can append across using the previous date of product creation to set a criteria then use a delete query to clear down the daily product table

gareth

 

[BNPMike]

I'd certainly think twice about doing this.

so it is not necessary to search a table filled with individual parts records

If that's your only concern then I wouldn't bother. Access will handle all the records a company your size will generate. How do I know how big you are? - You wouldn't be using Access if you were producing a million parts a day.

I've never ever known a situation where it is safe to sum things up and forget them. Believe me you will have errors and corrections, adjustments, new requirements etc etc. Keep your atomic data and you have the best chance of responding cleanly to the realities of life.

 

[bluenoser337]

garethp...(B) but at the end of each day.
BNPMike...7000 parts per day, and without doing the summing, the DB gets to the 2 gig limit too soon.
So how to do (B)...the right way?

Thanks!

 

[BNPMike]

BNPMike...7000 parts per day, and without doing the summing, the DB gets to the 2 gig limit too soon.

Dump Jet and use MS SQL Server or MySQL. Don't compromise your data model. There's no need to in today's world.

 

[bluenoser337]

BNPMike...unfortunately, I have to make this happen with what I have. Thanks!

 

[lespaul]

There is no way to do (B) "the right way" since by definition you are breaking 3NF by storing calculated values.

Why are you restricted to what you have? If cost is an issue MySQL is free.

HTH



Leslie

 

[mustangcoupe]

is there a place with information on how to setup mysql to work with an access front end? or does anyone have any "words of wisdom

 

[BNPMike]

I've just done a quick calculation.

If its 7000 parts, they must be of a limited number of types (ie not unique) so I would guess the entire record could be 50 bytes (through foreign keys). On that basis it would take you nearly 15 years to hit 2GB.

More typically you would archive the data each year - after some end-of-year processing and reconciliation.

I know this is not answering your original question but it seems like you would be better sorting out these issues rather than the one you thought was the problem.


I see now you have a single table. This is the almost certainly a recipe for pain a short way down the road. With 7000 records a day, your data has significant business value so you should design your system properly.

 

[bluenoser337]

Hi...780 different combinations of parts possible per day. Explain the "foreign key" thing. I'm new at this. Thanks!!

 

[BNPMike]

A part will have a number of 'views'. Each day you are making more than one of each possible type. So maybe you mark that by serial number, catalogue number, order number. The catlogue number then refers to another table which provides details of that part (rather than the partcular instance produced today). The order number refers to another table which has order details including customer number. Customer number in turn refers to another table of customer details eg address, discounts etc. And so on.

We tend, on this channel, to recommend you normalise your database to Third Normal Form.

The problem with your aggregate approach is sure-as-anything, someone will come back and say "Those GX2796s we produced in batch 348990 last month were all done to GX2795 standards. We've refunded the customer. Can you change the monthly stats?"

 

[bluenoser337]

BNPMike....thanks for the eye-opening info. I've done a test and found that the file reaches the size limit after the equivalent of 464 business days...so I will be changing my plans. Thanks again!

 

[lespaul]

For more information on normalization check out 'The Fundamentals of Relational Database Design'



Leslie