Results 1 to 8 of 8
  1. #1
    grussell is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2013
    Posts
    4

    New design and frozen by confusion

    I may have gotten my self in over my head. I volunteered to develop a data base for a diabetes prevention program, when I saw that they were using excel spreadsheets to capture the data.



    I have not used Access since version 2003 was new and the application I used it for was very basic.

    I have attached a sample of the Excel spreadsheet (Baseline.xls) and a Access 2007 table (DPP Reduced Data Set.accdb) that is a direct import of the spreadsheet.

    I have developed a database (CWO Model 20130828.accdb) which I believe represent third normal form for how I should reorganize the data from the spreadsheet.

    I don't know where to go from here and I am not sure of my design. The portion of the design that addresses the data for the three questionnaires that clients complete, is something that I copied from from researching how to handle questionnaires. I don't know how to implement this in a form that is user friendly.

    Finally I don't know how to go about importing the data into my new design. Should I be working with the Excel spreadsheet or would it be easier to work from the Acces version of the same data?

    Any advice would be greatly appreciated. As this is my first post please let me know if I should be posting under a different topic.

    Thanks
    grussell

    PS My attachments did not go. What did I do wrong?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you zip the files (can be in one zip folder)?

    A 'user friendly' questionnaire form would not be easy.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    grussell is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2013
    Posts
    4

    Here are the files I tried to attach with my original post

    Hope these files will help explain my problem and someone can give me some guidance.

    CWO data.zip

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I see a circular relationship with the client survey tables. Why would tblSurveysType be joined to tblClientSurvey?

    Is there a variety of standardized sets of questions and different sets for different respondents? Or just one set of questions that everyone must answer?

    Questionnaire db has been discussed in forum. Search and should get a few.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    grussell is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2013
    Posts
    4
    Thanks June7 for pointing out the circular reference. I had not worked on this part of the design yet as I was not sure how to handle the questionnaires.

    There are three standard questionnaires that all clients must answer at some point. They may be asked to retake a survey at a later time for comparison purposes. The responses for these surveys need to be saved.

    Part of my dilemma is how to design the form for each of these questionnaires. Maybe I will find something in the posts on questionnaires that will point me in the right direction.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ideally, there will be one form for inputting user responses.

    Select the survey set and limit the choice of questions in combobox based on the survey ID. Select question ID in record, input user response. Move to next record, select a question, enter response. Repeat until all responses input. A record for each question response. I know, not a 'user friendly' questionnaire style but that is data entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    grussell is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2013
    Posts
    4
    Good Morning

    I have been perusing the site for ideas and advice on designing questionnaires and surveys. I am amazed by all the different approaches there are to this topic. On the surface it seem like it should be straight forward but the more I look the more confused I become. Is this unique to Access or would I find the same amount of confusion with another database program? I have looked at Duane Hookom's program (At Your Survey) and it is very sophisticated and I think much more than I need. I only need to capture the responses for three different surveys and the questions never change. I can upload the three forms if it would help. If someone can point me to a sample database that might accommodate my needs, I would appreciate it very much.

    My other issue is how do I manipulate the historical data to get it into my new Access 2007 design. I have attached samples of the data, which was originally saved in Excel files. I did a direct import of this data into an Access table. The raw data is not normalized and I am not sure whether I should first make changes to the data organization in the excel file before importing or can I do all the cleanup within the access table.

    As I am just beginning to learn about VB it would be very difficult if I have to utilize it. Any help would be appreciated.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is a lot to cover, too much for this medium.

    I have already described the simplest approach to data entry of responses. Beyond that will probably require a lot of VBA code.

    Normalizing data can be done on the Access side but you might find it easier to organize the tables in Excel first. Depends if you want to use autonumber type field as primary key.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. DLL Confusion
    By frankvfox in forum Access
    Replies: 6
    Last Post: 08-11-2013, 07:22 AM
  2. Replies: 17
    Last Post: 11-13-2012, 03:28 AM
  3. Replies: 3
    Last Post: 07-12-2012, 06:36 AM
  4. VB and SQL confusion
    By Wayne311 in forum Programming
    Replies: 29
    Last Post: 01-31-2011, 10:27 PM
  5. Confusion
    By colenzo in forum Access
    Replies: 4
    Last Post: 07-21-2009, 06:38 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