divide a table into equal groups, using a calculated field based on total records

[angelagh]

Greetings,
I'm trying to figure out how to do the following (based on the "return" query setup). I've searched for several hours across several sites and can't find something that fits the following:

1. I will be importing an excel spreadsheet
2. randomize (rnd) & count total number of records (calculated)
3. based on this total number of records, return 1/10th of the records (which I will later move to another table and delete from the original table).

I was using the "return 10%" not thinking about this was not an equal 10th of the total number (i.e. 1000 records, I need 100 randomized records returned on each query (result)).

If the answer is VB, I need VERY clear instructions as I'm not versed in VB ... I've been sticking with SQL, queries, and macros.

Thanks,
Angela

[gnawoncents]

Angela, welcome to Bytes!

If you only need to return the 10% once, simply modify the "Top Values" field in your append and delete queries to 10% (it will automatically round to a whole number). If, however, you need to do this repeatedly (i.e. 1000 returns 100, then 900 returns 100, 800 returns 100, etc. until the list is exhausted), then we need to do some additional coding. Nothing too crazy, but VBA would be my choice. Below is one example of how it could be done.

Private Sub btnXfer_Click()
 
Dim strSQL As String
Dim intTotal As Integer
Dim int10 As Integer
Dim intCount As Integer
 
'Count all the records in the table
intTotal = DCount("*", "tbRecords")
intCount = intTotal
 
'Determine 10 percent of total records
int10 = intTotal / 10
 
DoCmd.SetWarnings (False)
 
Do While intCount > 0
 
    'Set up and run SQL for transfer query
    strSQL = "INSERT INTO tbRecords10 " _
    & "SELECT TOP " & int10 & " tbRecords.* " _
    & "FROM tbRecords;"
 
    DoCmd.RunSQL strSQL
 
    'INSERT ANY ADDITIONAL ACTIONS HERE THAT YOU WANT TO HAPPEN
    '
    '
 
    'Set up and run SQL for delete query
    strSQL = "DELETE FROM (SELECT TOP " & int10 & " * FROM tbRecords);"
 
    DoCmd.RunSQL strSQL
 
    'Subtract the records transferred from the count remaining
    intCount = intCount - int10
 
    'INSERT ANY ADDITIONAL ACTIONS HERE THAT YOU WANT TO HAPPEN
    '
    '
 
Loop
 
DoCmd.SetWarnings (True)
 
End Sub
 

In this example, the action happens when pressing a button named btnXfer. The code will move 10 percent of the records at a time from the source table (tbRecords) to another table (tbRecords10), then delete them from the original table. Of course, this is fairly useless, unless you are performing other actions in between or changing the destination table each time. So, I’ll leave it to you to decide what you need and modify as appropriate.

[twinnyfo]

angelagh,

gnawoncents is definitely on the right track. However, could you clarify the requirement to randomize? How does this play into your transfer of records? Are you simply trying to gather a random sample of the records divided into 10 equal chunks?

And, btw, VBA will be your best option, as gnawoncents has presented.

The more information you provide, the better able we will be able to assist.

[Rabbit]

You can't use Rnd() by itself to get a randomized sample. You need to supply a seed number that is based off multiple time components and a unique record component. Otherwise, Rnd() always returns the same number.

Also, you need to ask yourself how random does this pseudo random need to be. 99% of use cases it is fine to use the build in PRNG. If it is critical to achieve something close to random, like a lottery or slot machine, then the VB implementation of Rnd() used by Access won't cut it. You'll need to implement a PRNG algorithm.

EDIT: Looks like a time component is not needed. I could've sworn it was required to generate different random numbers each time the query is run. Maybe it's because I'm using a newer version of Access or I'm thinking of SQL Server

[gnawoncents]

Depending on how the database is set up, the randomizing function can also be done when importing the records from Excel by assigning an ID field in the new table that has a unique random autonumber. It all depends on what the goal is.

[angelagh]

Hi everyone:

twinnyfo is on the right track of what I'm needing to accomplish.

here are some answers:

1) I need 10 equal groups (not 10%). I had originally started w/ the 10% but realized that doesn't give me the 10 groups I need.

2) I am using the RND option because I need the imported data to be randomized before selecting the group (it's an inventory database)

3) I've setup the DB so the user would access the DB and "select" the button to show "inventory group". I then move that group to another table, delete the group from the original table. After the 10th month (user has viewed/printed the 10th group). The 11th & 12th function could be 10% and 20% from the second table (this part is easy!).

