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!
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: