large individual tables or not

[Guest]

I am trying to design a database and I haven't worked with them very much,
but have a fairly good knowledge of database design.

I have been trying to determine how to best design the tables for our
clients investment holdings information. Each month we need to send out a
report with their investment holdings and other information ( share amounts,
price and total mkt value) there may be some other information that they
request in the future, but we have about 180 clients and each holds between
40 and 150 assets in different quantities. So all of the information per
client per month is unique. I was wondering might be the best way to go
about holding this information. I could have one large table for each month
with the clients name, stock ticker, quantity...., I could have an even
larger one with the year and add the month as a column or I was thinking I
could have many tables, maybe one for each client with the date, ticker....
Or something else if there is a better way. Does anybody have any
suggestions?

The only other option is that I have this information in csv files and I
could leave it there and just create a macro to open the file and read it
into a report when the user wants it, but I don't know how efficient that
is...

thanks

 

[Douglas J. Steele]

A single table is definitely the way to go. Having separate tables per month
or client (with, presumably, names like "DougsAccounts" or "Feb2007Clients")
is a mistake. You're actually hiding data in the table name, and it's a
nightmare to do any queries that span (or compares) months or clients.

 

[Guest]

Thanks for the response. So there I shouldn't run into any issues of table
size then? Or is this something I should be concerned about?

 

[Rick Brandt]

Thanks for the response. So there I shouldn't run into any issues of
table size then? Or is this something I should be concerned about?

With properly designed and indexed tables size is not an issue until you outgrow
what Access (Jet) will hold.

 

[Guest]

The reason I ask is that my table size is easy to calculate since I will have
180 portfolios and and average of 70 securities, which will be 12600 rows
each month if I put it in one table. I have read that access' limit is 2GB,
so are there no limits to the number of rows in a table?

 

[Rick Brandt]

The reason I ask is that my table size is easy to calculate since I
will have 180 portfolios and and average of 70 securities, which will
be 12600 rows each month if I put it in one table. I have read that
access' limit is 2GB, so are there no limits to the number of rows in
a table?

Not until your file size hits 2GB, no. You should be fine for several years at
least (depending on how many other tables you have and their size).

 

[Guest]

Thanks Rick for the help.

Not until your file size hits 2GB, no. You should be fine for several years at
least (depending on how many other tables you have and their size).

 

[Douglas J. Steele]

Having one table containing 12600 rows should actually take less space
(marginally) than 20 tables containing 630 rows

There's no limit on how many rows you can have in a single table. The only
limit is how large an MDB file can be.