Tracking End of Month Data

[spherearrow]

Im just starting to work on a database that tracks account balances at the end of each month for a large number of customers. I need to be able to store the information for 24 months. I am realativily new to larger databases and this will be the first database I have designed to be used by other people and i want to design it to run efficently. Each month I will be getting multiple excel spreadsheets with account balances in them and I need to know how to set up my tables to allow access to the data efficiently.



Right now the only ways I can think of to do this is have a table for each account(impossible in my case due to the large number of accounts) or have a new table each month (also difficult due to the variety of different account types which may or may not have a act # identical to an act # in another account type)

Is there an easyier way to design this?


Thanks in advance for the help,

-sa

[weekend00]

I would suggest one table for all account, all months.

[spherearrow]

Unfortunatly putting everythign into one table will not promote efficiancy in the system as i need to be able to run query's on each month seperatly and each month i would have to add duplicate records for accounts(their balance changes, but i still need the previous months data) and there are aproximently 50000 records that would be added to that table each month.

[weekend00]

If your query only need one month's data, you can use one table for each month, but in this way, it will be very annoying when you need data in different months.

50000*24= 1,200,000 records are all right. unless you don't want to wait even 1 second.

[spherearrow]

Ok guess it jsut seems excessive to have multiple records for the same act in one table. Ill try it out and see how it works thanks for the input

[NoellaG]

Hi Spherearrow,

I think you look at Access a bit to much as a kind of Excel. When you say

i would have to add duplicate records for accounts(their balance changes, but i still need the previous months data)

well, if you don't delete any data they are still there from the previous month aren't they? I think you need to relook at your database design and redesign it as a normalized database, as Access is meant to work.
You'll still be able to add each month the new data to the tables, using a set of append/update queries.
there are some great papers on the net regarding database design.

succes
Noella