Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

    Unanswered: Table Structure for Tracking Data over Time

    I am in the process of setting up a database to log and analyze data regarding our 'clients.' Right now I have approximately 1,000 clients.

    I am using access to create the tables, I need something easy to use as I will be having multiple people enter data.

    I'm unsure how to structure the table, because I am looking to analyze the data over time. For example, we may measure the weight of 1,000 people. Then at various other times we will take their weight again.

    I want to be able to compare the weights for each individual over time. Meaning client 1 weight xxxlbs on January 1st and then weight xxxlbs on July 1st.

    What would be the best way to create the table so that I can easily keep track of the data points, but make sure they are able to be matched to the specific client id and show the date that the data was acquired? I appreciate any help, and please let me know if more information is needed to clear up what I'm trying to do.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    If you have the time and inclination read up on normalisation. A process used in RELATIONAL db design. In essence only store a piece of information once, dont repeat

    that means designing tables such that you never repeat data (except keys). In practice from what you have said so far that means at least two tables
    one for Persons (or clients if you prefer)
    One for Oberservations(ie weighins sessions)

    Persons stores everything you need to uniquely identify a client (name address phone email)
    Observations stores everything relevant to a specific session where you measured the weight ( eg date weight notes/excuses?
    You may need other tables as your design evolves but dont sweat it right now.say you periodically also take other measurements eg waist, chest, bicep...... some might argue theyd be in another table, but unless you have hordes of measurements leave them in observations. If you do have hordes of diaparate measuremenrs then consider the sub supertype model.

    in access you are better using a form per table. If it were me fir this id yse a form for oersons and an embedded form for observations. The embedded form would be continuous rows meaning youd see multiple observations per client.

    the primary key for persons coukd be either a autonumber or a made up obe something like the first 6 letters of the surname + some digits. Eg SMITH0045, FNLDWN045, HEALDE937

    pk for observations likewise could be an autonumber, but probably better if its a composite of person AND date of oberservation. That way round you can never duplicate observations.

    resist the temptation to dive into designing stuff in Access till you have a good idea of your requirements.
    read up on good table design...
    ...dont use spaces in your table and column names, use underscores or capitalusation to separate words in columns. Such as DateOfBirth or date_of_birth, use consistent abbreviations eg PhoneNmb or phone_no. Tablenames in plural. Use the caption property (in the table design) to set a human readable label
    understand what words Access reserves for its own use (google Accessreserved words), so dont use words like date, desc, table......
    dont get frystrated or disheartened if you make mistakes, we all do.
    dont loose sight of the fact your business is weight loss not system design, but enjoy the process
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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