Results 1 to 8 of 8
  1. #1
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14

    Access Database Reccomendations?


    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

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    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,

  3. #3
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14
    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?

  4. #4
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Quote Originally Posted by top1hat19 View Post
    -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.

    Quote Originally Posted by top1hat19 View Post
    -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,

  5. #5
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14
    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.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by top1hat19 View Post
    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!

  7. #7
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14
    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?

  8. #8
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Quote Originally Posted by top1hat19 View Post
    ... 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,

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-17-2013, 02:44 AM
  2. Take my access database to a UI
    By fndmewrk in forum Access
    Replies: 1
    Last Post: 11-19-2010, 12:22 AM
  3. Migration from Access 2003 database to Access 2007 database.
    By cyclus2 in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 11:43 AM
  4. Access database needs help!
    By chuffy in forum Access
    Replies: 4
    Last Post: 08-09-2010, 06:25 AM
  5. new to access need help on database
    By Miketallica in forum Access
    Replies: 1
    Last Post: 04-27-2010, 07:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums