Selecting Random Records

[faxylady]

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[KARL DEWEY]

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.

 

[faxylady]

I don't quite understand. Please explain further. I don't want to send the
same mailing to the same people all the time. How do I prevent duplicates
being sent to the same records?

To make sure of this, I thought of using the query that John Vinson sent me
and creating a make table, deleting those records from the original table,
then when I'm ready, append the make table back in to the original table. In
other words, take the records I want out, then put them back in when I'm
ready. Thanks.

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[John Spencer]

How about
--adding a field to your table - a date field if you have only one type of
mailing
--modifying the random query to screen out records where the date field is
not null
--using the random query you have now to update the date field with the
selection date (and possibly time)
--Now use the date field to select the most current set of records

You would have to reset the field to null once you had filled the field on
all the records.

If you have multiple different mailings, I would add a Mailing table and a
MailingType table. The Mailing table would contain the following fields:
CustomerID
MailingType
MailingDate

Then I would populate this table with the random query. And join this table
to the customer table when I wanted to generate the mailing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I don't quite understand. Please explain further. I don't want to send
the
same mailing to the same people all the time. How do I prevent duplicates
being sent to the same records?

To make sure of this, I thought of using the query that John Vinson sent
me
and creating a make table, deleting those records from the original table,
then when I'm ready, append the make table back in to the original table.
In
other words, take the records I want out, then put them back in when I'm
ready. Thanks.

Why not add a Yes/No field named Selected? Then update it. Then next
time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into
a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were
distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the
same
query to delete those same records by means of a delete query.
However, when
I tried to enter the Top Value in the delete query, Access would not
let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

Thanks for your reply. I don't quite understand your response. My thoughts
were to do a Make Table query utilizing the random query above. I also set
criteria as to which states I wanted to mail to. Once the Make Table was
created, I thought I could use the same query and delete them from the main
database. Is there a way I can do this?

How about
--adding a field to your table - a date field if you have only one type of
mailing
--modifying the random query to screen out records where the date field is
not null
--using the random query you have now to update the date field with the
selection date (and possibly time)
--Now use the date field to select the most current set of records

You would have to reset the field to null once you had filled the field on
all the records.

If you have multiple different mailings, I would add a Mailing table and a
MailingType table. The Mailing table would contain the following fields:
CustomerID
MailingType
MailingDate

Then I would populate this table with the random query. And join this table
to the customer table when I wanted to generate the mailing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I don't quite understand. Please explain further. I don't want to send
the
same mailing to the same people all the time. How do I prevent duplicates
being sent to the same records?

To make sure of this, I thought of using the query that John Vinson sent
me
and creating a make table, deleting those records from the original table,
then when I'm ready, append the make table back in to the original table.
In
other words, take the records I want out, then put them back in when I'm
ready. Thanks.

Why not add a Yes/No field named Selected? Then update it. Then next
time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into
a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were
distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the
same
query to delete those same records by means of a delete query.
However, when
I tried to enter the Top Value in the delete query, Access would not
let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[John Spencer]

Well you can do that, but why? If you delete the people from the table,
they will be gone. If you should ever want to send another mailing to the
group or do anything else with the group of people you couldn't.

Yes, you can use the make table to delete records from the source table, as
long as you have a reliable method of joining the two tables. That would
normally be a primary key.

If you need help setting up the delete query, post back. I see from your
posting to Karl Dewey that you are considering another (and in my opinion,
superior to the delete scheme) method of handling this.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KARL DEWEY]

You have to add the field in your table. Better yet would be a DateTime
field for Selected.
The easy way then is to use an update query to update the random selected
quanity of records. Then use those records with the Selected date for your
mailing.

Next time use an update query to update the random selected quanity of
records where the Selected field is null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

I agree, this is probably the best method. You said, "The easy way then is
to use an update query to update the random selected

quanity of records."

The "random selected quantity of records" is created by means of a query.
Do I now update this query with a new calculated field named Selected? Or do
I simply add a new field to the table named Selected and give it a date/time
data type? I'm confused so please give me specific directions as to what and
how to do this. Thanks for your great help.

You have to add the field in your table. Better yet would be a DateTime
field for Selected.
The easy way then is to use an update query to update the random selected
quanity of records. Then use those records with the Selected date for your
mailing.

Next time use an update query to update the random selected quanity of
records where the Selected field is null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

What I did was add the new field Selected to my table. I then did the query
Sort by Shuffle as a Select query using a Top Value of 2000 records. Up to
this point, things work fine. However, when I try to update the Selected
field of the Sort by shuffle query, access would not let me put anything in
the Top Values so I could control the number of records returned in the
result set. It updated the Selected field for the entire database (all the
records).

Can I do the Sort by Shuffle select query and the Update the Selected field
query at the same time? Or please suggest how to do this.

Thanks.

You have to add the field in your table. Better yet would be a DateTime
field for Selected.
The easy way then is to use an update query to update the random selected
quanity of records. Then use those records with the Selected date for your
mailing.

