Selecting random records with MS Access

Factual Questions
1

Hi folks,

I have a ten thousand record table with (among others) Location, Sub-Location, Item and autonumbered fields. I need to select varying numbers of property items for about fifty of the Location / Sub-Locations. For example, if Tucson is the Location and Nogales is the Sub-Location, I need to randomly select 25 out of 100 of their items and create a report from that. Each Location / Sub-Location will have a different amount of property items, and therefore a differing amount of items that will need to be selected. The number of items to be selected is arbitrarily picked by the client, so we have to input it by hand.

Here is what I have thought of so far. I could divide the total number of items at a Loc/SubLoc by the number of items needed to be selected. This would yield, for lack of a better word, a selection index. I could then compare the line number divided by the selection index and compare it with the integer of that same calculation. Therefore, if the line number is evenly divisible by the selection index, it gets selected for the table. In the end, I should (in theory) come up with the table I need. So for Tucson/Nogales, the selection index would be four, and I would get records numbered 4, 8, 12, etc. for a total of 25 records. This works great, but I run short when I need to select sixty out of the hundred records.

The other way I could do it would be to randomly select a record, count all records selected so far and if the number is larger than the number I need, I stop, if less than the number I go back and select another. Sounds great in theory, but I have no idea what to do. I could write a nice program on my Commodore 64 to do that, but I don’t know where to begin with MS Access. Is there a feature that will let me select a random number of records? It does seem that I can choose a random number, and tie it to the line number. But if the Sub-Loc starts at line number 2,843 and ends at 2,967, how well would that work?

Could anyone please point me in the right (random) direction?

Thank you very, very much.
Rhythmdvl

PS In case you are wondering how I got myself into this mess, reread the story about the tailor and the giant. The one about the tailor who kills seven flies in one swoop…

2

I won’t be able to give you any details until I get home from work but writing a correlated subquery will do the trick. This will require some fairly strong knowledge of SQL however. If you are looking to just one location/sublocation at a time you can just set the Top Values flag within the query to whatever you want so that you just get the number of records that you specify for each combination (Right click in the gray space above the grid in the query window to find it). If this is just a one-shot deal, you could simply do this for all the different combinations. Perhaps you turn this into an append query that appends to another table that you could then base the report on. Please let me know if this is a permanent or reoccuring problem because it will greatly affect the preffered option.

3

Thanks Mavpace. It is an annual thing, this being only the third year our company or the client has done it. A lot is being made up as we gain experience. Last year we merely printed inventory sheets and checked off every third item. But that got pretty hairy sending out fifty to sixty page faxes across the country. Much better to do it all within Access I said. Great, they said, have fun. Great, I said, me and my big mouth.

So, not easy. It certainly seems like it should have been easy. At least I can console myself with the fact that I am not missing something obvious. All I want is for Access to randomly pick a given number of items from a list.
What about this – can I get Access to add a field to my table, a field that contains random numbers? Would I have to do it with a make-table query that duplicates the table as it is now, and add the field? This would essentially replace the table as it is, no? If Access can sort by these random numbers, then I could create another make-table query that used the [enter] function in the Loc and Sub-Loc criteria fields, and (I hope) I could use the [enter] function again in a field that would select the top X of those records. Voila – sounds random to me.

To make the general inventory sheets I followed much the same method above. I had the master table and a master make-table query. I clicked the ‘make table’ icon on the toolbar, typed in name, clicked ‘run query’ on the task bar, and entered the Loc and the Sub-Loc. I did that fifty times (took maybe fifteen minutes or so) and there I was. I made one master report, changed its data under properties, saved as, changed its data… and so on. (Note to self: never claim elegance in solution finding).

Thanks for your help so far… let me know if I am wasting time trying to find a way to stick random numbers into a table.

4

Rhythmdvl, I am having a little trouble understanding how you have organized your table(s). I think you may be complicating the issue unnecessarily. I’ll try to help you with an example from one of mine pending further clarification.

I have a phone number table (called Numbers) with the following 6 fields: Fullname, Address, City, State, Zip, PhoneNumber. This query will select 25 random records from the table:

SELECT top 25 * FROM Numbers ORDER BY Rnd(Len(Numbers.FULLNAME))

Vary the number after “top” if you wish to have a different number of records returned. If you need to change this number at the time of execution, you could create the query from VB and execute it.

Does this help any?

5

Hardcore,

I think the only thing that makes this problem difficult is that you must pull out a certain random number of records for each location/sublocation, not just a number of random records for the whole set. However, I do like the way you have to randomize records in the set. I think that if we combine the too approaches, randomize per above procedure and then simply run an append query for each location/sublocation combination, that will be a workable solution. A more elegant solution is definetly possible but probably not worth the trouble for a once a year problem.

6

