Multiple "rankings" or "numberings" in queries

R

ronchito

Hello, I have a question that I'm wondering if someone can help me
out with. I have a database full of patient information. Let's say
Patient A was admitted on 1/1/2003. Then, on 1/7/2003 that patient
had their 1st re-evaluation. Then, on 1/13/2003, they had their 2nd
re-evaluation, and a 3rd re-eval on 1/22/2003. Then let's say that
Patient B was admitted on 2/20/2003, had their first re-eval on
2/24/2003 and that's it. The re-evaluations are all stored in a
single table. Each patient is tagged by a unique ID# -- so one ID#
can have many rows associated with it. What I'd like to do is create
a query that results in the following:

PatientID EvalDate Eval#
A 1/7/03 1
A 1/13/03 2
A 1/22/03 3
B 2/24/03 1

As you can see, what I'm trying to do is not only place the eval
dates in ascending order per patientID (that's easy), but assign a
'ranking' order to each eval so that, later on, I can compare all the
Eval #1's to each other, all the Eval #2's to each other, etc. It's
this last step of assigning the rank number that eludes me -- any
help would be greatly appreciated. Thanks,

Chris
 
C

Chris

The assumption here is that the eval# is just a figment of
the date order - it doesn't actually exist in any table.

If that is the case, my approach would be - at report
time - copy the required fields into a temporary work
table that has a column for Eval #, populate the column by
code and then use the worktable for your report
 
R

ronchito

Thank you for your response, however it is the "code" portion of your
answer that is not clear to me. I am not much of a programmer so any
suggestions on what procedures or code to use to accomplish the Eval#
column would be appreciated. Thanks.

(PS: your assumption about the Eval# column was correct)
 
C

Chris

If you can send me an empty version of your database
(table structures only - I don't need data) I should be
able to suggest a solution.

To do this (if you don't already know), Create a new blank
database, open your live database Copy the table, and when
you paste it, select "structure only"
send to

chris
@
mercury-projects.
co.
nz

Chris
-----Original Message-----
Thank you for your response, however it is the "code" portion of your
answer that is not clear to me. I am not much of a programmer so any
suggestions on what procedures or code to use to accomplish the Eval#
column would be appreciated. Thanks.

(PS: your assumption about the Eval# column was correct)


"Chris" <anonymous.chris@mercury-
Click to expand...
projects.co.nz.discussions.microsoft.com> wrote in message
 
Top