Break a query into 3 recordsets based on record number

  • Thread starter Thread starter slickdock
  • Start date Start date
S

slickdock

I need to break my query into 3 groups:

First 60 records (records 1-60)
Next 60 records (records 61-121)
Next 60 records (records 122-182)

Of course I could use top values 60 for the first query, but where do
I go from there?
 
Is this a multi-user situation? If there is any kind of pause between when
you deliver the first 60 records and the next, you might need to consider
whether users could be adding new records or deleting records during this
time. If there is, the concept of the 'next 60' might be uncertain.

One approach would be to create a temporary table where you hold the ID
value of the records you have served up. Say two fields:
- ID Number
- BatchID Number

For the 'First 60' request, you execute 2 action queries:
- a DELETE to clear the temp table out;
- an Append, to insert the TOP 60 as batch 1.
You then INNER JOIN your original table to this, and serve up the results.

For the 'Next 60', use the Unmatched Query Wizards (first dialog when you
create a query) to get a query for the records in your original table that
are not in the temp. table. Modify it to TOP 60, and change it to an Append
query to it writes the results to the temp table. The batch number will be
the max in the temp table + 1, and you serve up just that batch.

Sometimes Access struggles with writing from a non-updatable query (for the
temp table.) If that happens, use a subquery (rather than a frustrated outer
join) to identify the TOP 60 to write to the temp. table:
http://allenbrowne.com/subquery-01.html
 
Is this a multi-user situation? If there is any kind of pause between when
you deliver the first 60 records and the next, you might need to consider
whether users could be adding new records or deleting records during this
time. If there is, the concept of the 'next 60' might be uncertain.

One approach would be to create a temporary table where you hold the ID
value of the records you have served up. Say two fields:
- ID Number
- BatchID Number

For the 'First 60' request, you execute 2 action queries:
- a DELETE to clear the temp table out;
- an Append, to insert the TOP 60 as batch 1.
You then INNER JOIN your original table to this, and serve up the results.

For the 'Next 60', use the Unmatched Query Wizards (first dialog when you
create a query) to get a query for the records in your original table that
are not in the temp. table. Modify it to TOP 60, and change it to an Append
query to it writes the results to the temp table. The batch number will be
the max in the temp table + 1, and you serve up just that batch.

Sometimes Access struggles with writing from a non-updatable query (for the
temp table.) If that happens, use a subquery (rather than a frustrated outer
join) to identify the TOP 60 to write to the temp. table:
http://allenbrowne.com/subquery-01.html
 
I need to break my query into 3 groups:

First 60 records (records 1-60)
Next 60 records (records 61-121)
Next 60 records (records 122-182)

Of course I could use top values 60 for the first query, but where do
I go from there?
Click to expand...

Why do you need to break your query into three groups?
What do you want to do with them once you have?

If you explain the purpose there may be a solution you haven't
thought of yet.
 
Is this a multi-user situation? If there is any kind of pause between when
you deliver the first 60 records and the next, you might need to consider
whether users could be adding new records or deleting records during this
time. If there is, the concept of the 'next 60' might be uncertain.

One approach would be to create a temporary table where you hold the ID
value of the records you have served up. Say two fields:
- ID            Number
- BatchID    Number

For the 'First 60' request, you execute 2 action queries:
- a DELETE to clear the temp table out;
- an Append, to insert the TOP 60 as batch 1.
You then INNER JOIN your original table to this, and serve up the results..

For the 'Next 60', use the Unmatched Query Wizards (first dialog when you
create a query) to get a query for the records in your original table that
are not in the temp. table. Modify it to TOP 60, and change it to an Append
query to it writes the results to the temp table. The batch number will be
the max in the temp table + 1, and you serve up just that batch.

Sometimes Access struggles with writing from a non-updatable query (for the
temp table.) If that happens, use a subquery (rather than a frustrated outer
join) to identify the TOP 60 to write to the temp. table:
   http://allenbrowne.com/subquery-01.html
Click to expand...
 
Why do you need to break your query into three groups?
What do you want to do with them once you have?

If you explain the purpose there may be a solution you haven't
thought of yet.
Click to expand...

We are required to send the query results to another company. The
company's requirement is that we send them the query results in excel
format, with no more than 60 records per excel file. So we currently
export the msaccess query results to excel, and manually break up the
files to make sure there are no more than 60 per file. Have no idea
why the company wants no more than 60 records per excel file. Ours is
not to question why.
 
Back
Top