Random Duplicate Records

[spectrolab]

Hi Guys,

I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:

Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
    For intCounter = Me.txtStartValue To Me.txtEndValue
        rs.AddNew
        rs.Fields(MyField) = "TP" & intCounter
        rs.Fields("Submission #") = Me.SubNum
        rs.Fields("Sample Type") = "Blasthole"
        rs.Fields("XRF") = "True"
        rs.Fields("LOI") = "True"
        rs.Update
    Next intCounter
    rs.Close
    db.Close
Set rs = Nothing
Set db = Nothing

What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?

Thanks in advance for your help!

 

[Pat Hartman]

Record order in a relational database is controlled exclusively by sorting in a query. Simply opening a recordset, will not produce a predictably ordered set. So, if you want something every 50th record. You would need to determine the unique identifier of each 50th record and insert a row with a value 1 higher than the record you want to duplicate.

Since what you are trying to do is not possible, if you tell us the purpose, someone may be able to offer an alternative.

 

[spectrolab]

Thanks Pat,

I understand what you are saying, let me put my problem another way. All the records in the table are searchable by "SubNum", which is unique for each job, not each record. What I would like to do (it doesn't have to be every 50th) is to pick a number at random from those generated by the code for each "subNum" and add another record to the table with the same number, but with the suffix "DUP". It is for a laboratory system, so the record picked should be random, not the first or last number. Also, if the job, or "SubNum" has more than 50 records, do this twice, that is, output 2 new records with the DUP suffix. More than 100, 3 records, etc. Hope this makes a bit more sense and is possible.

Thanks for your help!

 

[Pat Hartman]

This is going to take a little coding. I would start with a query that counts each set.

qryCount
Select SubNum, Count(*) As Rec_Count From YourTable
Group by SubNum;

You'll need a code loop that reads each record of qryCount and generates a random number between 1 and the value of Rec_Count. You'll then need to open a query that selects only the records for the current SubNum and orders them by their unique identifier. You will read through the recordset sequentially until you get to the number generated by the rnd() function. Copy that data and use the .AddNew method to insert the dup record into the open recordset.

One thing to note is that you need to use the Randomize function to seed the randomizer otherwise, Rnd() will generate predictable numbers. Look up Randomize and Rnd in VBA help.

 

[spectrolab]

This is driving me insane, does anyone have any idea how to do this in VB?

 

[namliam]

Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
dim LastDub as double
dim addString as string
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
randomize
LastDub = Me.txtStartValue
addString = ""
    For intCounter = Me.txtStartValue To Me.txtEndValue
        rs.AddNew
        rs.Fields(MyField) = "TP" & intCounter
        rs.Fields("Submission #") = Me.SubNum &  addString
        rs.Fields("Sample Type") = "Blasthole"
        rs.Fields("XRF") = "True"
        rs.Fields("LOI") = "True"
        rs.Update
        addstring = ""
        if LastDub + 45 < intCounter and rnd < 0.1 then
            LastDub = intCounter
            intcounter = intcounter -1 
            addstring = " DUP"
        endif
    Next intCounter
    rs.Close
    db.Close
Set rs = Nothing
Set db = Nothing

From every 45th records you have about a 10% chance of creating a duplicate. Meaning you should have a duplicate just about around every 50 records on average with a spread between 45 and say 55. No way to be sure offcourse.
Random means you may never trigger a duplicate... but thats random

Hope this helps

Edited to add the Addstring bit...

 

[The_Doc_Man]

spectrolab, first let me say that what you are doing smacks of a questionable record-keeping design philosophy. My "Doc" is a PhD in one of the experimental sciences and is earned, not "awarded" or "honorary." If you need a duplicate record, WHY do you need a duplicate record IN THE SAME TABLE? It seems to me that you are asking for data contamination in your record-keeping process.

A more direct way to do this, consider this viewpoint:

If you want some sort of randomly sampled table, make another table with the same exact format as the one you wanted to sample. Then using the rnd function, multiply the random number by 50 and truncate it to an integer (which will then have a range 0-49). Write an INSERT query that only inserts your record when the random value is some arbitrary number you like. I.e. the random value test appears in the WHERE sub-clause but not the SELECT (field list) sub-clause. If a sample rate of 1/50 is not right, pick any other multiplier and any target number within the range of the random number.

Now, if you MUST consider the original data and the duplicates together, create a UNION query of the two. (The Help files will tell you about UNION queries.) Since the format of one table exactly matches the format of the other, this is a trivial UNION. Any time you don't need to see the duplicates, they are in another table. Use the raw table rather than the UNION query to see only raw data. Just use the other table to see only sampled data.

I don't think you would want to mix your sampled values with the raw values, just as a matter of good scientific bookkeeping. Unless the basic concept of keeping your scientific observation data "pure" has somehow become declasse' since I got my doctorate.

 

[spectrolab]

Thanks for the info doc, it was most helpful.

However, the data in the table is not actual "experimental data" in the strictest sense. It is just a sample submission table, whereby we enter the samples we receive on a batch basis (hence SubNum, the submission number) and assign what tests are required. The people submitting the samples to us would like us to do a random duplicate about every 50 samples (or one per job if less than 50 in a job), which is then "made" in the preparation stage, giving us the original sample, plus its duplicate, which is then treated the same way as all the other samples, to ensure no sample bias and repeatability.

All the experimental results and data are stored in other tables and linked to the sample number from this submission table for result reporting purposes.

 

[spectrolab]

Thanks for the help Mailman, it works well, but i was wondering if it would be possible to have the "DUP" record anywhere in the 50 samples, not just between 45-55. That is, what I am after is to have the "DUP" anywhere from record 1-50, then another between 51-100 etc. Hope that makes sense and is easy to do, if not, then I will stick with your rather easy solution (BTW, what is the DUB function, I can't seem to find any info on it anywhere!)

 

[namliam]

What do you mean? "What is the Dub function" I dont understand your question....

Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
dim ThisDub as double
dim DubEvery as integer
dim addString as string
dim x as integer
dim DoneDub as boolean
DubEvery = 50
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
randomize
ThisDub = int(rnd()*(dubEvery + 1))
DoneDub = false
addString = ""
    For intCounter = Me.txtStartValue To Me.txtEndValue
        rs.AddNew
        rs.Fields(MyField) = "TP" & intCounter
        rs.Fields("Submission #") = Me.SubNum &  addString
        rs.Fields("Sample Type") = "Blasthole"
        rs.Fields("XRF") = "True"
        rs.Fields("LOI") = "True"
        rs.Update
        addstring = ""
        if intcounter(int(intcounter/DubEvery) * Dubevery) = ThisDub and DoneDub = false then
            donedub = true
            ThisDub = int(rnd()*(dubEvery + 1))
            intcounter = intcounter -1 
            addstring = " DUP"
        endif
        if intcounter(int(intcounter/DubEvery) * Dubevery) = 0 then
            donedub = false
        endif
    Next intCounter
    rs.Close
    db.Close
Set rs = Nothing
Set db = Nothing

To your other question, see above...

 

[spectrolab]

Thanks a lot for that, it works really well! Don't worry about my last question, I am just an idiot!

 

[spectrolab]

Pretty Old post but having a few problems

I sent this through quite a while ago, but I have adapted the code to another dB, in the line

If intCounter(Int(intCounter / DubEvery) * DubEvery) = ThisDub And DoneDub = False Then

It gives an error Compile Error: Expected Array, can anyone help or point out what is going wrong?