Random Function for Selecting Records

Hi everyone,

This should be simple.  I'm trying to pull one random record from each
group within a table.  The groups are "Units" - geographic areas, and
I want to select one random point (conveniently enough, "Point") from
each Unit that satisfies certain criteria.  Step 1 - query with a
random number field - Rnd([EventID]), sorted by the random number
field.  EventID is an autonumber PK field.  Then a query that performs
the grouping (by Unit) and pulls the first value of interest (in this
case Point) from the first record for each group.  The problem is the
first query doesn't actually perform the sort correctly.  Here are the
first few values in the random number field, which is set to sort in
ascending order:

RandomNumber
0.212475836277008
0.456852912902832
0.35159033536911
0.721272110939026
0.638044655323029

Clearly that's not ascending order - it doesn't appear to be any order
at all.  Seems like the logic of using this setup to select a random
record is violated if the records don't actually get sorted
correctly.  Every time I run the query I end up with a different
record on top, so it seems to be sorting "randomly" somehow but not by
the random number field.  Any idea what's up?  Is it recalculating the
random numbers after sorting the records or something?

PS - I know there are additional problems with trusting a last or
first function to do anything meaningful - that's the next hurdle but
at the moment I'd like to get the first step worked out.  If anyone
has a good suggestion for returning the top 1 record within a group
(without using the first or last functions) that would help too.
0
esn
5/29/2010 12:48:19 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
1352 Views

Similar Articles

[PageSpeed] 42

Also, I just noticed Access redraws the random number every time I
click in one of the records of the query results, and seems to
struggle with recalculating (often tries to display the original and
new values at the same time in the same cell).  Is this query just
recalculating and that's why the records never really appear in any
sort of order?
0
esn
5/29/2010 12:56:48 AM
esn wrote:
>Also, I just noticed Access redraws the random number every time I
>click in one of the records of the query results, and seems to
>struggle with recalculating (often tries to display the original and
>new values at the same time in the same cell).  Is this query just
>recalculating and that's why the records never really appear in any
>sort of order?

Read this:
http://www.mvps.org/access/queries/qry0011.htm

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

0
PieterLinden
5/29/2010 1:07:53 AM
esn wrote:
>This should be simple.  I'm trying to pull one random record from each
>group within a table.  The groups are "Units" - geographic areas, and
>I want to select one random point (conveniently enough, "Point") from
>each Unit that satisfies certain criteria.  Step 1 - query with a
>random number field - Rnd([EventID]), sorted by the random number
>field.  EventID is an autonumber PK field.  Then a query that performs
>the grouping (by Unit) and pulls the first value of interest (in this
>case Point) from the first record for each group.  The problem is the
>first query doesn't actually perform the sort correctly.  Here are the
>first few values in the random number field, which is set to sort in
>ascending order:
>
>RandomNumber
>0.212475836277008
>0.456852912902832
>0.35159033536911
>0.721272110939026
>0.638044655323029
>
>Clearly that's not ascending order - it doesn't appear to be any order
>at all.  Seems like the logic of using this setup to select a random
>record is violated if the records don't actually get sorted
>correctly.  Every time I run the query I end up with a different
>record on top, so it seems to be sorting "randomly" somehow but not by
>the random number field.  Any idea what's up?  Is it recalculating the
>random numbers after sorting the records or something?
>
>PS - I know there are additional problems with trusting a last or
>first function to do anything meaningful - that's the next hurdle but
>at the moment I'd like to get the first step worked out.  If anyone
>has a good suggestion for returning the top 1 record within a group
>(without using the first or last functions) that would help too.


The problem is that you are looking at the numbers  ;-)

Displaying a query's datasheet is done on an as needed basis
so the window is filled with what looks like decent data.
But, the rest of the data is not calculated and retrieved
until you scroll down to see more records.  Then, when you
scroll back up to see the earlier records, they too are
calculated and retrieved ... again, but with different
random numbers.

If you use code to access the records or display them in a
report, it should be fine regardless if they are calculated
only once or more than once.  OTOH, since you want a random
record, why do you care how the record was chosen?

-- 
Marsh
MVP [MS Access]
0
Marshall
5/29/2010 2:44:37 AM
Thanks for the replies - I care how the record was chosen only in that
I need it to be random (or reasonably close to random).  When I
checked to make sure that Access was functioning properly to select a
random record, there seemed to be a glitch, so I thought I would run
it by the experts.  I figured this was just a recalculating issue and
that, at some point, the order of the records had been randomized, but
I wanted to be sure before I went too much further.  And it's good to
know it's possible to build a function to stop Access from
recalculating the random field - given the crummy performance of
queries based on this one I might end up using that to speed things
up.

