I am working on a patient billing database. I have a table called Treatment, foreign key [Admission_ID]. Each admission can have more than one treatment. I need to be able to price the 1st treatment at full price, the 2nd at 1/2 price and the 3rd at 1/4 price of their total cost.

I don't know how to achieve this. I though maybe of ranking the treatments - i.e. If one admission had numerous treatments then I would rank them as T1, T2, T3 ect. then apply a calculation such as If T1 = total cost, If T2 = 1/2 cost ect.

I have tried a count but am not getting anywhere.

I have so far (SQL view of my query):
SELECT Treatment.Admission_ID, (SELECT COUNT (Admission_ID) FROM Treatment WHERE [Admission_ID]=[Admission_ID])+1 AS RANK, Treatment.Drug, TreatmentCodes.Code AS TreatmentCodes_Code, TreatmentCodes.CodeDescription, TreatmentCodes.Price, Treatment.Code AS Treatment_Code, Treatment.Code.Value, Treatment.CycleNumber
FROM TreatmentCodes INNER JOIN Treatment ON TreatmentCodes.[Code] = Treatment.[Code].[Value]
ORDER BY Treatment.Admission_ID;


Any help would be appreciated.
Regards C (intermediate user)

---

Answer from Richard Rost:

You could use a DCOUNT function to determine how many treatments they had prior to that one. You will need a TreatmentID or a DateTime stamp in your table, though, so you can tell which treatments came BEFORE the current one.

It would have to read something like:

=DCOUNT("AdmissionID","TreatmentTable","TreatmentID < " & TreatmentID)

Now you could put that into a query as a calculated field, and it will tell you how many treatments came BEFORE the current treatment. If the value is 0, you know it's the first one. If it's 1, then it's the 2nd treatment, and so on.

I cover DCOUNT in more detail in my Access tutorials. Here's a free tutorial on the DLOOKUP function, which is a close cousin of DCOUNT:

You can use the DLOOKUP function to look up a specific value from a table or query.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use DLOOKUP:

http://599cd.com/tips/access/dlookup-function?key=BlFAlEx