Using DMax function to compute highest visit number for patients

T

Ted

hi,

i have a subform/report (called "DaysView") which uses a table (also called
"DaysView") having a composite PK comprising

a) Last Name (LastName)
b) First Name (First_Name)
c) MI (M_I)
d) MR_Number (MRNumber)
e) IRB Number (IRBNumber)
f) RecordNumber (Visit)*

* where the terms in the parentheses are the recordsource's name on the
subform/subreport

in my subreport what i want is the maximum value of RecordNumber (Visit)
conditioned on the uniqueness of the combination of values of the 5 preceding
controls in the PK. basically, all i need is one record for any instance of
the subform/subtable that has a mate in the mainform, that record belong to
the one having the maximum of the RecordNumber (so that i can print out the
values of the non PK conrols in the subtable, like "Comments" and VisitType
and VisitDate and whathaveyou).

i think that it would be good to get this not from the subtable ("DaysView")
but from a query which used it. am i correct. if so, why am i having such a
hard time of it?

this is probably simple once you know how -- does anyone?

-ted
 
Top