Ranking visit dates by individual

[ucdcrush]

Hi, I am trying to do a query with the following fields from the table
"tClinical"

patientID visitDate rank
123 5/1/2003 2
162 12/1/2000 2
123 4/15/2003 1
184 3/1/2006 1
162 2/12/1988 1


(the Rank field is one I'm trying to make). It is supposed to rank the
visit date for that particular patient, with 1 being the oldest. I
found this solution for someone's similar issue in another post, from
Tom Ellison:

Rank: (Select Count (*) from T:OpportunitiesReport
where T.product = OpportunitiesReport.product
AND [Extended Cost]>[T:OpportunitiesReport1].[Extended Cost]+1)

but when I try to make it work in my case, I can't. Here is what I've
tried to use:

rank: (Select count(*) from tClinical where [patientId] =
[tClinical].[patientId] AND [visitdt]>[tClinical].[visitdt]+1)

I do not know what the "T:" in the original solution does, but it did
not seem to like it when I tried with "T:" in my query...

When I run it, the rank column only contains 0s. Can anyone help?

Thanks,
Dave

 

[Guest]

I think the "T:" is an error and should be "T." as "T" is the table name.

"T.product" is indicating a field named "product" in a table named "T" but I
could be wrong.

 

[Tom Ellison]

Dear UCD:

I'm going to take a wild guess, and that is that what Tom was using was an
alias to a table. It's a shortcut to using the whole name of a table, but
in this case it's a lot more. Because the query is, effectively, using two
independent copies of the same table, they MUST be referenced separately.

Now, just given your results, I'm going to attempt the whole query:

SELECT patientID, visitDate,
(SELECT COUNT(*)
FROM tClinical T1
WHERE T1.patientID = T.patientID
AND T1.visitDate < T.visitDate)
AS rank
FROM tClinical T
ORDER BY PatientID, visitDate

Unlike your post, this is not a fragment of the query to be placed into the
design grid. Instead, create a new query, go to the SQL View, and paste
this in.

Please let me know if this helps, and if I can be of any other assistance.

I'll let Tom know when I see him!

To better understand this, the topics describing the techniques are Aliasing
and Correlated Subquery.

Tom Ellison

 

[ucdcrush]

Thank you very much Tom Ellison (or his associate?); I had to only
change visitDate to visitDt and from "FROM tClinical T1" to "FROM
tClinical as T1".

Thanks again!!
Dave

 

[Tom Ellison]

Dear Dave:

I'm glad it was no more difficult than that.

Yes, a very close associate!

Tom Ellison