CODEWITHSUNDEEP
databasesortingms-accesspagination
joe
joe

Reputation: 556

paging and ordering a MS Access query

i have the following MS ACCESS query that i would like it to return results ordered by name and "paged" by "faking" a rownumber

 select * from (SELECT *
 FROM (SELECT  
        s.name as SHolderCategory,
        c1.id,
        c1.fmember,
        c1.link,
        m.name as category,
        c1.name,
        c1.address1,
        c1.address2,
        c1.city,
        c1.state,
        c1.zip, 
        (SELECT COUNT(c2.id) FROM orgs AS c2 WHERE c2.id <= c1.id) AS rownumber                
 FROM 
((orgs AS c1 inner join membershipcls m on m.Id = c1.mClassID)  
 inner join SHolderscategories s on s.Id = c1.SHolderCategoryID
 ) 
 where c1.active = 1) 
  order by c1.name)
WHERE  rownumber > 20 AND rownumber <=40

the problem here is that the ordering is done before the where clause which enforces paging. so it ends up sorting one page at a time, rather than sorting the whole resultset then paging it...so the results are wrong because in page 1 i have names starting with a to g ... then in page 2 it comes back to names starting with c .... and so on

when i try to get the order clause out so that the query executes the paging first...Mr ACCESS is Angry!!! and tells me it is a COMPLEX query !!!!

any workaround for this?

Upvotes: 0

Views: 1600

Answers (1)

nelucon
nelucon

Reputation: 326

try also this approach:

SELECT * FROM
(
    SELECT TOP 20 *
    FROM 
    (
        SELECT TOP 40
            s.name as SHolderCategory,
            c1.id,
            c1.fmember,
            c1.link,
            m.name as category,
            c1.name,
            c1.address1,
            c1.address2,
            c1.city,
            c1.state,
            c1.zip
        FROM 
            orgs AS c1 
            inner join membershipcls m on m.Id = c1.mClassID
            inner join SHolderscategories s on s.Id = c1.SHolderCategoryID
        WHERE c1.active = 1
        ORDER BY c1.name
    ) o 
    ORDER BY o.name DESC
) f ORDER BY f.name

Upvotes: 2

Related Questions