Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-19
5
Medium Priority
?
327 Views
Last Modified: 2012-06-27
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;
0
Comment
Question by:coffee_bean
  • 2
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
leos_ earned 120 total points
ID: 12630466
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
 

Author Comment

by:coffee_bean
ID: 12630867
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
 
LVL 3

Expert Comment

by:leos_
ID: 12631050
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
 
LVL 6

Expert Comment

by:Eladla
ID: 12642779
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
 

Author Comment

by:coffee_bean
ID: 12651048
Hi Eladla, i m using Java and the database is Access.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question