Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Unanswered: Help Creating Database (general ?)

    Hi All,

    I need to create a database to track an investment for about 900 different accounts and keep track of their daily account value and keep historical values to calculate performance on the accounts.

    My initial amateur thought is to create a table that has two fields:
    ACCOUNTNUMBER
    BALANCEDATE
    ACCOUNTBALANCE

    Example (would kind of look like this with two accounts and three days worth of values):

    11111; 01/14/08; $30,000
    22222; 01/14/08; $60,000
    11111; 01/15/08; $30,300
    22222; 01/15/08; $59,800
    11111; 01/16/08; $31,000
    22222; 01/16/08; $60,200

    My concern is at 900 accounts * about 300 trading days during the year I would be adding 270,000 records to the table and I don't know if Microsoft Access can handle this or if this is the best way to do it, or should I have a seperate table for each day rather than grouping all data on one table...any thoughts/suggestions???

    Thank You,

    Matt.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    microsoft access can handle it, no problem

    separate tables for each day would be suicidal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    13

    Thanks

    Thank you...do you think I should add an autonumber field for a primary key or is one even necessary...as I won't have any field that is a unique value

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i believe every table should have a candidate key that is not an autonumber -- consisting of multiple columns if necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Indeed, and every "data" table should have an autonumber field (a lot of save record problems on forms stem from simply not having an autonumber field in the table.) I personally recommend an autonumber field in ALL tables.
    Last edited by pkstormy; 04-05-08 at 01:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and what kinds of tables are there besides "data" tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    lookup type tables, Switchboard tables, Company Header tables which aren't often updated (I see it common that developers don't put in an autonumber field in these tables.) By "data" tables I meant the main tables which hold the data that is constantly updating.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To add: Temporary tables, Application Options tables... there's many types of tables I wouldn't classify as "data" tables.

    And I agree with you PK. I believe every "data" table should have an autonumber primary key, excluding junction tables, which I use composite keys.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i see what you guys are saying about a "data" table

    please let me correct my statement slightly:

    i believe every table -- "data" table as well as all others -- should have a candidate key that is not an autonumber -- consisting of multiple columns if necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Elaborate? Why? To prevent duplicate entries? Any other reasons?

    I mean every query is going to be so much more complex if you do it that way. Even creating a combo box would be a mission... unless I'm misunderstanding you, I'd say that's overkill.

    With tables such as client, I find it impossible to uniquely identify a client with any combination of fields other than the ID. People can and do change names, addresses, phone numbers, mobile phones, e-mail addresses etc.... sometimes the only thing that DOESN'T change is the ID.

    Tables that hold option data only ever have one record and aren't related to any data tables so there is absolutely no point even having a key!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •