Setting Record Numbers in a query

  • Thread starter Thread starter Amy Baggott
  • Start date Start date
A

Amy Baggott

I'm not sure if this is possible, but I have a waiting
list that is dynamic (companies are added to it, others
drop off because they get space, refuse space, or ask to
be taken off). What I'd like to do is use a query to sort
the current waiting list records by date and assign each
one the number of where they are in line at that time.
Then I'd like to create a report based on that query that
lists them in alphabetical order, but with the number the
query gave them based on date to make it easy for my boss
to find a company when it calls and tell them where they
are on the waiting list as of that day. Anybody have any
ideas?
 
Amy Baggott said:
I'm not sure if this is possible, but I have a waiting
list that is dynamic (companies are added to it, others
drop off because they get space, refuse space, or ask to
be taken off). What I'd like to do is use a query to sort
the current waiting list records by date and assign each
one the number of where they are in line at that time.
Then I'd like to create a report based on that query that
lists them in alphabetical order, but with the number the
query gave them based on date to make it easy for my boss
to find a company when it calls and tell them where they
are on the waiting list as of that day. Anybody have any
ideas?
Click to expand...

Hi Amy,

I believe you are looking for a "ranking"
subquery, which counts all the records
in a separate instance of the table where
the date is less than the current date in the
record you are looking at in the main table.

assume a simple table "WaitingList"

Company ApplyDate
D 1/1/2004
R 2/1/2004
C 3/1/2004
A 4/1/2004

SELECT
M.Company,
M.ApplyDate,
(SELECT COUNT(*) +1 FROM WaitingList As W
WHERE W.ApplyDate<M.ApplyDate) AS ListOrder
FROM WaitingList AS M
ORDER BY M.Company;

Company ApplyDate ListOrder
A 4/1/2004 4
C 3/1/2004 3
D 1/1/2004 1
R 2/1/2004 2

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top