Counting records

[Guest]

I am preparing to send out letters. I am running a query to give me a list
of members to send them too. I can only send out 750 per week. If I have a
result of 3,200 members, I need to capture the first 750 and the second 750
and the thrid 750 and the remainder.

I have tried many different ways. I thought about 'mod' but if there are
less than 750, this will give me small lists. I thought about trying to
utilize Autonum, but unsuccessfully. Do you have any suggestions?

Thank you for your time.

 

[Guest]

One way is to use the TOP function and flag field to indicate those
previously selected.

Add a field for Sent. Create three queries - first select the TOP 750, the
second flags those using an update query (can be a Yes/No or a text field for
an "X"), and the third updates to remove the flag.

The first has a criteria for Sent field NOT flagged.

 

[Guest]

Karl,

Thank you for your response. I understand how you are getting the first 750
on the list (top) but how are you getting the next 750 on the list? i.e.
751-1500 and then 1501-2250 on the list. Are you perhaps deleting the top
750 and then start over again with the top 750?

Sorry, confused.

 

[Guest]

The second query flag the first 750 as already being pulled and then the
first query is used again with criteria not to pull those flagged as having
been pull already.

 

[Guest]

Karl,

OK. As I sit here thinking and thinking and yes .. some more thinking... but
now it makes sense.

Thank you soooo much.

 

[Guest]

Karl,

Sorry, but another glich. I run the first query for the 'top' 750, and I
get the top 750. I then update those to an 'x'. I run the second time for
the 'top' 750 not equal to an 'x', but now I receive nothing. Because the
top 750 have an x.

Help.

 

[Guest]

Karl,

I did some more testing. I am using a Unique ID field (customer num) within
the query and the 'top' is based off a field with an amount. Well, several
of the amounts are identical. So it gives me the top 750 amount AND all that
are equal to it. Which in my second run amounted to 1900 instead of 750.
Any thoughts?

 

[Guest]

Ok. Use a select query that pulls those not flagged. Create an other query
that uses the first as data source (not the table) and pull the TOP 750.

 

[tshillam]

You can create a sub-query that indexes your records, then build three
sub-queries that returns your result sets (using the first sub-query).

1. Create a query that returns some table data.
2. Now create a sub-query that returns results from the first query
(use example below) but provides a 'Ranking' column and values:


Ranking: (Select Count (FIELDNAME) FROM [QUERYNAME] as Temp WHERE
[Temp].[FIELDNAME] <= [QUERYNAME].[FIELDNAME])

3. When you run the sub-query, the results should display a 'Ranking'
field in ascending order: 1, 2, 3, etc. I try to use a field with
unique values--its been a while, but its some neat code.

 

[Guest]

Karl,

That was the sequence I was trying to understand. That will work. Thank you.

 

[Guest]

Tshillam,

Thank you for your response. I tried the script, I was very interested to
see how it would work. However, it seems to process for a very long time.
When the query finally comes up - the numbers not Sequential and they did not
start with the number 1. There was about 400 records results in the query
and the ranking numbers were in the 1,000s. Any thoughts?

 

[tshillam]

Tshillam,

Thank you for your response. I tried the script, I was very interested to
see how it would work. However, it seems to process for a very long time.
When the query finally comes up - the numbers not Sequential and they did not
start with the number 1. There was about 400 records results in the query
and the ranking numbers were in the 1,000s. Any thoughts?

Gina,

When you work the query examples, just copy and past SQL statements in
SQL Design mode.

Example:


1. Create table (populate with values):

RecordID Text
1 abc
2 def
3 ghi
4 jkl



2. Create query (Save as 'qryRecords' for name):

SELECT tblRecords.Text, tblRecords.RecordID
FROM tblRecords
ORDER BY tblRecords.Text DESC;

3. Create sub-query (Save as 'qryRecords_SUB' for name):

SELECT (Select Count (Text) FROM [qryRecords] as Temp WHERE
[Temp].[Text] <= [qryRecords].[Text]) AS Ranking, qryRecords.RecordID,
qryRecords.Text
FROM qryRecords;


Experiment with changing orders. You can also create further
sub-queries.

Good luck

Todd