Maintaining History of Orders

[melanieh]

I am creating a database for a company that sells a product with a variety of options.

They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.

The company wants all those 1.5 million records accessible in their access database.

I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.

My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)

The history table then will take the new orders each year and add to that table.

(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)

Thanks for any advice.
Melanie

 

[RuralGuy]

It will be much easier to work with when all of the records are in the same table. You might even consider putting that table in an mdb by itself to mitigate most of the size limits of Access for a while. Just my $0.02.

 

[David Eagar]

I would suggest a Live history table (last 2-3 years) and an archive table for the rest. How often would you need to check an order that is 15 years old?

 

[RuralGuy]

One table makes it easier to find out how many widgits you've sold over time.

 

[GaryPanic]

my take on this is each year a sepearate d/b

....

or the free ms sql version (forgotten whats its call - but it has double the storage capicaity of access.....

 

[melanieh]

Thanks for the ideas!
If I put the history in a separate database....... would I be able to add the curren't years orders to that history?

(Never done that before so just wondering if it's possible.)
(He wants to see every single order from the past and recent.)

Thanks.

Melanie

 

[RuralGuy]

If I put the history in a separate database....... would I be able to add the curren't years orders to that history?

Absolutely Mel. If you wanted, you could have each table in your db in a separate mdb and the system would not care. I would not recommend it because relationships are harder to maintain.

 

[melanieh]

Great thanks.
I'll start working on it.