Access 2010-Combining Make Table and Append Queries

[Taffski]

I need to be able to create random practice examinations from a single data table of questions.

The questions are grouped by Learning Objective Codes.

At present, I have achieved this by creating a Make Table Query selecting n questions from LO Code 1. I have then created a series of Append Queries selecting n questions from each of the other LO Codes 2 - 10, etc.

The final table is used to produce the report.

This does work but the Query section of the Access Objects viewer is somewhat cluttered to say the least and I still have many subjects to go.

I have tried copying the SQL from all queries into one but that simply returns errors.

There must be a simpler way of doing this.

Any ideas?

Grateful for your learned advice.

 

[JamesMcS]

I usually use a for loop in VBA to do this kind of thing, although I suspect there will be a more efficient way:

Sub Append_Questions()
Dim LONumber as integer

docmd.runsql "DELETE * From Questions;"

For LONumber = 1 to dmax("[LO Number]", "Questions")

docmd.runsql [insert sql from append query here], "WHERE [Questions].[LO Number]=" & LONumber & ";"

Next LONumber

End Sub

or something like that anyway.