Now I have a question about the next step - here's the SQL I'm using
right now:

SELECT [GLSA Caps with Unit].Unit, [GLSA Caps with Unit].Point
FROM [GLSA Caps with Unit]
WHERE ((([GLSA Caps with Unit].Point) In
   (SELECT TOP 3 [GLSA Caps with Unit_1].Point
   FROM [GLSA Caps with Unit] AS [GLSA Caps with Unit_1]
   WHERE ((([GLSA Caps with Unit_1].Unit)=[GLSA Caps with Unit].Unit))
   ORDER BY Rnd([RndSeed]))))
ORDER BY [GLSA Caps with Unit].Unit;

And here's the output:

Unit	Point
1	OO007
1	RR007
2	II006
2	LL001
2	LL005
2	MM001
2	MM002
3	II009
3	LL011
3	OO008
4	BB002
4	BB005
4	CC003
5	BB013
5	CC008
5	FF011
5	GG010
5	HH009
5	HH011
6	FF013
7	S002
7	U002
7	V003

Note the variable number of records per unit.  FYI - Point is a text
field that identifies a geographic location (as I stated above) within
a grid based on a row identifier (a single or double letter) and a
column identifier (3 digits from 000 to 110).  The source query (GLSA
Caps with Unit):

SELECT [Grid Point Info].Unit, [Trapping Data Records Table].Point,
Min([Trapping Data Records Table].[Capture/Event ID]) AS RndSeed
FROM [Grid Point Info] INNER JOIN [Trapping Data Records Table] ON
[Grid Point Info].LetterNumb = [Trapping Data Records Table].Point
WHERE ((([Trapping Data Records Table].[Species/Event])="GLSA"))
GROUP BY [Grid Point Info].Unit, [Trapping Data Records Table].Point;

To anticipate the first question - I already checked to make sure that
"GLSA Caps with Unit" returns at least three points per unit, and it
does.  I've also tried using the "randomizer" custom function from the
link above, but I still get similar results.  If I run the subquery on
it's own using "Unit=1" as criteria I get the right results (3 random
points in unit 1).  So why does the query return less than three
points for units 1 and 6, and how can a subquery with a TOP 3 clause
be returning more than 3 points for some of the units?
0
esn
5/29/2010 11:55:04 PM
esn wrote:

>Thanks for the replies - I care how the record was chosen only in that
>I need it to be random (or reasonably close to random).  When I
>checked to make sure that Access was functioning properly to select a
>random record, there seemed to be a glitch, so I thought I would run
>it by the experts.  I figured this was just a recalculating issue and
>that, at some point, the order of the records had been randomized, but
>I wanted to be sure before I went too much further.  And it's good to
>know it's possible to build a function to stop Access from
>recalculating the random field - given the crummy performance of
>queries based on this one I might end up using that to speed things
>up.
>
>Now I have a question about the next step - here's the SQL I'm using
>right now:
>
>SELECT [GLSA Caps with Unit].Unit, [GLSA Caps with Unit].Point
>FROM [GLSA Caps with Unit]
>WHERE ((([GLSA Caps with Unit].Point) In
>   (SELECT TOP 3 [GLSA Caps with Unit_1].Point
>   FROM [GLSA Caps with Unit] AS [GLSA Caps with Unit_1]
>   WHERE ((([GLSA Caps with Unit_1].Unit)=[GLSA Caps with Unit].Unit))
>   ORDER BY Rnd([RndSeed]))))
>ORDER BY [GLSA Caps with Unit].Unit;
>
>And here's the output:
>
>Unit	Point
>1	OO007
>1	RR007
>2	II006
>2	LL001
>2	LL005
>2	MM001
>2	MM002
>3	II009
>3	LL011
>3	OO008
>4	BB002
>4	BB005
>4	CC003
>5	BB013
>5	CC008
>5	FF011
>5	GG010
>5	HH009
>5	HH011
>6	FF013
>7	S002
>7	U002
>7	V003
>
>Note the variable number of records per unit.  FYI - Point is a text
>field that identifies a geographic location (as I stated above) within
>a grid based on a row identifier (a single or double letter) and a
>column identifier (3 digits from 000 to 110).  The source query (GLSA
>Caps with Unit):
>
>SELECT [Grid Point Info].Unit, [Trapping Data Records Table].Point,
>Min([Trapping Data Records Table].[Capture/Event ID]) AS RndSeed
>FROM [Grid Point Info] INNER JOIN [Trapping Data Records Table] ON
>[Grid Point Info].LetterNumb = [Trapping Data Records Table].Point
>WHERE ((([Trapping Data Records Table].[Species/Event])="GLSA"))
>GROUP BY [Grid Point Info].Unit, [Trapping Data Records Table].Point;
>
>To anticipate the first question - I already checked to make sure that
>"GLSA Caps with Unit" returns at least three points per unit, and it
>does.  I've also tried using the "randomizer" custom function from the
>link above, but I still get similar results.  If I run the subquery on
>it's own using "Unit=1" as criteria I get the right results (3 random
>points in unit 1).  So why does the query return less than three
>points for units 1 and 6, and how can a subquery with a TOP 3 clause
>be returning more than 3 points for some of the units?


