Storing ID values in field and retrieving them

[fluffyozzy]

Oh god, I will try to explain this as clearly as I can

I have a testing database, where candidates sit through MCQ test and an oral test. The questions for these are selected randomly from a bank (40 questions for multiple choice and 10 questions for oral), gets put in a new table and reports are produced to give the test papers. These are coded already and working well.

Now, I need a way to store the ID numbers of these randomly produced test questions in a textfield (could be separated by a comma in between) of some sort to be reproduced at a later date, both for marking purposes and for scrutiny (if necessary). Could this be done? If so, I then need to be able to use a command button on a form to reproduce these at a later date for each of the candidates.

I tried to do this with simple queries but just couldn't manage it I'm afraid. I'm new at VBA, but learning fast (thanks to this forum largely), so any code suggestions would be very very much appreciated.

Thanks

 

[fluffyozzy]

In its simplest form this would be a two column table, the first column would hold the ID number of the "tblTestDate" table and the second column would hold the randomly selected question number this table could be called "tblTestDetail".

Thanks for replying Uncle Gizmo (loving the username by the way). This sounds good to me, but I need to store 40 questions per person for the multiple choice test (let's say), not just one question on the field "tblTestDetail". So, do I need 40 fields (Q1, Q2 etc) ?

Also, how do I actually store them? What do I need to use in order to enter them automatically into these fields at the same time the papers are produced?

This is giving me a headache, I don't know what it's doing to you but I would really appreciate any help/wisdom you could part with.

Thanks

 

[fluffyozzy]

How is this done?

Hi there, I think I understand your explanation of the table, THANK YOU! I will try to do that and see what happens. I had thought I had understood how to make tables, but obviously not! Back to the drawing board I go...

Your question about randomisation: I have a table of questions with choices (4 in total) and the correct answer number. Each of these questions belong to a criteria (specialised criteria in my industry) and there are many questions in each criteria. A kind soul in this forum have provided me with a FANTASTIC piece of code that goes through the MCQ questions within each criteria, extracts questions through the use of a loop, at the end producing 40 questions for an exam. So, each candidate gets a different set of questions.

Does that answer your question?

Thanks again

 

[fluffyozzy]

So the information you want to save is already stored in a table? Why are you proposing that you need some way of storing the question numbers?

Not quite. Just as I explained above, I have a questions table that stores all the info about the questions (question itself, choices and the right answer). In addition I have a temporary table that renews its contents everytime the randomisation process is run (this table also contains the candidate's answer). This was done so that I could have a demonstrative form for producing a different test each time I ran the code. So, here comes the problem. Because the contents of the temporary table changes each time the randomisation code is run, I lose the ID numbers of the questions that I have done for the last candidate. As I would like to re-produce those test papers for each candidate for scrutiny purposes in the future, I would like to be able to store the ID numbers of these questions somehow.

Does this make sense?

 

[fluffyozzy]

Why not change this process so that instead of replacing the tables data, you keep it by making the process an append process?

mmm, how do I do that??? Could you possibly give me an example?

Here's the randomisation code I'm using, is it there that I need to append? I tried zipping the database, but it's still too big, otherwise I'd post it here for you to look at.

Thank you very much for your replies, I really appreciate it.

---------------------------------

Public Function MakeMCQRandomTable()

Dim intCounterA As Integer, intCounterB As Integer ' Loop Counters
Dim intPosition As Integer ' The current random number position
Dim rst As Recordset ' Recordset for Random Ranges
ReDim intRandom(0) As Integer ' Store for accepted numbers

Set rst = CurrentDb.OpenRecordset("SELECT MCQs.CriteriaID, Min(MCQs.MCQID)AS MinOfMCQID,Max(MCQs.MCQID) AS MaxOfMCQID FROM MCQs GROUP BY MCQs.CriteriaID;")
' MinOfMCQID - Low end of ID range for random selection per CriteriaID
' MaxOfMCQID - High end of ID range for random selection per CriteriaID

Randomize

Do While Not rst.EOF
' Inflate the Random Array for new entry
ReDim Preserve intRandom(UBound(intRandom) + 1)
' Randomly select MCQID in the given range for the current CriteriaID and store in Random Array
intRandom(UBound(intRandom)) = Int((rst!MaxOfMCQID - rst!MinOfMCQID + 1) * Rnd + rst!MinOfMCQID)
rst.MoveNext
Loop

rst.Close

' Clear the MCQRandom table for new entries
CurrentDb.Execute "DELETE * FROM MCQRandom;"

For intCounterA = 1 To 40
' Select Random position within the Random Array
intPosition = Int(UBound(intRandom) * Rnd + 1)
' Fetch MCQID from the Random Array, select matching record from MCQs
' and append to MCQRandom
CurrentDb.Execute "INSERT INTO MCQRandom SELECT MCQs.* FROM MCQs WHERE MCQs.MCQID=" & intRandom(intPosition) & ";"
' Remove selected MCQID from the Random Array and deflate by 1
' to prevent duplicate selections
For intCounterB = intPosition To UBound(intRandom) - 1
intRandom(intCounterB) = intRandom(intCounterB + 1)
Next intCounterB
ReDim Preserve intRandom(UBound(intRandom) - 1)
Next intCounterA

End Function

 

[fluffyozzy]

can append now, here's the next problem

It took a while for me to figure out, but I managed to append the data into a new table called tblMCQRandomAppend, which will hold the records that I want to keep for future re-production.

So, now, I have encountered another problem. The records on the tblMCQRandomAppend need to belong to a group of some sort (say MCQPaperID). I have created a table that contains a field called MCQPaperID (primary key) and put this as a foreign key in the tblMCQRandomAppend, so that I can track down which questions belong in which paper. I created a relationship between these two tables (without the referential integrity) which is one-to-many relationship. So here's the question:

Is there any way to generate the ID number of the Paper as the randomisation process is run?

In other words, how can I generate an ID number for a paper that contains 40 questions and automatically enter this number in both tblMCQPaper (as ID number as the primary key) and enter this number as a foreign key against each question generated by the randomisation? Is this even possible?

Please if anyone can provide an answer to this, it would be much appreciated. I am working on a sample database to put on the forum when all of this is done, so that others can benefit from the solutions to all these niggly problems

 

[fluffyozzy]

I would guess that you are using a form with a command button on it to initiate this process. If you attach the form to a table, then you can make this form so that every time you open it, it generates a new record in the underlying table. On the form you could have a text box which displays the ID from the new record in the underlying table you can then take this I D. into your code and build it into the append routine.

mmm, you come up with solutions I've never even thought of!

I can make the form up I think, no problems there. Code-wise, could you possibly give me an example based on the line of code that appends my data into a table please?

CurrentDb.Execute "INSERT INTO tblMCQRandomAppend SELECT tblMCQs.* FROM tblMCQs WHERE tblMCQs.MCQID=" & intRandom(intPosition) & ";"

I'm really appreciating all this help, thank you so much.

 

[fluffyozzy]

thank you very much Uncle Gizmo! I will try this code and see if I can modify it to suit my needs.

Obviously, this project is a little over my head, I just don't have the experience to do all this, but I definitely learn better from examples, so thanks again.