--------
I'm able to randomize them and use the "return" feature in the queries...but that doesn't give me the 10 equal groups I need from the total available.

Note: total records can and will vary -- otherwise, I know I could just divide the number of records by 10 and enter that number into the return field.

THANKS!!

[twinnyfo]

Angelagh,

Is this first table a one-time import, or is it a living table? If it is a one-time import, the only challenge I see is maintaining the original number of records (to determine what "10%" of the original is. What happens to the records in the second table after everything is done? Are these maintained or deleted?

Likewise, I am not certain this requires a copy to a new table and a delete from the original table. If there is a Flag in the first table that indicates whether the record has been viewed, all could be accomplished on one table (experience makes me very cautious to use the copy, paste and delete method--although it can work that way).

For example, if you import 1,000 records, at the time of the import, you have zero records with the "Viewed" flag set to true.

The first User accesses 100 random records, at which time, the "Viewed" flag is set to True on those 100 records. The next user accesses an additional 100 random records of those remaining with "Viewed" still set to False. This continues for the ten sets of records.

Not sure if this is crystal clear.

Using the copy/paste method, you could simply randomly choose one record, copy, paste and delete it from the first table, and reiterate that process 100 times (or whatever the 10% value would be). Also, the sum of the number of the records in both tables, will always give you your original number of records for calculating 10%.

Hope this is starting to get you moving toward a solution.

[angelagh]

Hi twinnyfo:
I'll try to answer your questions:

1) 1 time (per year) import

2) my "hope" was that I could calculate upon import total number of records / 10; then set a parameter (somewhere) that populate a macro (button) so each time it was clicked, would produce that set number. For example, if original import = 200 records; calculation (in background) would be 200/10= 20. 20 is then carried over to a macro (button) so each time the user want's to extract the 1/10th list...it would give them 20 random records.

3) I don't know how to set a view flag (remember no VB experience) :)

4) I did the copy/paste/delete method because that's what I know. I figured this allows the user to view what has been inventoried and what has not been inventoried. After the 10th data pull; there will be 2 additional pulls - one for 10% and another for 20% (from the original recordset (in the example 200 records). These 2 additional pulls, I've designed to come from the 2nd table (the one I essentially moved the data from table 1 to table 2 throughout the 10 data pulls.

5) This years dataset includes over 8000 records, so it's not a small number.

6) right now, I'm figuring I can do this behind the scenes. Formulas to count than divide by 10 and than I manually enter the 1/10th number into the query "return" field. This is what I'm trying to automate so someone without Access experience can just click a button to import a spreadsheet and doesn't have to worry about anything else.

----
Note: I say I know enough in Access to get me into trouble! Such as this situation -- I've got everything working how I want them to with the import, create random field, copy, paste, delete, return random sets....delete everything upon import...splash window upon open...the only remaining item is this dynamic calculation...

[twinnyfo]

angelagh,

It sounds more like the only real question you have is how to save this "10%" number (or the original number of files)?

If this is the case, just create a tiny table with one field, a numeric value for the number of records imported. Every time you import a spreadsheet, you count the number of records and save it to that table. When a person wants to inventory the records, the DB looks at that value, divides by 10 and copies the records.

That really is about as simple as it can get.

I only say this, because it appears that all the wheels are currently in motion, except for the ability to track the initial number of records. DB structure and design are different things altogether, but this should provide you the necessary solution.

Please let me know if I am missing something else.

[gnawoncents]

Angela,

Below is some code you may find useful and can modify to meet your needs.

Assumptions:
- There will be no changes to the inventory items (no record additions or deletions) once imported
- You wish to inventory 10% of the total records every month
- After 10 inventories, you want to have inventoried 100% of total inventory
- You want to inventory a new random 10% set for each of months 11 and 12, having inventoried another 20% of the original 100% at the end of the year

The way this works:
- It runs after clicking a button named btnInventory
- The first function helps determine whether or not the necessary columns (fldInvMonth, fldInvMonth2, and fldRandom) have already been added
- If the fields do not already exists in the imported table (tbRecords for purposes of this code), they will be created
- Each record is assigned a random number (fldRandom), then sorted and assigned an inventory month (1-10) in fldInvMonth in increments of 10% for a total of 100%
- All records are then assigned a NEW random number (fldRandom), then sorted and assigned an inventory month (11-12) in fldInvMonth2 in increments of 10% for a total of 20%

