Using Append Query in a loop to create 6 records in one go.

[Mel_P]

Hello - I want to create a set of 6 records in an Access 2003(2000) database. I can create a record thus (SQL from append query):

INSERT INTO tblTest ( AssessmentID, AssessmentNumber, Hazard, [q(y/n)], [c(y/n)], info, moreinfo )
SELECT [Input AssNo] & [Input Hazard] AS AssID, [Input AssNo] AS AssNo, [Input Hazard] AS Haz, 'Y' AS q, 'N' AS c, 'lots' AS Inf, 'Lotsmore' AS morinf;

[Input Hazard] has 6 values (e.g. A, B, C, D, E, F) for each value of Input AssNo - which is a number e.g. 1339?

A, B, C, D, E, F are always the same so they can be "hard coded".

How do I get a query to loop and create 6 records please?

Thanks, Mel

 

[pbaldy]

The only pure SQL way would require a table with those 6 values in it as 6 records. Otherwise you're looking at a VBA loop, I suspect.

 

[Mel_P]

Do you mean another "look up table"?

Mel

 

[pbaldy]

Basically yes. With that, if next week it needed to be 7 records, all you'd have to do is add a record to this table.

 

[Mel_P]

I have a table of 75000 records in sets of 15 and I have to add 4 records for each set to make these sets of 18, i.e. 80,000 records!