Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Unanswered: Access or Excel for this:

    Hi
    I have been slowly building the DB for work for a few months now. It tracks everything I need, and works quite nicely.. yay.
    The clients I have will sit 4 tests while they are with us. These results need to be tracked, and eventually made into a graph, to visually track progress. etc.
    Attached is a sample of data I will be collecting.

    The dilemma is, which program to use. I started it in excel, but ran into problems straight away as there is no way to set if the client is current or not.
    We have a max of 14 clients at any one time.

    Is access capable of using data in the same way as excel is? Can I output the data from Access to Excel..

    What would you suggest?

    Thanks
    Tracy
    Attached Files Attached Files

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Gwyar View Post
    Hi
    I have been slowly building the DB for work for a few months now. It tracks everything I need, and works quite nicely.. yay.
    The clients I have will sit 4 tests while they are with us. These results need to be tracked, and eventually made into a graph, to visually track progress. etc.
    Attached is a sample of data I will be collecting.

    The dilemma is, which program to use. I started it in excel, but ran into problems straight away as there is no way to set if the client is current or not.
    We have a max of 14 clients at any one time.

    Is access capable of using data in the same way as excel is? Can I output the data from Access to Excel..

    What would you suggest?

    Thanks
    Tracy
    Tracy,

    Databases (Access) are used to store data

    Spreadsheets (Excel) as used to analyze data.

    By design, Excel can be used to analyze data from an Access database.

    Databases are usually the best tool for storing and maintaining data over time.

    IMHO, you should be using a database to store the data.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jan 2008
    Posts
    114
    I was hoping this was going to be the case. I much prefer Access... I guess I need to learn how to integrate access with excel.

    thanks much
    Tracy

  4. #4
    Join Date
    Jan 2008
    Posts
    114
    Hello again.
    I have given this some more thought. The PDF file above.. how would you separate that?
    Separate tables by date or by subject.

    I think I need to visualize this,

    One Client-date one - lots of questions =table 1
    'One Client-date two - lots of questions = table 2
    etc .....

    Or a better way to organize this data?
    What would you suggest.

    thanks

    Tracy

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    HiTechCoach is absolutely right. Also, don't think about your database design based on the format of how the data is presented.

    There would probably be at least one more table (possibly two) but I would have a table of this form:
    ClientPTSDOverTime{ClientID, Measure, TimeLine, Score}
    You will probably have better column names, but the entries for Measure are "Problem Recognition", "Desire for help" etc and TimeLine are "Baseline", "23 Day", "90 Days". Instead you might want to call it something like Days and have integer entries of 0, 23, 90. The primary key is a composite of the three columns, Score is the sole non-key attribute.
    You would need a further table for the Measures if you need to group them as "Treatment Needs\ Motivation", "Psychological functioning" etc.

  6. #6
    Join Date
    Jan 2008
    Posts
    114
    I'm trying various things.. And totally confusing myself in the process....
    A little explanation:

    A client stays with us for 6 months. Within that 6 months they will take the same test 4 times.
    Once at intake, then at 23 days, 90 days and 180 days.
    Would these be the tables.?
    =======

    After writing this I tried to work it out....I guess I don't know how to group this stuff together. If I need to keep track of how the client is doing with Problem Recognition, I will have 4 scores which I will make a graph out of.
    I don't want to repeat the questions in each table, cos that's a waste.

    If you look at the picture and see how that is.. With each client taking the test the table will get very big... very quick. Right?

    Augh.. confused

    Tracy
    Attached Thumbnails Attached Thumbnails New Picture (33).bmp  
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Having fewer rows is not a goal of database design. Indeed, well designed databases typically have narrow tables (few columns) with lots of rows. The important thing with tables with many rows is to get you indexes correct. An index is in most RDBMSs typically implemented as a B-Tree. A B-Tree is incredibly efficient - its depth (which is the important feature) is logarithmic to the number of rows in the table. In other words, the number of rows in the table have to increase by several factors before the depth increases. Don't be scared of lots of rows - I work with tables that contain billions (not in access obviously) but they are well designed and well indexed and run just fine.

    If you need to group your Measures then you will need an additional table. I don't know what you mean by waste unless you mean you do not need to group them.

    I suspect what you really want to design the table like is very like your report with four columns for each timeline. If so then note this is a violation of first normal form and means your table is not normalised.

    more details on many of the points I raise:
    The Relational Data Model, Normalisation and effective Database Design

  8. #8
    Join Date
    May 2010
    Posts
    601
    This may help:

    At Your Survey
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Jan 2008
    Posts
    114
    Just a quick question then..
    If every client answers these 24 questions 4 times, (24*4=96) there will be 96 rows for each client. As time progresses these rows will grow very long.. Is that not a problem?

    Thank you everyone

    Tracy

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Gwyar View Post
    Just a quick question then..
    If every client answers these 24 questions 4 times, (24*4=96) there will be 96 rows for each client. As time progresses these rows will grow very long.. Is that not a problem?

    Thank you everyone

    Tracy
    Tracy,

    That is the correct way. That is exactly what should happen over time with any database.

    tables normally have lots of rows.


    What concerns do you have?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Jan 2008
    Posts
    114
    I guess I was a bit worried that it would get very, very long. We only have 14 clients at any one time, but if you add em up over time I was just worried it would bog down

    Thanks you
    Tracy

  12. #12
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Gwyar View Post
    I guess I was a bit worried that it would get very, very long. We only have 14 clients at any one time, but if you add em up over time I was just worried it would bog down

    Thanks you
    Tracy
    Tracy,

    This was previously stated:
    Quote Originally Posted by pootle flump View Post
    Having fewer rows is not a goal of database design. Indeed, well designed databases typically have narrow tables (few columns) with lots of rows. The important thing with tables with many rows is to get you indexes correct.
    This is spot on. I have never worried about the number of rows in a table. I am concerned about having lots of fields (columns) in a single row/record. That is usually more of an issue with the design than the number of rows.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  13. #13
    Join Date
    Jan 2008
    Posts
    114
    Thank you gentlemen.
    This eases my mind

    Onwards and downwards!


    Tracy

  14. #14
    Join Date
    Jan 2008
    Posts
    114
    Having a hard time with this one. I am heading in the right direction or not?

    I'm thinking not... but I'm at a loss right now..
    Which data to put into what table. I have read and reread this topic and still im confused. Sorry to be a pain.

    Tracy

    The attachment is a quick mock up.....
    Attached Files Attached Files

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The timeframe of the test is data not meta data. You should have a single table, not four, and the timeframe should be the data in a column in that table, not part of table names.

    Do you store data about people? If so, do you have a personMale table and a personFemale table? Or do you have one table with a gender column?

Posting Permissions

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