423,688 Members | 2,138 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

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

P: 6
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
Feb 9 '18 #1
Share this Question
Share on Google+
16 Replies


gnawoncents
100+
P: 212
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnXfer_Click()
  2.  
  3. Dim strSQL As String
  4. Dim intTotal As Integer
  5. Dim int10 As Integer
  6. Dim intCount As Integer
  7.  
  8. 'Count all the records in the table
  9. intTotal = DCount("*", "tbRecords")
  10. intCount = intTotal
  11.  
  12. 'Determine 10 percent of total records
  13. int10 = intTotal / 10
  14.  
  15. DoCmd.SetWarnings (False)
  16.  
  17. Do While intCount > 0
  18.  
  19.     'Set up and run SQL for transfer query
  20.     strSQL = "INSERT INTO tbRecords10 " _
  21.     & "SELECT TOP " & int10 & " tbRecords.* " _
  22.     & "FROM tbRecords;"
  23.  
  24.     DoCmd.RunSQL strSQL
  25.  
  26.     'INSERT ANY ADDITIONAL ACTIONS HERE THAT YOU WANT TO HAPPEN
  27.     '
  28.     '
  29.  
  30.     'Set up and run SQL for delete query
  31.     strSQL = "DELETE FROM (SELECT TOP " & int10 & " * FROM tbRecords);"
  32.  
  33.     DoCmd.RunSQL strSQL
  34.  
  35.     'Subtract the records transferred from the count remaining
  36.     intCount = intCount - int10
  37.  
  38.     'INSERT ANY ADDITIONAL ACTIONS HERE THAT YOU WANT TO HAPPEN
  39.     '
  40.     '
  41.  
  42. Loop
  43.  
  44. DoCmd.SetWarnings (True)
  45.  
  46. End Sub
  47.  
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.
Feb 12 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Feb 12 '18 #3

Rabbit
Expert Mod 10K+
P: 12,272
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
Feb 12 '18 #4

gnawoncents
100+
P: 212
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.
Feb 13 '18 #5

P: 6
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!!
Feb 14 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Feb 14 '18 #7

P: 6
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...
Feb 14 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,667
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.
Feb 15 '18 #9

gnawoncents
100+
P: 212
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).

