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

 

[Uncle Gizmo]

You need to think in terms of using tables to store information. From your question I understand that you have a test which is generated by picking questions randomly from a question bank table. You want to store these random selections so that you can reproduce the test at a later date. The first thing to do is make a table for storing the test name and the date of the test call it "tblTestDate". Then you need a table to store the random numbers in. 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".

To reproduce the exam questions you would just have to filter on the "tblTestDate" ID

 

[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

 

[Uncle Gizmo]

So, do I need 40 fields (Q1, Q2 etc)

No, you have to think in terms of tables, you have to think in terms of each row in the table representing a piece of information. This is very difficult to do when you start learning Access in fact I am only just beginning to do it automatically and still have to watch my thinking.

I just thought of this analogy, so I will state it to see if it's makes sense .

You know in XL there's the option to transpose your data from rows into columns. So imagine two rows, the top row is the field names like your post states (Q1, Q2 etc), the field names in the top row and the value in the row underneath. Now imagine selecting these two rows in XL and then pasting back as two columns. In other words when you catch or self thinking of making a field for storing the same item in, have a rethink as you are probably doing it wrong. The "question" is the item (or group), not the question number. What I mean is the question is the group of data and the question number represents one item in that group, therefore the group "question" should be represented by one field in your table.

Looking at your idea of a separate field for each question, imagine a situation where there were more than 40 questions what if you took it to the extreme and said there were a 1000 questions? Not only are you way over the number fields allowed for in Access, but more importantly the construction of the data base would be an horrendous task. And extracting any useful information would be painful and possibly impossible.

 

[Uncle Gizmo]

The questions for these are selected randomly

How is this done?

 

[Uncle Gizmo]

gets put in a new table

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?

 

[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?

 

[Uncle Gizmo]

temporary table that renews its contents everytime the randomisation process is run

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

 

[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

 

[Uncle Gizmo]

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.

 

[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.

 

[Uncle Gizmo]

There's probably a very easy way to do this with queries which I don't know about . I have used both ADO and D. A. O. code in this function , I used code from previous functions, and I am too lazy to convert the DAO to ADO, Although it looks very simple as all you do is Swap the "D" and the "A" around.


You will need a reference set to DAO in the references of your access database for this code to work.

Public Function fImpSpread(intBatchNo As Integer) As Integer
On Error GoTo HandleErr
'This routine is used exclusively in the form frmJob. it needs a batch number to work on,
'the batch number is obtained from the field "txtEPDRID" in the form frmJob
'This function also returns the number of records added

'The purpose of this function is to extract the serial numbers of the EPD's from an attached spreadsheet.
'The spread sheet name is "EPD_List" if this attached spreadsheet does not appear in the database then
'the update will cancel itself.
'the extracted serial numbers are added to the "tblUnits" table.

Dim DB As DAO.Database
Dim rs As DAO.Recordset

Dim strConn As String
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command

Dim strSQL As String
Dim strSQL_Insert As String
Dim strText As String

Dim strIn1 As String
Dim strIn2 As String
Dim strIn3 As String
Dim strIn4 As String
Dim strInsertDate As String
Dim intCounter As Integer

'Remmed Out
'strInsertDate = SQLDate(Date) 'Convert local date to SQL Date format
'Debugging
'MsgBox " Date >>> " & strInsertDate

strIn1 = "INSERT INTO tblUnits ( UnitRepairSerialNo, UnitRepairBatchNo, UnitRepairMoveDate ) VALUES ('"
strIn2 = "', "
strIn3 = ", "
strIn4 = ")"

strSQL = "SELECT EPD_List.F1 FROM EPD_List"
Set DB = CurrentDb

Set adoConn = CurrentProject.Connection
Set adoCmd = New ADODB.Command
    
    'Open a Recordset and loop through it to fill Units table from a spreadsheet
    
    Set rs = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until rs.EOF
            intCounter = intCounter + 1
            strText = rs!F1
            'Remmed Out
            'strText = PadOut(strText, 8, 0) 'Adds a set of Prefix "0000" to make the number
                                            'the correct format for a serial number
            
    strSQL_Insert = strIn1 & strText & strIn2 & intBatchNo & strIn3 & strInsertDate & strIn4
'Debugging
 '   MsgBox " >>> " & strSQL_Insert
        'Use a Command Object to issue an SQL statement
        With adoCmd 'Insert records into the table just created.
            .ActiveConnection = adoConn
            .CommandType = adCmdText
            .CommandText = strSQL_Insert
            .Execute
        End With
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set DB = Nothing

fImpSpread = intCounter

ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err.Number  'This gets the Access internal Error Code
    Case -2147467259
    MsgBox "Error Number >>  " & Err.Number & "   Error description >> " & Err.Description & "   "
    MsgBox "You have already added Unit >>>  " & strText & "    " _
    & vbNewLine _
    & vbNewLine _
    & " ENDING UPDATE NOW!!!!   "
Resume ExitHere
        'Resume Next
    Case 3078
    MsgBox "Cannot Find the Linked Spreadsheet. It Should be Named 'EPD_List'  " _
    & vbNewLine _
    & vbNewLine _
    & " ENDING UPDATE NOW!!!!   "
Resume ExitHere
        'Resume Next
    Case Else
        MsgBox Err & ": " & Err.Description, , "fErrorLayout" 'Where the error occurred
    End Select
    Resume ExitHere

End Function

 

[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.