How to display a range of record (1-10++) from Access Database

Eg From (1-10),(11-20),(21-30)......(101-110++)
on click of 1-10 of 120

I assume, first I had to determine the total no of records in the table (based on Resource ID), then set the Maximum and Minimum number of records to display (which I am going to display on JTable using Java).

But I am not sure how I can do so. Lost....

This is my sql statement to retrieve the whole list of workstation.

SELECT workstation.[Resource ID], pc.[Pc Description], pc.[Software Description], peripheral.[Peripheral Description]
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1));

This is the statement I using to retrieve the total number of resource records

SELECT  count(*) AS [Total Resource]
FROM workstation;
coffee_beanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leos_Commented:
This is rather inefficient, but it should work.

To get your first 10 you can use the top command:

SELECT top 10 workstation.[Resource ID], pc.[Pc Description], pc.[Software Description], peripheral.[Peripheral Description]
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1));

you'll only get 10 results. The subsequent sets of 10 are harder.  

We use a make a primary key for the joined tables made out of the primary keys of each seperate tables. I'm assuming that each of your tables has a primary key and it's called pkid.
ie: workstation.pkid & '-' & pc.pkid & '-' & peripheral.pkid

For 11-20

SELECT top 10 workstation.[Resource ID], pc.[Pc Description], pc.[Software Description], peripheral.[Peripheral Description]
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1)) and

workstation.pkid & '-' & pc.pkid & '-' & peripheral.pkid not in
(
SELECT top 10 workstation.pkid & '-' & pc.pkid & '-' & peripheral.pkid not in
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1))
)

For 21-30

SELECT top 10 workstation.[Resource ID], pc.[Pc Description], pc.[Software Description], peripheral.[Peripheral Description]
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1)) and

workstation.pkid & '-' & pc.pkid & '-' & peripheral.pkid not in
(
SELECT top 20 workstation.pkid & '-' & pc.pkid & '-' & peripheral.pkid not in
FROM (workstation LEFT JOIN pc ON workstation.[Resource ID] = pc.[Resource ID]) LEFT JOIN peripheral ON pc.[Pc ID] = peripheral.[Pc ID]
WHERE (((workstation.Active)=1) AND ((pc.Active)=1))
)

and so on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coffee_beanAuthor Commented:
thanks leos.  I will try out the codes and see how it works but what is this '-' for that I see in the suggested solution.

//*******************
By the way, I forgotten to mentioned that I had this ResultSetTableModel.java java(JDBCAdapter) that extends AbstractTableModel file. I used this file to get the records from database and display to a JTable. Even though I could get the number of rows using this file. It didn't help.
0
leos_Commented:
the '-' is just a dash that seperates the primary keys.  Without it you may run into some errors.

workstation = 10, pc = 13, peripheral=21
workstation = 101, pc = 32, peripheral=1

are two possible combiations

so their unique id are 10-13-21 and 101-32-1

however without the dashs they would be the same: 101321

good luck, i hope it works for you.
0
EladlaCommented:
I would just select the entire table, since you will need more than 10 in most cases (I get the idea is to browes the list)
And then just use a filter with in your programing language.
This is very easy with ADO in VB or VC++.
I don`t know what your using.....but I`m sure that it`s possible.
0
coffee_beanAuthor Commented:
Hi Eladla, i m using Java and the database is Access.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.