Append and Update 10% of records to say "Yes" and other 90% say "N

[Guest]

I have a main table and a completed table.
At the end of each week an append query is ran to move all comleted records
from the main table to the completed table. Now I need 10% of them to be
marked for QC. I added a column labeled QC to my completed table and am
trying to mark 10% of the appended records as "Yes".
I want to append all (100%) of the records but in the process I want 10% of
them to say Yes in the QC field while the other 90% stays null or says "No"
(I don't really care, as long as they do not say Yes).
I know there are ways to use randomizers and Select Top 10% but I don't know
how to use them or if there is something better I could use.
Thanks for your help.

 

[Guest]

You should never have two tables with the same data in them. For example to
see both the completed and active records you need to do a Union query which
is slow and sloppy. Also when moving the records over from the Main table to
the completed table, there's always a chance that something goes wrong like
the records not making it to completed yet still deleted from Main or the
opposite where the records don't get deleted in Main and you now have copies
of the records in completed.

So.... What you really want is a checkbox in your Main table that says
something like Completed with the default set to No. When the record is
completed, set it to Yes. Base all your queries on this Yes/No Completed fied.

 

[Guest]

The reason I am moving them to a completed table is because the main table
will continue to get larger and larger and I do not want to slow processors
down (working through forms)with a bunch of completed items.
So, any idea on how to select/update that 10% for QC?

 

[Brendan Reynolds]

Jerry makes a good point - archiving isn't always a good idea. It's worth
thinking about things like whether you will ever need to produce reports
that included both completed and uncompleted records.

That said, here's one way of updating approximately 10% of records. This one
depends on the table including a more-or-less sequentially numbered field.
An occasional gap in the sequence shouldn't skew the results significantly
....

UPDATE Orders SET Orders.TestBool = True
WHERE ((([OrderID] Mod 10)=0));

Here's an alternative, likely to be more resource-intensive, but doesn't
depend on any sequentially numbered field in the table ...

Public Sub TestRandTen()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngRand As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TestBool FROM Orders ORDER BY
OrderDate")
Randomize Timer
Do Until rst.EOF
lngRand = Int(10 * Rnd + 1)
If lngRand = 10 Then
rst.Edit
rst.Fields("TestBool") = True
rst.Update
End If
rst.MoveNext
Loop
rst.Close

End Sub