Selecting X random entries of a subset of data in excel from access

[crystalball]

Hello,

I'm attempting to pull a random sample of 5 rows from a chunk of data I'll be pulling in a query. While I'm able to get the data into excel, and it does select 5 entries to display, my Order By entry fails to randomize in excel (it works perfectly when run as a query in access). Do any of you excel gurus know why ordering by random(Autonumbered field) would fail here (the query does run, it just doesn't work properly. Same 5 every time). Do you know of another way to command it to randomize if this method can't be fixed?

SELECT top 5 DB.Test
FROM `C:\Users\...\DB.accdb`.DB DB
WHERE (DB.Position1=?) AND (DB.Position2=?)
ORDER BY RND(DB.ID);

I know I could probably do this with some MacGuyvering in excel (select all entries with those criteria, assign random numbers in excel, use those numbers to figure out which 5 to pick) but I'd prefer to get it done in query form if possible so it can be reused in other areas.

Any help appreciated.

 

[crystalball]

Hello,

I'm attempting to pull a random sample of 5 rows from a chunk of data I'll be pulling in a query. While I'm able to get the data into excel, and it does select 5 entries to display, my Order By entry fails to randomize in excel (it works perfectly when run as a query in access). Do any of you excel gurus know why ordering by random(Autonumbered field) would fail here (the query does run, it just doesn't work properly. Same 5 every time). Do you know of another way to command it to randomize if this method can't be fixed?



I know I could probably do this with some MacGuyvering in excel (select all entries with those criteria, assign random numbers in excel, use those numbers to figure out which 5 to pick) but I'd prefer to get it done in query form if possible so it can be reused in other areas.

Any help appreciated.

Followup: It seems to be mixing up the data, just doing it the same way every time. A sample pull showing the IDs:

<TABLE style="WIDTH: 465pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=620><COLGROUP><COL style="WIDTH: 465pt; mso-width-source: userset; mso-width-alt: 22674" width=620><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right>8</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: white 0.5pt solid; WIDTH: 465pt; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=20 width=620 align=right></TD></TR></TBODY></TABLE>

 

[crystalball]

Is there anyone who can help with pulling random results like this? I'd really appreciate it.