Expand|Select|Wrap|Line Numbers
  1. Function DoesFieldExist(sField As String, sTable As String) As Boolean
  2.  
  3. Err.Clear
  4. DoesFieldExist = False
  5. On Error GoTo setfalse:
  6. If (DCount(sField, sTable) = 0) And Err Then DoesFieldExist = False Else DoesFieldExist = True
  7.  
  8. setfalse:
  9.  
  10. End Function
  11.  
  12. Private Sub btnInventory_Click()
  13.  
  14. Dim dbsInv As Database
  15. Dim rstInv As Recordset
  16. Set dbsInv = CurrentDb
  17.  
  18. 'Test to see if needed fields have already been created and, if not, create them
  19. If DoesFieldExist("[fldRandom]", "tbRecords") = False Then
  20.  
  21.     'Add the following fields and types to the tbRecords table: fldRandom (number), fldInventoried (Y/N), fldInvMonth (Integer), fldInvCurrent (Integer)
  22.     dbsInv.Execute "ALTER TABLE tbRecords ADD COLUMN " _
  23.         & "fldRandom LONG, " _
  24.         & "fldInvMonth NUMBER, " _
  25.         & "fldInvMonth2 NUMBER;"
  26.  
  27.     'Assign a random number to each of the records
  28.     Set rstInv = dbsInv.OpenRecordset("tbRecords")
  29.     rstInv.MoveLast
  30.     rstInv.MoveFirst
  31.         Do While Not rstInv.EOF
  32.             rstInv.Edit
  33.             rstInv!fldRandom = Rnd * 1000000000
  34.             rstInv.Update
  35.           rstInv.MoveNext
  36.         Loop
  37.  
  38.     '------------------------------------------------------------------------
  39.     'Assign an inventory month (1-10) to ten equal record sets (+/- 1 record)
  40.     '------------------------------------------------------------------------
  41.     DoCmd.SetWarnings (False)
  42.     Dim intTotal As Integer
  43.     Dim sin10 As Single
  44.     Dim sin10tot As Single
  45.     Dim int10 As Integer
  46.     Dim int10tot As Integer
  47.     Dim intCount As Integer
  48.     Dim strSQL As String
  49.  
  50.     intCount = 1
  51.  
  52.     'Count all the records in the table
  53.     intTotal = DCount("*", "tbRecords")
  54.  
  55.     'Determine 10 percent of total records
  56.     sin10 = intTotal / 10 'preserve any part records in decimal number
  57.     int10 = Int(sin10) 'round down to the nearest whole number
  58.  
  59.     sin10tot = sin10 'add to start totals
  60.     int10tot = int10 'add to start totals
  61.  
  62.     'Update the Inventory Month (fldInvMonth) for each set of months 1-10
  63.     Do Until intCount = 11
  64.         strSQL = "UPDATE " _
  65.             & "(SELECT TOP " & int10 & " tbRecords.fldInvMonth " _
  66.             & "FROM tbRecords " _
  67.             & "WHERE ((tbRecords.fldInvMonth) Is Null) " _
  68.             & "ORDER BY tbRecords.fldRandom DESC) AS a " _
  69.             & "SET a.fldInvMonth = " & intCount & ";"
  70.         DoCmd.RunSQL strSQL
  71.  
  72.         intCount = intCount + 1 'set up count for the next month
  73.  
  74.         'Determine how many records will be selected for the next month inventory
  75.         sin10tot = sin10tot + sin10 'increase total count
  76.         int10 = Int(sin10tot - int10tot) 'number to select next time
  77.         int10tot = int10tot + int10 'increase total count
  78.     Loop
  79.  
  80.     'Assign a NEW random number to each of the records
  81.     rstInv.MoveLast
  82.     rstInv.MoveFirst
  83.         Do While Not rstInv.EOF
  84.             rstInv.Edit
  85.             rstInv!fldRandom = Rnd * 1000000000
  86.             rstInv.Update
  87.           rstInv.MoveNext
  88.         Loop
  89.  
  90.     'Reset counts
  91.     int10 = Int(sin10) 'round down to the nearest whole number
  92.     sin10tot = sin10 'add to start totals
  93.     int10tot = int10 'add to start totals
  94.  
  95.     'Update the Inventory Month (fldInvMonth) for each set of months 11-12
  96.     Do Until intCount = 13
  97.         strSQL = "UPDATE " _
  98.             & "(SELECT TOP " & int10 & " tbRecords.fldInvMonth2 " _
  99.             & "FROM tbRecords " _
  100.             & "WHERE ((tbRecords.fldInvMonth2) Is Null) " _
  101.             & "ORDER BY tbRecords.fldRandom DESC) AS a " _
  102.             & "SET a.fldInvMonth2 = " & intCount & ";"
  103.         DoCmd.RunSQL strSQL
  104.  
  105.         intCount = intCount + 1 'set up count for the next month
  106.  
  107.         'Determine how many records will be selected for the next month inventory
  108.         sin10tot = sin10tot + sin10 'increase total count
  109.         int10 = Int(sin10tot - int10tot) 'number to select next time
  110.         int10tot = int10tot + int10 'increase total count
  111.     Loop
  112.  
  113. End If
  114.  
  115. 'Cleanup
  116. dbsInv.Close
  117. Set rstInv = Nothing
  118. DoCmd.SetWarnings (True)
  119.  
  120. End Sub
  121.  
Feb 15 '18 #10

P: 6
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;
-------------------
Feb 22 '18 #11

Rabbit
Expert Mod 10K+
P: 12,272
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?
Feb 22 '18 #12

P: 6
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.
Feb 22 '18 #13

NeoPa
Expert Mod 15k+
P: 31,084
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.
Feb 22 '18 #14

P: 6
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
Feb 22 '18 #15

Rabbit
Expert Mod 10K+
P: 12,272
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.
Feb 23 '18 #16

gnawoncents
100+
P: 212
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:
Expand|Select|Wrap|Line Numbers
  1. Dim db10 As Database
  2. Dim qdf10 As QueryDef
  3. Dim int10 As Integer
  4. Dim strSQL As String
  5.  
  6. Set db10 = CurrentDb()
  7.  
  8. 'Determine 10% of total records in query (RcdNms)
  9. int10 = DCount("*", "RcdNms")
  10.  
  11. 'Update query (View10%) to reflect 10% of total records
  12. Set qdf10 = db10.QueryDefs("View10%")
  13.     strSQL = "SELECT DISTINCTROW TOP " & int10 & " * " & _
  14.              "FROM Sheet1;"
  15.     qdf10.SQL = strSQL
  16.  
  17. Set db10 = Nothing
  18.  
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.
Feb 23 '18 #17

Post your reply

Sign in to post your reply or Sign up for a free account.