Generating a new field based on existing fields

[Johanna Gronlund]

Hello,

I have a problem that I can't solve.

I have a table with patient admissions for one year. There are 2 fields:
date and time of admission (Format dd/mm/yyyy tt:tt:tt) and patient ID.
Essentially what I need to do is to generate an admission number for each
patient.

For example, patient ID number 333 had his first admission on the 01/01/2007
12:00:00 and this should be numbered 1. Same patient had another admissions
on the 15/01/2007 12:00:00 and this should be numbered 2.

Does anyone know if this is possible to do and if so, how can I go about it
with only mediocore knowledge of Access?

Thanks,

Anna

 

[KARL DEWEY]

Substitute your table name for [Change Requests] in this query ---
SELECT Q.PatientID, Q.Admission, (SELECT COUNT(*) FROM [Change Requests] Q1
WHERE Q1.[PatientID] = Q.[PatientID]
AND Q1.[Admission] < Q.[Admission])+1 AS Rank
FROM [Change Requests] AS Q
ORDER BY Q.PatientID, Q.[Admission];