Need count, PatientID by Admit Date by Visit Date

[Guest]

I need two counts on a MSAccess FORM (please don't talk about Excel or
Reports).
Fields on the table are PatientID, Admit Date, Visit Date, Visits, TotVisits

1) count number of visit dates by the Admit Date for a PatientID, so that
the first visit date makes Visit = 1, second visit date makes Visit = 2, etc.
for that PatientID for that admit date.
2) count the total number of visits (TotVisits) for a Patient ID by Admit
Date.

I'd like this to run as a macro, but I can handle some VB code. Thanks

 

[Steve Schapel]

Vance

Your Table should only contain the fields PatientID, Admit Date, Visit
Date. Visits and TotVisits are derived/calculated data, and as such
should not be stored in a table. Make a Query based on your table,
which uses Domain Aggregate Functions to return the Visits and TotVisits
values, and then base your Form on this Query. Assuming your table is
called PatientVisits, the SQL view of such a query will look like this...
SELECT PatientID, [Admit Date], [Visit Date],
DCount("*","PatientVisits","[PatientID]=" & [PatientID] & " And [Admit
Date]=#" & [Admit Date] & "# And [Visit Date] <=#" & [Visit Date] & "#")
AS Visits, DCount("*","PatientVisits","[PatientID]=" & [PatientID] & "
And [Admit Date]=#" & [Admit Date] & "#") AS [Tot Visits]
FROM PatientVisits