Programatically add new sequenced records

[jmd]

tblScoringTable
ScoringID PK
TicketNumber
QuestionNumber
ChoiceNumber

Ticket number is pre-populated from another external database via an append
query, the remaining fields have no data.

TicketNumber QuestionNumber ChoiceNumber
12345
99999
77777
55555

I have scoring survey/questionnaire with 13 questions (in another table).
For each TicketNumber, I need the QuestionNumber to be populated with 1
through 13, thus 13 records for each ticket. ChoiceNumber will remain blank
until I evaluate/score the ticket. The result would look like this.

TicketNumber QuestionNumber ChoiceNumber
12345 1 Null
12345 2 Null
12345 3 Null
12345 4 Null
12345 5 Null
12345 6 Null
12345 7 Null
12345 8 Null
12345 9 Null
12345 10 Null
12345 11 Null
12345 12 Null
12345 13 Null
99999 1 Null
99999 2 Null
99999 3 Null
99999 4 Null
99999 5 Null

and so on... How could I programmatically or via query, populate the table
with the existing TicketNumber. If ChoiceNumber is "not null" then it
wouldn't create a record. Any help would be muuuch appreciated, I'm googled
out and these forums are thee best.

 

[Allen Browne]

Presumably you are starting with a table that has fields:
TicketNumber one record for each ticket number
Quantity number of records desired (e.g. 13)
(If not, you can make such a table by creating a query into tblScoringTable,
and turning it into a Make Table query.)

Next, create a table that teaches Access the numeric sequence.
Create a table with just one field, type Number, named (say) CountID.
Save the table as tblCount.
Enter records from 1 to the largest question number you will ever need.
There's some code in the end of this link that can do that programmatically:
http://allenbrowne.com/ser-39.html

Now create a query that uses your TicketNumber table (where you have 1
record for each TicketNumber), and tblCount. In the upper pane of table
design, there must be no line joining the 2 tables. (This gives every
possible combination.)

Now drag tblCount.CountID into the grid, and under it enter criteria:
<= [Quantity]
This limits the number of records to the correct number of questions.

After checking that this works, turn this query into a Make Table (or
Append) query, to write the records into your final table.

 

[jmd]

I'm going to try it out and post the result, thanks for responding.

Presumably you are starting with a table that has fields:
TicketNumber one record for each ticket number
Quantity number of records desired (e.g. 13)
(If not, you can make such a table by creating a query into tblScoringTable,
and turning it into a Make Table query.)

Next, create a table that teaches Access the numeric sequence.
Create a table with just one field, type Number, named (say) CountID.
Save the table as tblCount.
Enter records from 1 to the largest question number you will ever need.
There's some code in the end of this link that can do that programmatically:
http://allenbrowne.com/ser-39.html

Now create a query that uses your TicketNumber table (where you have 1
record for each TicketNumber), and tblCount. In the upper pane of table
design, there must be no line joining the 2 tables. (This gives every
possible combination.)

Now drag tblCount.CountID into the grid, and under it enter criteria:
<= [Quantity]
This limits the number of records to the correct number of questions.

After checking that this works, turn this query into a Make Table (or
Append) query, to write the records into your final table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tblScoringTable
ScoringID PK
TicketNumber
QuestionNumber
ChoiceNumber

Ticket number is pre-populated from another external database via an
append
query, the remaining fields have no data.

TicketNumber QuestionNumber ChoiceNumber
12345
99999
77777
55555

I have scoring survey/questionnaire with 13 questions (in another table).
For each TicketNumber, I need the QuestionNumber to be populated with 1
through 13, thus 13 records for each ticket. ChoiceNumber will remain
blank
until I evaluate/score the ticket. The result would look like this.

TicketNumber QuestionNumber ChoiceNumber
12345 1 Null
12345 2 Null
12345 3 Null
12345 4 Null
12345 5 Null
12345 6 Null
12345 7 Null
12345 8 Null
12345 9 Null
12345 10 Null
12345 11 Null
12345 12 Null
12345 13 Null
99999 1 Null
99999 2 Null
99999 3 Null
99999 4 Null
99999 5 Null

and so on... How could I programmatically or via query, populate the table
with the existing TicketNumber. If ChoiceNumber is "not null" then it
wouldn't create a record. Any help would be muuuch appreciated, I'm
googled
out and these forums are thee best.

 

[jmd]

It worked, I'm in love. Thank you.

I'm going to try it out and post the result, thanks for responding.

Presumably you are starting with a table that has fields:
TicketNumber one record for each ticket number
Quantity number of records desired (e.g. 13)
(If not, you can make such a table by creating a query into tblScoringTable,
and turning it into a Make Table query.)

Next, create a table that teaches Access the numeric sequence.
Create a table with just one field, type Number, named (say) CountID.
Save the table as tblCount.
Enter records from 1 to the largest question number you will ever need.
There's some code in the end of this link that can do that programmatically:
http://allenbrowne.com/ser-39.html

Now create a query that uses your TicketNumber table (where you have 1
record for each TicketNumber), and tblCount. In the upper pane of table
design, there must be no line joining the 2 tables. (This gives every
possible combination.)

Now drag tblCount.CountID into the grid, and under it enter criteria:
<= [Quantity]
This limits the number of records to the correct number of questions.

After checking that this works, turn this query into a Make Table (or
Append) query, to write the records into your final table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tblScoringTable
ScoringID PK
TicketNumber
QuestionNumber
ChoiceNumber

Ticket number is pre-populated from another external database via an
append
query, the remaining fields have no data.

TicketNumber QuestionNumber ChoiceNumber
12345
99999
77777
55555

I have scoring survey/questionnaire with 13 questions (in another table).
For each TicketNumber, I need the QuestionNumber to be populated with 1
through 13, thus 13 records for each ticket. ChoiceNumber will remain
blank
until I evaluate/score the ticket. The result would look like this.

TicketNumber QuestionNumber ChoiceNumber
12345 1 Null
12345 2 Null
12345 3 Null
12345 4 Null
12345 5 Null
12345 6 Null
12345 7 Null
12345 8 Null
12345 9 Null
12345 10 Null
12345 11 Null
12345 12 Null
12345 13 Null
99999 1 Null
99999 2 Null
99999 3 Null
99999 4 Null
99999 5 Null

and so on... How could I programmatically or via query, populate the table
with the existing TicketNumber. If ChoiceNumber is "not null" then it
wouldn't create a record. Any help would be muuuch appreciated, I'm
googled
out and these forums are thee best.