Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Smile Unanswered: Add records based on user input

    I'm new to this group and fairly new to MS Access.

    I am using Access 2010 and Windows 7.

    Not sure how to ask this, so here it goes.

    I have a table with 218 questions (tblHID). Each question needs to be answered a number of times depending on user input. My form is based on a parameter query (qryHID) that asks the user how many sets of questions they need (1 through X). What I need to do is:
    - Based on the answer given (1 through X), replicate the 218 questions that many times (e.g., if the answer is 2, replicate 2 times; if the answer is 50, replicate 50 times)
    - Take the value (1 through x) and put in a designated field in tblHID.

    I think I need to use an append query, but I'm not sure how to take the value provided in the parameter query and replicate the questions x amount of times.

    I hope I explained this correctly!
    Thanks in advance!
    Robin

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a relational model, you do not duplicate rows. As you describe the problem, you should have 3 tables (at least): the table with the questions (tblHID), a table with the candidates (i.e. people who answer - or request - the questions) and a junction table (see: Junction table - Wikipedia, the free encyclopedia) that associates the questions and the candidates. this table should contain a foreign key to the questions table, a foreign key to the candidates table (rows with both same foreign keys are OK) and a column that identify the row number in a set of duplicate rows containing the same foreign keys. The association of those 3 columns provides the primary key of this junction table. There can be other columns in the junction table, for instance the date and time for each question (if relevant).

    If the candidate 49 needs a set of 12 times the question 118, you create 12 rows in the junction table, like this:
    Code:
    RowNumber | PK_Questions | PK_Candidates
    ----------+--------------+--------------
        1     |      118     |     49
        2     |      118     |     49
        3     |      118     |     49
        4     |      118     |     49
        5     |      118     |     49
        6     |      118     |     49
        7     |      118     |     49
        8     |      118     |     49
        9     |      118     |     49
       10     |      118     |     49
       11     |      118     |     49
       12     |      118     |     49
    You can very easily know how many times the candidate 49 uses the question 118 using:
    Code:
    SELECT COUNT(*) FROM TblJunction WHERE PK_Candidates = 49 AND PK_Questions =118;
    If you can garantee that RowNumber will always be in contiguous sequence and always begin at 1 (hard to obtain), you can also use:
    Code:
    SELECT MAX(RowNumber) FROM TblJunction WHERE PK_Candidates = 49 AND PK_Questions =118;
    Have a nice day!

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
  •