Next time use an update query to update the random selected quanity of
records where the Selected field is null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

--using the random query you have now to update the date field with the
selection date (and possibly time)


Please notice some of my later questions. Also, tell me how to do this
suggestion.

Thanks.

How about
--adding a field to your table - a date field if you have only one type of
mailing
--modifying the random query to screen out records where the date field is
not null
--using the random query you have now to update the date field with the
selection date (and possibly time)
--Now use the date field to select the most current set of records

You would have to reset the field to null once you had filled the field on
all the records.

If you have multiple different mailings, I would add a Mailing table and a
MailingType table. The Mailing table would contain the following fields:
CustomerID
MailingType
MailingDate

Then I would populate this table with the random query. And join this table
to the customer table when I wanted to generate the mailing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I don't quite understand. Please explain further. I don't want to send
the
same mailing to the same people all the time. How do I prevent duplicates
being sent to the same records?

To make sure of this, I thought of using the query that John Vinson sent
me
and creating a make table, deleting those records from the original table,
then when I'm ready, append the make table back in to the original table.
In
other words, take the records I want out, then put them back in when I'm
ready. Thanks.

Why not add a Yes/No field named Selected? Then update it. Then next
time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into
a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were
distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the
same
query to delete those same records by means of a delete query.
However, when
I tried to enter the Top Value in the delete query, Access would not
let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[faxylady]

Please answer the following two posts. They were posted last week and I have
been waiting for an answer. My experience or lack thereof has not provided
me a solution. How do you update the random selection query so that the
selected field reflects the current day's date?

I cannot do a delete query so that those records could be deleted and added
back in later because Access will not allow Top Values in the Delete Query.
I could not delete the same records that had been selected.

Your help is appreciated. Thank you.

You have to add the field in your table. Better yet would be a DateTime
field for Selected.
The easy way then is to use an update query to update the random selected
quanity of records. Then use those records with the Selected date for your
mailing.

Next time use an update query to update the random selected quanity of
records where the Selected field is null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.

 

[John Spencer]

ASSUMING that you have a numeric primary key, you can select a group of
records using the following.


UPDATE YourTable
SET LastMailingDate = Date()
WHERE PrimaryKeyField IN (
SELECT Top 1000 PrimaryKeyField
FROM YourTable as T
WHERE T.LastMailingDate Is Null
ORDER BY rndNum(PrimaryKeyField))

That will select 1000 records from your table that have no value in the
date field. And it will put in the current date into that field. (You
can use NOW() instead of Date() if you want the date and time, because
you are doing multiple mailings.

Then for the actual mailing all you do is select the records with the
date (or date and time) you have just created.

SELECT *
FROM YourTable
WHERE LastMailingDate = [Which date?]


Or even
SELECT *
FROM YourTable
WHERE LastMailingDate =
(Select Max(LastMailingDate)
FROM YourTable)

If you get less than 1000 records back, then you have probably used all
the records in the table at one time or another. Once that happens you
can clear all the dates with an update query that sets the date field to
null


UPDATE YourTable
SET LastMailingDate = Null

Or you can choose to just clear the oldest records by using some
criteria to identify which ones to clear.

If you don't understand how to use the SQL window to build the queries,
then post back and I'll try to lead you through using the grid.




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Please answer the following two posts. They were posted last week and I have
been waiting for an answer. My experience or lack thereof has not provided
me a solution. How do you update the random selection query so that the
selected field reflects the current day's date?

I cannot do a delete query so that those records could be deleted and added
back in later because Access will not allow Top Values in the Delete Query.
I could not delete the same records that had been selected.

Your help is appreciated. Thank you.

You have to add the field in your table. Better yet would be a DateTime
field for Selected.
The easy way then is to use an update query to update the random selected
quanity of records. Then use those records with the Selected date for your
mailing.

Next time use an update query to update the random selected quanity of
records where the Selected field is null.
--
KARL DEWEY
Build a little - Test a little

Thanks for your response. After thinking about it, this might be plausible.
Please explain further. When, where and at what point do I add the field
"Selected?" Would this be in the original query when the random records are
first selected? How would I go about updating it?

:

Why not add a Yes/No field named Selected? Then update it. Then next time
arond include criteria for the field as not selected.
--
KARL DEWEY
Build a little - Test a little


:

This Question is directed toware John Vinson.

A while back, I asked for a solution to find random records for mailing
throughout a 100,000+ database. You told me to put the following into a
module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function


and in a calculate field put:
Shuffle: RndNum([fieldname])

This did work in that the results did give me records that were distributed
throughout the table.

However, to do another set of records that are different from the first
group, what do I need to do?

I have tried to do a make table query of the 1st group, then use the same
query to delete those same records by means of a delete query. However, when
I tried to enter the Top Value in the delete query, Access would not let me.
There was no Top Value in the Properties.

What do you suggest so that I can generate different records each time?

Thanks. Your responses are always good.