Matching on Sequence Numbers

[Bunky]

Here is the scenario. Mgmt wants me to determine if an agent has perfect
attendance for the past week. If they do, their name is entered in for a
drawing for $$. This keeps going for 1 quarter so the agents can have 12
entries or none. I have got it set up that I append the last weeks data to a
quarterly table after the processing for the week has occurred. Now, let's
say it is now time to pull the winner. I have a random number routine that
firsts does a count of the entries in the quarterly table and then returns a
totally random number. I want to then match that random number with the
quarterly table and pick out the winner based on the number returned. What I
think I need is a way to have my own sequence number starting at 1 and going
for the entire quarterly table after the table is created. I tried
DMax("[MySeqNum]","PerfectWk")+1 in the default area but it did not update.
Ideas???

 

[John Spencer (MVP)]

Why not just use a Top 1 query based on a random number.

SELECT TOP 1 *
FROM Quarterly
ORDER BY Rnd(Len([AnyField]))


More details follow:

See http://support.microsoft.com/default.aspx?id=208855

Or take a look at the following from John Vinson.
Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bunky]

John,

You are a genius! But Now I have one more question since I like both
solutions. Is one better than the other? Why would I want the one in the
module over yours?

Still learning!
Kent

Why not just use a Top 1 query based on a random number.

SELECT TOP 1 *
FROM Quarterly
ORDER BY Rnd(Len([AnyField]))


More details follow:

See http://support.microsoft.com/default.aspx?id=208855

Or take a look at the following from John Vinson.
Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Here is the scenario. Mgmt wants me to determine if an agent has perfect
attendance for the past week. If they do, their name is entered in for a
drawing for $$. This keeps going for 1 quarter so the agents can have 12
entries or none. I have got it set up that I append the last weeks data to a
quarterly table after the processing for the week has occurred. Now, let's
say it is now time to pull the winner. I have a random number routine that
firsts does a count of the entries in the quarterly table and then returns a
totally random number. I want to then match that random number with the
quarterly table and pick out the winner based on the number returned. What I
think I need is a way to have my own sequence number starting at 1 and going
for the entire quarterly table after the table is created. I tried
DMax("[MySeqNum]","PerfectWk")+1 in the default area but it did not update.
Ideas???

 

[John Spencer (MVP)]

The module ensures that you are getting a different starting random number
every time you run the query. That's what the line RANDOMIZE does.

The other method can generate the same random sequence. So going with the
function is probably better to ensure that you are getting a different random
selection each time you initally execute the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

You are a genius! But Now I have one more question since I like both
solutions. Is one better than the other? Why would I want the one in the
module over yours?

Still learning!
Kent

Why not just use a Top 1 query based on a random number.

SELECT TOP 1 *
FROM Quarterly
ORDER BY Rnd(Len([AnyField]))


More details follow:

See http://support.microsoft.com/default.aspx?id=208855

Or take a look at the following from John Vinson.
Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Here is the scenario. Mgmt wants me to determine if an agent has perfect
attendance for the past week. If they do, their name is entered in for a
drawing for $$. This keeps going for 1 quarter so the agents can have 12
entries or none. I have got it set up that I append the last weeks data to a
quarterly table after the processing for the week has occurred. Now, let's
say it is now time to pull the winner. I have a random number routine that
firsts does a count of the entries in the quarterly table and then returns a
totally random number. I want to then match that random number with the
quarterly table and pick out the winner based on the number returned. What I
think I need is a way to have my own sequence number starting at 1 and going
for the entire quarterly table after the table is created. I tried
DMax("[MySeqNum]","PerfectWk")+1 in the default area but it did not update.
Ideas???