Access Database Reccomendations?

[top1hat19]

I need to build a database, and I'm not sure what structure to use (what data to put in separate tables, or one table, or something else...). I thought maybe I could get some suggestions based on the data I need to store and access:



-Pool of about 1,000 funds. Each fund has a score, and new data is imported every quarter.
-List of about 100 clients. Each client has about 40 funds in its portfolio that come from the Pool of funds (clients can have the same funds).

So each quarter, I will import the same 1,000 funds (maybe a few extra/less) with new scores into the database. I then need to generate a report for each Client that shows the funds they have and what their new scores are. I also need the ability to look at data from past quarters.

So how should I do this? Should I have 1 table with all the funds, and add 1,000 rows to it every quarter? Or should I make a new funds table every quarter, and call it "Funds QX 20XX"? Or should I have 1 table for each Fund, and add a new row to each one every quarter with the new score? I need help! lol

[ConneXionLost]

I suggest you start with four tables:

tblClient
ClientID (PK)
Surname
Forename
address

tblFund
FundID (PK)
FundTitle

tblClientFund
ClientFundID (PK)
ClientID (FK)
FundID (FK)
PurchaseDate

tblFundScore
FundScoreID (PK)
FundID (FK)
Quarter
Score

PK - Primary Key
FK - Foreign Key (PK from other table)

Each quarter you will place all the fund scores into tblFundScore. There will be a record (row) added to this table for each fund in each quarter; in other words, you will add 1000 records each quarter into this table.

Cheers,

[top1hat19]

Thanks! But I have a few questions:

-would this do the same thing?
---remove tblFund table
---remove FundScoreID from tblFundScore
---make FundID the PK in tblFundScore

-also, how would the tblClientFund table look? Each client has about 40 funds, so would there be about 40 columns? Or about 40 rows per client?

[ConneXionLost]

-would this do the same thing?
---remove tblFund table
---remove FundScoreID from tblFundScore
---make FundID the PK in tblFundScore

Actually, no. This would limit the number of records to the number of funds. For each quarter, you would either have to add a new field (column), or over-write the previous quarter information. Remember that the maximum number of fields (columns) you can have in a table is 255; but you can have many more records (rows). That says something about how you should design the tables.

-also, how would the tblClientFund table look? Each client has about 40 funds, so would there be about 40 columns? Or about 40 rows per client?

40 rows per client.

Database tables work better when they're "long & skinny" as opposed to "short & fat".

Cheers,

[top1hat19]

Thank you so much for your advice! This is really helping me out. I have another question though:

If the tblClientFund table has 40 records for each client, wouldnt that mean about 4,000 records with alot of repeats in the FundID column? Seems like there would be a better way to do that.

[ajetrumpet]

Thank you so much for your advice! This is really helping me out. I have another question though:

If the tblClientFund table has 40 records for each client, wouldnt that mean about 4,000 records with alot of repeats in the FundID column? Seems like there would be a better way to do that.

no, there isn't. The advice you got, IMO, is right on. FK tables are supposed to be like that. That's why FK's are not designed to hold unique values.

as a side note, which I just realized, the max fields in 255 in Access. Directly correlated to the expression 256 x 256, which equals 65536. I always love thinking about those bits, and how those first gaming systems started. That reminds me of the Dragon Warrior games for the NES. The max experience points you could get was 65536. Now I know why!

[top1hat19]

Cool, thanks! Ok next question:

Every quarter, when I import 1000 records into the tblFundScore, the FundID needs to match the respective field in tblFund. The source that I import from doesnt have a good unique identifier for each fund, so i want to just use autonumber as the key. How do I import all 1000 funds each quarter without having to manually type in the FundID FK for each one?

[ConneXionLost]

... The source that I import from doesnt have a good unique identifier for each fund, ... How do I import all 1000 funds each quarter without having to manually type in the FundID FK for each one?

Well, the good news is, this issue won't affect your choice of data model! The bad news is, there is no special data model that can help with this. Without some method of matching the records to the FundID keys, you will be adding the data manually, or adding the FundIDs to the data prior to importing (with Excel or something similar, but still manual).

Sorry,