Sorry, but I am having a seriously tough time unraveling
where the randon numbers are being recalculated.  This is
especially compounded by the query optimizer doing whatever
it wants to combine your three queries into one with who
knows what effect on the random numbers.

I have not been able to explain the various number of
records, even when including the fact that TOP 3 will return
more than 3 records when there is a tie for the third value
in the sorted list.

-- 
Marsh
MVP [MS Access]
0
Marshall
6/1/2010 5:34:46 PM
Thanks for checking it out, I appreciate it.  I guess I have to go
through the units one by one, which certainly isn't the end of the
world.
0
esn
6/1/2010 7:19:18 PM
Reply:

Similar Artilces:

Using the kit function
We would like to create some kits using our inventory on hand. How would we create them and have the individual items depleted from stock and go to these kits. We need to be able to know when to order more items to create the kits when they are getting low. Go to Database -> Items -> New Item -> Standard then change item type to Kit, then click on Kit tab and add the items you need, it will be depleted from inventory. Click on attributes and set the re-order point and re-stock level for the items, create a purchase order weekly or daily and select re-order point method to cr...

ranking the records
i have a table like this name dept sales rank ram purchase 230 1 bill purchase 120 2 mol purchase 40 3 steve purchase 24 4 ram logistics 89 1 ram logistics 23 2 bill logistics 12 3 mol logistics 4 4 steve logistics 2 5 I already have the first three columns in one of my tables. I want to create a query which can give me the fourth column the rank column. Rankings change once the dept changes as you could see above. How to create a query to create a new column like rank plea...

Sheet Select Macro
Hi, I have this problem, firstly i have created a macro to create a pivot table of data when ever the macro is activated. The macro automaticaly creates a new sheet e.g. sheet10 to represent this data. On that new sheet the macro has created i wanted to create anothe rmacro that makes the sheet look tidy and nice. This involves copying data from a different sheet into it. The problem is i made this macro but there is an error. Say i delete the sheet that is "Sheet10" and create a new one, the macro that i have created does not work, this is because it cannot find sheet 10. How c...

Selecting Citigroup 401(k) Plan
Does anybody know what this means: "The service that allows you to download transactions from MSN Money My Accounts has been disabled. Close and reopen Money. If the problem persists then try turning the service off and then re-enabling it. If the problem still persists you will need to re-enable the service with a new Windows Live ID." BRAND-SPANKING-NEW copy of Money+, stupid windoze id that's been working fine for years, over a dozen on-line accounts set-up ok. then this little gem. of course, i have already tried closing & re-opening. but the message doesn't e...

selecting XML from SQL2000 and using it in ASP.NET
I am running this in SQL Server 2000: SELECT lastname, firstname, title, hiredate FROM employees FOR XML AUTO I get back these results in the Query Analyzer: <employees lastname="Jones" firstname="James" title="Developer" hiredate="2004-05-10T00:00:00"/><employees lastname="Jordan" firstname="Ken" title="Sr. Developer" hiredate="2004-05-03T00:00:00"/> But in a webform, when I call the ExecuteXMLReader and do a Response.Write white looping through the reader, I only get back the first node. Any reason...

Parameter of dll's function
Hi all, I write a MFC application, in this application I load a dll and call its function by use GetProcAddress function. The function of dll that I need invoke has an output parameter which is a pointer of CString class. In dll's function I asign the value of local variable to the content CString pointer parameter. After performing this asign operation my application is halted. What is wrong in my asign operator. Please help me. Thanks. What is the type of your local variable ? Does exe and dll use the same version of MFC ? Generally speaking, following code should work: void...

