Sequence numbers

[Ryan Tisserand]

I have two queries, qryPatients and qryCharges. I have a main form bound to
qryPatients that has a subform bound to qryCharges. How would I make each
new charge squence with a number. To better explain, if I am looking at
Patient Jane Doe and I add the first charge, it would be assigned sequence
number 1, and then the second charge would have sequence number 2 and so on.
I would use an autonumber field in the table, but I want each patients
charges to be sequenced starting with one and ending with how ever many
charges there are.

 

[Michel Walsh]

Make a query to compute that 'rank'. Don't store it in the table, use the
query when you need its value.


A possible solution is:

SELECT a.patientID, a.chargeID,
(SELECT COUNT(*)
FROM qryCharges AS b
WHERE b.patientID=a.patientID AND b.TimeStamp <= a.TimeStamp) AS rank

FROM qryCharges AS a



and another one:


SELECT a.patientID, a.chargeID, COUNT(*) AS rank
FROM qryCharges AS a INNER JOIN qryCharges AS b
ON b.patientID=a.patientID AND b.TimeStamp <= a.TimeStamp
GROUP BY a.patientID, a.chargeID




Hoping it may help,
Vanderghast, Access MVP

 

[Ryan]

Thank you Michael, this worked for me. I would, if possible, like to know if
there is a way to save the values in the table.

 

[Michel Walsh]

Make a table out of the query, next, update the very first table with values
from the temp table, over a join (on PatientID) between these two tables
(should be easy using the query graphical editor).


Vanderghast, Access MVP