Multiple "rankings" or "numberings" in queries

  • Thread starter Thread starter ronchito
  • Start date Start date
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
 
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
 
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)
 
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
 
Back
Top