How to use this in your database:
There are a couple of main ways this can be implemented: you can have the code run every time someone selects an inventory (nothing will happen unless it is the first time), or run it once and have a separate button/selector to choose the inventory month. Either way, you would use either the current month or a month/inventory selector (1-12) to filter your destination form/report for only those records where fldInvMonth or fldInvMonth2 = the selected month/inventory.

The benefit of doing it this way is that you do not have to create an extra table or move any records. Also, it lets you keep a historical record of what items were inventoried for each month (and even look ahead at what records will be inventoried in coming months, should you desire this option).

Function DoesFieldExist(sField As String, sTable As String) As Boolean
 
Err.Clear
DoesFieldExist = False
On Error GoTo setfalse:
If (DCount(sField, sTable) = 0) And Err Then DoesFieldExist = False Else DoesFieldExist = True
 
setfalse:
 
End Function
 
Private Sub btnInventory_Click()
 
Dim dbsInv As Database
Dim rstInv As Recordset
Set dbsInv = CurrentDb
 
'Test to see if needed fields have already been created and, if not, create them
If DoesFieldExist("[fldRandom]", "tbRecords") = False Then
 
    'Add the following fields and types to the tbRecords table: fldRandom (number), fldInventoried (Y/N), fldInvMonth (Integer), fldInvCurrent (Integer)
    dbsInv.Execute "ALTER TABLE tbRecords ADD COLUMN " _
        & "fldRandom LONG, " _
        & "fldInvMonth NUMBER, " _
        & "fldInvMonth2 NUMBER;"
 
    'Assign a random number to each of the records
    Set rstInv = dbsInv.OpenRecordset("tbRecords")
    rstInv.MoveLast
    rstInv.MoveFirst
        Do While Not rstInv.EOF
            rstInv.Edit
            rstInv!fldRandom = Rnd * 1000000000
            rstInv.Update
          rstInv.MoveNext
        Loop
 
    '------------------------------------------------------------------------
    'Assign an inventory month (1-10) to ten equal record sets (+/- 1 record)
    '------------------------------------------------------------------------
    DoCmd.SetWarnings (False)
    Dim intTotal As Integer
    Dim sin10 As Single
    Dim sin10tot As Single
    Dim int10 As Integer
    Dim int10tot As Integer
    Dim intCount As Integer
    Dim strSQL As String
 
    intCount = 1
 
    'Count all the records in the table
    intTotal = DCount("*", "tbRecords")
 
    'Determine 10 percent of total records
    sin10 = intTotal / 10 'preserve any part records in decimal number
    int10 = Int(sin10) 'round down to the nearest whole number
 
    sin10tot = sin10 'add to start totals
    int10tot = int10 'add to start totals
 
    'Update the Inventory Month (fldInvMonth) for each set of months 1-10
    Do Until intCount = 11
        strSQL = "UPDATE " _
            & "(SELECT TOP " & int10 & " tbRecords.fldInvMonth " _
            & "FROM tbRecords " _
            & "WHERE ((tbRecords.fldInvMonth) Is Null) " _
            & "ORDER BY tbRecords.fldRandom DESC) AS a " _
            & "SET a.fldInvMonth = " & intCount & ";"
        DoCmd.RunSQL strSQL
 
        intCount = intCount + 1 'set up count for the next month
 
        'Determine how many records will be selected for the next month inventory
        sin10tot = sin10tot + sin10 'increase total count
        int10 = Int(sin10tot - int10tot) 'number to select next time
        int10tot = int10tot + int10 'increase total count
    Loop
 
    'Assign a NEW random number to each of the records
    rstInv.MoveLast
    rstInv.MoveFirst
        Do While Not rstInv.EOF
            rstInv.Edit
            rstInv!fldRandom = Rnd * 1000000000
            rstInv.Update
          rstInv.MoveNext
        Loop
 
    'Reset counts
    int10 = Int(sin10) 'round down to the nearest whole number
    sin10tot = sin10 'add to start totals
    int10tot = int10 'add to start totals
 
    'Update the Inventory Month (fldInvMonth) for each set of months 11-12
    Do Until intCount = 13
        strSQL = "UPDATE " _
            & "(SELECT TOP " & int10 & " tbRecords.fldInvMonth2 " _
            & "FROM tbRecords " _
            & "WHERE ((tbRecords.fldInvMonth2) Is Null) " _
            & "ORDER BY tbRecords.fldRandom DESC) AS a " _
            & "SET a.fldInvMonth2 = " & intCount & ";"
        DoCmd.RunSQL strSQL
 
        intCount = intCount + 1 'set up count for the next month
 
        'Determine how many records will be selected for the next month inventory
        sin10tot = sin10tot + sin10 'increase total count
        int10 = Int(sin10tot - int10tot) 'number to select next time
        int10tot = int10tot + int10 'increase total count
    Loop
 
