By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Break a query into 3 recordsets based on record number

P: n/a
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?
Nov 13 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
sl*******@yahoo.com wrote:
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?
What is a record number? There isn't one in Access.

Have you looked at AbsolutePostition in help? It won't help you in a
query but you can have a "record number"

If you are using something like an Autonumber or a field that will be
unique to the record in the query you can do something like
Select *, Dcount("*","TableName","AutoNumFldNm < " &
[AutoNumFldNm]) + 1 As TempNum From TableName
This will create a column called TempNum
Nov 13 '08 #2

P: n/a
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sl*******@yahoo.comwrote in message
news:94**********************************@r36g2000 prf.googlegroups.com...
>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?
Nov 13 '08 #3

P: n/a
rkc
On Nov 12, 7:41*pm, slickd...@yahoo.com wrote:
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?
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.
Nov 13 '08 #4

P: n/a
rkc
On Nov 13, 5:35*am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<slickd...@yahoo.comwrote in message

news:94**********************************@r36g2000 prf.googlegroups.com...
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?
Nov 13 '08 #5

P: n/a
On Nov 13, 7:18*am, rkc <r...@rkcny.comwrote:
On Nov 12, 7:41*pm, slickd...@yahoo.com wrote:
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?

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.
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.
Nov 19 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.