Results 1 to 9 of 9
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Questionnaire with answers submitted on Access... Replaced by new questionnaire version

    Okay, I have a big one. Sorry, and thank you in advance.



    I have a paper/word type form which will be entered onto access by one person.

    While building the forms for the database, I had the sudden realisation we would have problems when a new version of the form came out which removed/added/split up questions.

    So, I want to be able to create a new version of the form in access in as simple a way for the user as possible, where they can:
    Automatically pull through selected questions from the most recent version - most of the questions will be consistent throughout so having to select them all would be annoying
    Include questions from older versions - I'm not sure, but this may occur an would be easier if this is an option
    Add new questions - would be appropriate in the cases for added/split up questions
    Change "risk" of the question - each question has a "risk" of high, medium or low, occasionally a question may be re-categorised so this would need to be included somehow.

    We may occasionally need to submit older versions to access (where the "assessor" has submitted late/not used the most recent) so we would need to keep the old versions in the database for this.

    The only way I can think of doing this is by creating a new table and adding it to the relationship etc. each time... I'm not even sure if that's possible, let alone if it would work

    Your help is much appreciated.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    No,a tQuestion table would have the
    [Qid], [q#],[Q],[batch]

    then the question answer selections can be in tQAns:
    [qID], opt#, AnsTxt, IsPicked

    Pick the questionaire batch
    user can pick from the options for each question.

  3. #3
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Sorry, I'm not sure I understand, could you explain in more detail?

    I have updated the database, so please find attached the latest version.

    Thanks
    Attached Files Attached Files

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I did not look at the attachment. In general: regarding your need to accommodate 2 versions of forms/questions. I would suggest you remain with one table for questions, not make a second - and instead add a field that flags which version the question is part of. Possibly just a checkbox/yes/no or a number with a value i.e. 1,2,3 This way you can use a query to provide the correct record set.

  5. #5
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, thanks. Is there a way to get each question displayed on the form one below the other so that the user can just select the ones they will need in that version?

    If you can, what I'm thinking is there would be a text box at the top where the user enters the version number, then below this a list of all the questions and a check box (preferably automatically checked) to indicate that the question is in the form - this would then pull find the version number from the text box and enter this in the table.

    Hopefully that makes sense, if not just ask and I'll try again.

  6. #6
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    The continuous form type (rather than the single form type) will display records (in this case questions) all stacked with a scroll so one can have as many as one wants/needs.

    A form fundamentally has a record source - which in your case would be a query you make. A query, in turn, has criteria whereby the results change as the criteria changes. In your case version 1 or 2 or 3.

    One can call criteria from a form's text box, or one can create a parameter prompt in the query itself.

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, I'll give that a go.

    With the "AddAssessment" and "AddLowRiskAssessment" etc. forms, I believe I want something similar, however since I will be adding data to the respective tables, I believe I am unable to do it in the same way, have you got a way around it?

  8. #8
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I have noticed the problem mentioned above also exists in the "AddLowRiskFormVersion" form (it will also apply to the Medium and High risk forms, but it will be exactly the same and I have not yet produced them), I have attached my database so you can see what I mean.
    Attached Files Attached Files

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, (I think) I've done what you suggested, it has mostly worked, however: when changing the risk level for an individual question/behaviour, it also changes the risk level at the top - which I am using so that the user knows they are on the correct page. Is it possible to get this to stay the same?

    Also, if someone "unchecks" one of the boxes in the "In Version __" column, and clicks the "Update" button, I would like this row to be deleted from the table - is this doable?

    Once again I have attached the database for your use, the form I have done this for is "EditLowRiskFormVersion" and the entry for the textbox to get anything to display is 4.
    Attached Files Attached Files

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

Similar Threads

  1. Questionnaire Database
    By phool4fool in forum Forms
    Replies: 2
    Last Post: 12-16-2012, 07:40 PM
  2. Questionnaire Database
    By phool4fool in forum Access
    Replies: 1
    Last Post: 12-13-2012, 04:13 PM
  3. Questionnaire DB from Scratch
    By jomby in forum Database Design
    Replies: 4
    Last Post: 05-18-2012, 01:20 AM
  4. Replies: 1
    Last Post: 04-10-2012, 04:21 PM
  5. Sorting a questionnaire by gender
    By uchiha37 in forum Access
    Replies: 1
    Last Post: 10-07-2010, 07:37 AM

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
  •  
Other Forums: Microsoft Office Forums