When I Select More Than One Tab
Is there a way for Excel to tell me when I have more than one tab selected? Other than me remembering to look at the top and see [Group] in the file name. Any ideas are welcome! (B^>)-]=[ The grouped sheets' tabs will be white. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 15:20:51 -0400, "WSR" <wsr-203@hotmail.com> wrote: >Is there a way for Excel to tell me when I have more than one tab selected? > >Other than me remembering to look at the top and see [Group] in the file >name. > >Any ideas are welcome! > >(B...

using date function, month shows as January when i type (12)
when date of birth is A1 and i use the formula =date(year(A1)+19,month(12),day(31)), to get the last day of the year before the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this? if i take the +19 away, the same thing happens. The only time it works is if i specify the year instead of using another cell. Does this formula do what you need? ="12/31/"&YEAR(A1)+19 The reason your original formula did not work is because the MONTH() and DAY() functions require serial date numbers (that is to say, an Excel date equivalent expressed as a number). =DATE(YEA...

Algebra function graphs with Excel?
My daughter is getting to a place in her Algebra where she needs to begin graphing functions. Of course, she wants an expensive graphing calculator! I'm wondering if an add-in to Excel will allow graphing of Algebra functions. Ed Ed, Microsoft Student 2006 includes a graphical calculator. About $60 street price. http://www.tushar-mehta.com/excel/software/utilities/iga.html is a free add-in, though I believe it requires a bit of Excel expertise to use. (I haven't used it.) HTH, Bernie MS Excel MVP "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:eYaC0...

Prompt to Ask If User Wants to Create a Duplicate Record
For example, let's say there's already a record for Mary Shelly, and I have a new Mary Shelly that I'm trying to add to my database. Can I make it so that Access will prompt me and say "There is already a record matching this description. Do you wish to continue?" Hi Dove, I'm assuming first and last name are in the same field. Create a button or Label and add this code to the On Click event but make the following changes to the code: NAMEFIELD= The name of the field where the name is kept on the form TABLEWITHUSERNAMES = The name of the table where the username i...

Sum of cell which use if array function
Hi, I have many cells using if function which result is number. I'd like to sum all of those cells, unfortunatelly i cant do that, the result is none. How can i solve this one? Thank you If your IF formulas contain quotes around numbers, REMOVE THEM. Wrong: =IF(A1="x","10","0") Right: =IF(A1="x",10,0) -- Biff Microsoft Excel MVP "Pran" <Pran@discussions.microsoft.com> wrote in message news:1F9DB21C-4DF6-45A5-AA22-D3F8CDA45F31@microsoft.com... > Hi, > > I have many cells using if funct...

Create individual worksheets for selected rows in a table
Hi guys, I hope somebody may be able to help. I want to be able to automate the following procedure Create individual worksheets for selected rows in a table. Many thanks - Batman2002 Batman, If you really want the entirerow, then use this - if you just want the selection, then remove the .EntireRow of the last line: Sub CopySelectioToNewWorksheet() Dim mySheet1 As Worksheet Dim mySheet2 As Worksheet Set mySheet1 = ActiveSheet Set mySheet2 = Sheets.Add(Type:="Worksheet") mySheet1.Activate Selection.EntireRow.Copy mySheet2.Range("A1") End Sub HTH, Bernie "...

Selecting A single Month From a Table with Multiple Months
The following code is used as the criteria in a selct query for a date field: >=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),0) It has worked fine until the table included records from January 2010, database was created in 2009. Is there a fix or a better way to accomplish the intent? Not sure what your problem is the expressions you have return the first and last day of the prior month. So this is February 3, 2010 and the expression return 1/1/2010 and 1/31/2010. What do you want returned? What is the problem you are ha...

Where can I find all of the SA functionality
I'm doing a project for SOX compliance and I need to document all of the functionality that SA has in 7.5 that can't be assigned to a regular user. Where could I locate that information? SA by default has complete access to the systems, expect where you explicitly revoke access under individual user security. SA is hard coded in the application to create new users, and assign user access to companies. The best bet is to contact MBS support for this documentation if they have any >-----Original Message----- >I'm doing a project for SOX compliance and I need to >...

How do I insert a variable into a SELECT statement?
To filter a recordsource, I want something like: SELECT ...WHERE [Name] =' & strName & '; I would assign the above to the recordsource property of a form. My intent here is to save a step over opening the form on a table and then setting a filter. Can this be done, or must I use a filter? I read somewhere that filtering after opening a large recordsource was inefficient. Use the Open event provedure of the form. Create the string. Assign to the form's RecordSource property. This kind of thing: Private Sub Form_Open(Cancel As Integer) Dim strSql as String ...

