First x entries of a query & copy or "append" those entries into a

[Guest]

One of the databases I'm working with is a mailing list of 200,000 people.
Right now only the people in the office that know access can effectively use
the database. I'm trying to make a user form that
1) Runs our update query as soon as it's loaded
2) Has a textbox so the user can just but in the number of mailings they
need for that time. (i.e. I already have the query to get the table, I just
need to know how to make it show the first x records, with x being the number
that the user inputs)
3) Puts those x records into a separte table so they aren't used again

Thank you for your help

 

[Guest]

1) Runs our update query as soon as it's loaded

** Presuming you have an opening Form - you can just use the "On Open" event
to trigger a Macro that runs the query.

2) Has a textbox so the user can just but in the number of mailings they
need for that time. (i.e. I already have the query to get the table, I just
need to know how to make it show the first x records, with x being the number
that the user inputs)

** design your query to result in the records being numbered i.e. 1,2,3,
etc. Next; in the Criteria of your query you would reference the textbox on
the form like this in the Criteria of column that has the number of the query
record:
<= Forms!FormName.txtBox

With this in the criteria then the query will return only records who have a
number < or = to the value supplied

3) Puts those x records into a separte table so they aren't used again

*** this one isn't easy. At least not without getting into a coded solution
using VB. Just for clarification - do you really want them put into a second
table and deleted from the first? or do you just want them marked as "used"
in the existing table. The 'Make Table' approach using a query is an easy to
use feature in Access. But the delete or mark as 'used' is not. Perhaps
others will give a creative solution to this one.

 

[Graham Mandeno]

Hi Joshua

A "SELECT TOP x" query will return only the first x records satisfying the
WHERE criteria, as ordered by the ORDER BY clause.

If you want the selection to be random, then you can use:
ORDER BY Rnd([SomeField])
where SomeField is a field that contains a positive number. Often an
incrementing AutoNumber primary key is perfect for this.

I suggest you have a table that records when mail was sent to each person,
rather than moving the record from one table to another.

Create a table "Mailings" with:
MailingID (autonumber, primary key)
MailingDate
MaximumSelections
.... and perhaps other fields such as:
User who sent the mail
Document that was sent
.... etc

Create another table "MailSent" with:
MailingID (foreign key to Mailings table)
PersonID (FK to People table)

Now create a form bound to Mailings, with a textbox for MaximumSelections
(and the other fields as required).

Add a command button, cmdSelectRecipients with some code like this:

Private Sub cmdSelectRecipients_Click()
Dim db as DAO.database
Dim sSQL as String
' first save current record
sSQL = "Insert into MailSent (MailingID, PersonID) " _
& "Select top " & MaximumSelections & " " & MailingID _
& ", PersonID from [yourquery];"
Set db = CurrentDb
db.Execute sSQL, dbFailOnError
End Sub

This will create up to x records in the MailSent table with the MailingID of
the current record on the form.

Your query will need to include a WHERE clause to exclude PersonID values
that already exist in the MailSent table. For example:

WHERE PersonID not in (Select PersonID from MailSent)

Depending on your business rules, you might like to refine this to exclude
only those who have received a mailing in the last month, or those who have
already been mailed the current document.

Once you have created these records in the MailSent table, you can use a
query of People joined to MailSent to select the people in that particular
mailing.

 

[Guest]

Thank You

** Presuming you have an opening Form - you can just use the "On Open" event
to trigger a Macro that runs the query.


** design your query to result in the records being numbered i.e. 1,2,3,
etc. Next; in the Criteria of your query you would reference the textbox on
the form like this in the Criteria of column that has the number of the query
record:
<= Forms!FormName.txtBox

With this in the criteria then the query will return only records who have a
number < or = to the value supplied


*** this one isn't easy. At least not without getting into a coded solution
using VB. Just for clarification - do you really want them put into a second
table and deleted from the first? or do you just want them marked as "used"
in the existing table. The 'Make Table' approach using a query is an easy to
use feature in Access. But the delete or mark as 'used' is not. Perhaps
others will give a creative solution to this one.

 

[Guest]

Thank you

Hi Joshua

A "SELECT TOP x" query will return only the first x records satisfying the
WHERE criteria, as ordered by the ORDER BY clause.

If you want the selection to be random, then you can use:
ORDER BY Rnd([SomeField])
where SomeField is a field that contains a positive number. Often an
incrementing AutoNumber primary key is perfect for this.

I suggest you have a table that records when mail was sent to each person,
rather than moving the record from one table to another.

Create a table "Mailings" with:
MailingID (autonumber, primary key)
MailingDate
MaximumSelections
.... and perhaps other fields such as:
User who sent the mail
Document that was sent
.... etc

Create another table "MailSent" with:
MailingID (foreign key to Mailings table)
PersonID (FK to People table)

Now create a form bound to Mailings, with a textbox for MaximumSelections
(and the other fields as required).

Add a command button, cmdSelectRecipients with some code like this:

Private Sub cmdSelectRecipients_Click()
Dim db as DAO.database
Dim sSQL as String
' first save current record
sSQL = "Insert into MailSent (MailingID, PersonID) " _
& "Select top " & MaximumSelections & " " & MailingID _
& ", PersonID from [yourquery];"
Set db = CurrentDb
db.Execute sSQL, dbFailOnError
End Sub

This will create up to x records in the MailSent table with the MailingID of
the current record on the form.

Your query will need to include a WHERE clause to exclude PersonID values
that already exist in the MailSent table. For example:

WHERE PersonID not in (Select PersonID from MailSent)

Depending on your business rules, you might like to refine this to exclude
only those who have received a mailing in the last month, or those who have
already been mailed the current document.

Once you have created these records in the MailSent table, you can use a
query of People joined to MailSent to select the people in that particular
mailing.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

One of the databases I'm working with is a mailing list of 200,000 people.
Right now only the people in the office that know access can effectively
use
the database. I'm trying to make a user form that
1) Runs our update query as soon as it's loaded
2) Has a textbox so the user can just but in the number of mailings they
need for that time. (i.e. I already have the query to get the table, I
just
need to know how to make it show the first x records, with x being the
number
that the user inputs)
3) Puts those x records into a separte table so they aren't used again

Thank you for your help

 

[Guest]

On #3- If its easier to just have them somehow marked as used instead of them
being put in a separte table, that would do just as well.

Thank you for your help