End If
 
'Cleanup
dbsInv.Close
Set rstInv = Nothing
DoCmd.SetWarnings (True)
 
End Sub
 

[angelagh]

Note: I'm not looking for 10% -- I need to take the total and divide by 10 (see below) to give me 10 equal "groups".

So I built this query (RcdNms)
SELECT Count(*) AS Total_Rcds, [Total_Rcds]/10 AS tenth
FROM Original;


Now how do I feed "tenth" value into the query below query (View10%)? (instead of SELECT DISTINCTROW TOP 10 PERCENT)? Or is there a way to code (RcdNms) into (View10%)?

Here is the SQL:
-----------------
SELECT DISTINCTROW TOP 10 PERCENT Sheet1.[Asset Id], Sheet1.[Custodian Nbr], Sheet1.[Item Desc], Sheet1.Loc, Sheet1.[Mfr Name], Sheet1.[Mfr Part Nbr], Sheet1.[Mfr Yr], Sheet1.[Mfr Model Nbr], Sheet1.[Serial Nbr], Sheet1.[Stock Nbr], Sheet1.[Sub Loc], Sheet1.[Total Cost], Sheet1.UII, *
FROM Sheet1;
-------------------

[Rabbit]

10% and count(*)/10 is the same thing. It sounds like you think they're not the same thing. Why do you think 10% and dividing by 10 is not the same thing?

[angelagh]

because they are not the same thing - 10% only returns 10% of the existing records. 10% is not 10 equal groups.

I need to take a recordset and divide it into 10 equal groups (as I said in earlier posts)...so the complete inventory is done in 10 sessions.

I had originally started w/ 10% and after testing realized after the 10th itiration, I still had 336 records in the original grouping.

[NeoPa]

I suspect we may have a misunderstanding here. X x 10% is actually X / 10.

That is not to say, however, that when you use SELECT TOP 10% you will necessarily get an accurate 10% of the available records. If you read through the Help section on the TOP predicate you'll see the caveats.

[angelagh]

I would appreciate help instead of quibbling about 10% versus 10 groups...IN MY CASE IT IS NOT THE SAME...as I moved 10% of files out...the next 10% was not 1/10th of the original (that I need)...

SO IN MY CASE I NEED 10 EQUAL GROUPS...HENCE MY EXPLANATION IN PREVIOUS POSTS

[Rabbit]

The reason we have to "quibble" about words is because that's how we understand the requirements of the question. When communication is unclear, it's impossible to formulate a solution. We need to understand and agree on the meaning before an accurate solution can be provided.

From what I can gather, you're not looking to return a random 10% or even 1/10th of the records each time the query is run. What you're looking for is to section all the records in the table so you can grab the same size grouping each time you run the query until you have processed all the records.

This is very different from a query that returns 10% or 1/10 of the records.

I suspect gnawoncents' code will get you most of the way to your actual requirements.

[gnawoncents]

Angela,
We understand your concern. In your first iteration, 10% would be fine. The only problem is if you then took 10% of the remaining 90%, you're actually only getting 9% of the total records for the second iteration. In that sense, yes, you need the original number saved somewhere. Or, the code I suggested earlier could get you there all at once and be ready for future use. If, however, you need it saved in a separate query, but automatically calculated the first time, you would need to use VBA to adjust the QueryDef.

Something like this should work:

Dim db10 As Database
Dim qdf10 As QueryDef
Dim int10 As Integer
Dim strSQL As String
 
Set db10 = CurrentDb()
 
'Determine 10% of total records in query (RcdNms)
int10 = DCount("*", "RcdNms")
 
'Update query (View10%) to reflect 10% of total records
Set qdf10 = db10.QueryDefs("View10%")
    strSQL = "SELECT DISTINCTROW TOP " & int10 & " * " & _
             "FROM Sheet1;"
    qdf10.SQL = strSQL
 
Set db10 = Nothing
 

This isn't necessarily how I would go about setting it up, but you know your database and requirements better than I do. It should work based on what information you have provided and the fix you requested.