The Select statement is working beautifully so far. To quickly dispel any remaining confusion about the task: A major Federal Service decided about four years ago that it wanted to get a better handle on property on a national level. One of the controls they put in place was nationwide inventory audits - independent folks like us would visit selected sites with the site’s full inventory sheets in hand. We would look for a sample of the items on our list (the list-to-floor portion of the audit) and look at a random sampling of items on the floor and compare them with the list (the floor-to-list portion).

To make things a bit easier on the Service and us, for the past two years we have been selecting the items for the list-to-floor inventory ahead of time. Last year, we took the complete inventory and highlighted items manually. Very, very time consuming. What I am trying to do here is spit out a list of ‘items to be audited’. The main table (provided to us by the client - no control over its format) has fields for Region (i.e. western, HQ, OCONUS, etc.) Location (Phoenix, Dallas, New York) and Sub-Location (District office, service center, etc.) and various property description fields. The Location and Sub-Location combination is enough to uniquely identify a particular site we are going to visit.

Hardcore’s method seems to be working great. I tried it out with a mock database with a LOC SUB and Name fields. After creating the query in SQL view, I changed to Design View and added the LOC and SUB fields to the criteria list. I put [enter LOC] and [enter SUB] in as above, and it worked great - out of thirty records, it choose a random number of items that matched the location I needed. That is, in my list with fifty items, fifteen matched a certain LOC / SUB criteria, and it (randomly) choose five of those items.

The only thing stopping this from becoming a remarkably easy process (though again, no claim to elegance) is that to change the number of items selected, I have to go into SQL view. With the LOC and SUB criteria, I was able to use the [enter] function to speed things up. Is there a way to get Access to prompt for the top X number each time I run the query?

Thanks so far… if I can get over this last hump, I think the only thing I will need to know is where to send the tequila.

Rhythmdvl

7

Are you sure you want it to prompt you for the values every time? I would create multiple copies of the query, give each query a different name, make the necessary changes to each query, then base the different reports off the various queries. Unless you have like a gazillion different locations that each need a report. If you do, or you just really want to input the values while it is running, let me know and I will show you a way to dynamically create the queries at runtime based on VB code.

8

The reason I’d like to get it a bit automated is that I have to generate lists for fifty-eight sites, and there are three property categories (property over five thousand, firearms and vehicles) that are stored in separate tables. The number of items will vary from site to site, because the number is based on an external statistical program that the client runs. You can see why I was hoping that this would be easy. It is, thanks to your quick line of SQL, mind you. If worse comes to worse, I can leave the query open in SQL view, change the top X number, alt-q, K = name the new table, alt-q R runs the query. Done. This is just one and a half steps more than it took to get the general inventory lists.

9

If this board wasn’t so slow right now I would pay more attention to your issue. As it is, I can only afford to check in occasionally.

Anyway, is there a particular reason you prefer to make new tables instead of queries? Generally, it is a bad idea to make tables except for entry of data. Queries are used to view the data in various arrangements.

Is your ultimate goal printouts or new “views” of the data in the database to be shared online with other users? The “views” could be new tables, queries or reports.

I’ve got a routine that will create either of the above. It would be fairly simple to modify it to step through a table containing each site and grab the value for the number of records to return.

10

You may have already finished with your project, but just in case you haven’t, I’ll post something that may help. I will be on the road all day tomorrow moving, so I might not be back online for a few days.

This code will create a separate report for each site and save it in the database. You could modify it to just use one report and keep changing the Recordsource property if you prefer. Or change it to create queries (or tables) instead of reports. Whatever you need.

Assuming you have:
[ul][li]a template report titled “myPlayReport” that is designed the way you want the reports to appear[/li][li]a table (named “tabSite”) with fields ID (autonumber), Site (string), and NumItems (integer)[/ul][/li]You should have a table similar to this (without NumItems) if you have designed it as a typical relational database. If not, you can create one with a MakeTable query, taking care to select only unique values.

I made the following quick modification to some of my existing code:


    Dim dbs As Database, strSQL As String
    Dim myRs As Recordset, myRptStr As String

    Set dbs = CurrentDb
    Set myRs = dbs.OpenRecordset("tabSite", dbOpenTable, dbReadOnly)

    myRs.MoveFirst

    Do Until myRs.EOF

        myRptStr = "Site" & myRs!ID & "Report"

        DoCmd.CopyObject , myRptStr, acReport, "myPlayReport"

        strSQL = "SELECT top " & myRs!NumItems & " * from Numbers "
        strSQL = strSQL & "ORDER BY Rnd(Len(Numbers.FULLNAME));"

        DoCmd.OpenReport myRptStr, acViewDesign
        Reports(myRptStr).RecordSource = strSQL
        DoCmd.Close acReport, myRptStr, acSaveYes

        myRs.MoveNext

    Loop

If you want the reports to printout during this routine, add


DoCmd.OpenReport myRptStr, acViewNormal

before you leave the Loop.

To use this code, create a form with a command button and copy the code into the click event. You may want to add error checking, and perhaps some code to delete any existing reports with the same names as the new ones.

If this wasn’t useful now, maybe you can use it next year!