querying specific rows in UNION query

[dharkangel]

Hi everyone,

I have a UNION query that I would like to select certain rows based on the rownumber, or ID if it were an actual table. For example, lets say I have 20 records in my UNION query and I write another query that only selects records 5-10. How would i do that? I already know how to use TOP, but that only grabs the first set of records, I don't know to grab records in between.

Thanks,

SR

 

[AlexCuse]

If rownumber is selected in the query, could you not just use

select fields from (query) where rownumber between 5 and 10

?

Or do you want something like Bottom fifty percent of the top fifty percent? (or second twenty five percent)

A wise man once said
"The only thing normal about database guys is their tables".

 

[dharkangel]

Thank you for replying. I tried the following query but it did not work. When I ran it, the 'Enter Parameter Value' for 'rownumber' pops up. I must not be using the correct syntax:

SELECT *
FROM getAllRecords
WHERE SiteCityState="CORONA CA" AND rownumber between 5 AND 10
ORDER BY SiteAddress;

 

[AlexCuse]

Ah, this is access. Do you actually have a row number field?

You might have to do this with temp tables.

A wise man once said
"The only thing normal about database guys is their tables".

 

[dharkangel]

No there is no row number field because it is a UNION query that was pulling from external data that also does not have any kind of ID field. What are temp tables? Or could I add rownumbers to a UNION query? Then I could treat the UNION query as if it were a true table.

 

[AlexCuse]

what you would do is set up a 'template' table that has all fields contained in your union query, and also has an autonumber field. (this table must be empty).

Then rather than a single query, set up a vba routine that goes like this:

First, runs a make table query based on 'select * from template'. Have the created table named 'temp' This will create a new 'temporary' table for you.

Second, run an insert from your union query. Something lke 'insert into temp select * from myUnionQuery'

Third, select from temp where RowNumber between 5 and 10

(where RowNumber is the AutoNumber field)

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".