Extract list using functions
Hi, can anyone help with the following: I want to extract items in a list and show them in another worksheet. An example of the master list would be: Name Dollars Quantity Months Customer1 $ Q 12 Customer2 $ Q 12 Customer3 $ Q 6 I want to be able to extract all those customers which have less than 12months data (as indicated in the month column). I want to be able to do this using functions in another worksheet and list those seperately there. Is there a way to do this? Elijah Hi y...

Import Export Wizard and Archive function missing under FILE
I have to reinstall my office XP and tried to import my previous settings. However I do not have the option under the FILE menu to Import/Export or Archive. Anyone know how to get this function back? cheers, John. johnkeogh@vexinc.com ...

Report not functionning in Money 2007
Hi ! I just finished the upgrade from Money 2004 to Money 2007. Everything went fine except that the Report function no longer work. Every time i try to get a report, the "Loading" stays there forever and nothing happen. Does someone has a clue of what is happening ? Andr´┐Ż Hi Andre, I am using Money 2006 was also experiencing the problem you described where I would attempt to generate a report, would get the pie chart and then the "Loading" message and nothing would happen. I converted over from Quicken in 2005. I was only experiencing this problem when I tr...

Auto-select combo box entry when only one row
I have 2 combo boxes the second is dependent on the first. When the first is clicked, I requery the second in the OnClick event. When the requeried combo box has only one row, I'd like this to be auto selected. How can I do this? Thanks. "mscertified" <rupert@tigerlily.com> wrote in message news:3AACB546-54A0-4BC2-8AFA-03AEB37F001E@microsoft.com... >I have 2 combo boxes the second is dependent on the first. When the first >is > clicked, I requery the second in the OnClick event. When the requeried > combo > box has only one row, I'd like this to be...

Multiple Calendars but Shared One is Selected
When a shared Calendar or multiple shared calendars are displayed, when swithcing between "Mail" and "Calendar" the other calendar or one of the others is selected when pressing "Calendar" and viewing those calendars. On every other users' machine I have tested the users' calendar is always selected no matter how many shared calendars are in the calendar view. Running Office 2007 on Windows XP - all fully updated with exchange server 2003. I already refreshed roaming and local profile and re-installed office products. Both steps had no ef...

HASHBYTES function
1. select HASHBYTES('MD5', 'himansu114@hotmail.com') -- generates this hash: -- 0x0633944C04E55C674701FB084120975C -- I need to generate a 32 byte value. Like this: -- 0633944C04E55C674701FB084120975C Does anyone know to accomplish this? -------------------------------------------------- 2. I have a table with an EmailAddress column that needs to have the HASHBYTES function applied to. Anyone who how I can pass the column of a table into this function? -- Thanks in advance. Himansu Himansu wrote: > > 1. > > sel...

Algebraic Functions
How would you solve a function with input 1 output 8. Then input 2 output 14. next input 3 output is blank then input is blank Output is 24. the input is blank and the out put is 28. Finally, the input is 6 and the output is blank. ...

Limited IF Nested Level functions.
I am day to day user of Excel and need to develop spreadsheets models for management accounting reports. However the application has a limitation on nested level of up to 7 but my financial period has 12 months and it makes it difficult to produce the right template. For example when I want the formula to pick the 8th month totals, the nested IF function does not work. In the old Lotus 123 spreadsheet, one was able to do this. Is there other way this can be corrected? There are lots of alternatives such as a lookup function (VLOOKUP, HLOOKUP), the SUMPRODUCT function etc. Please give m...

Problems with select statement that is a combination of select and a variable
Hi, I would like to create a search form that is pretty flexible so I created a from with oll the fields possible on it so you can fill them in or not. then I put a button on it that will get the query in a listbox. this is the code behind the search button: Private Sub cmdSearch_Click() Dim ctl As Control Dim qry As String Dim teller As Integer Dim lengte As Integer Dim i As Integer Dim field As String Dim fieldtxtlen As String Dim fieldtxt As String Dim Condition As String Dim sSelect As String 'Clear the listBox lstQuery.RowSource = &q...

Problem with AND function
I'm using the function as shown below =IF(AND((COUNTBLANK(O14:AE14)<17),L14>0),N14*L14, "enter project") essential =IF(AND(TRUE, TRUE), do this, else "enter project" however, I'm getting "enter project" even if only one of the options is TRUE. I thought the whole point of AND was that BOTH had to be TRUE? Any ideas??? Excel 2003 Think it over: if both condition is TRUE then AND is also TRUE, but if only one of the conditions is TRUE then AND becomes FALSE and your formula returns "enter project" which is right. I t...