Dates across multiple fields

[GLS]

I'm working on a waiting list table where patients can be offered a number of
appointment dates. I have 4 columns - 'Appt 1', 'Appt 2' etc. How can I set
up an expression in the 5th column which will select the maximum date across
the other 4?

many thanks

GLS

 

[Allen Browne]

If one patient can have multiple appointments, you need a related table with
*one* field for entering the dates.

This table will have fields like this:
AppointmentID AutoNumber
PatientID refers to the primary key of your Patients
table.
AppointDate Date/Time when the appointment is.

This approach is absolutely essential to understand in working with a
relational database: the idea of using a related table to handle one-to-many
relationships. Whenever you see repeating fields (such as Appt1, Appt2,
....), it always means you need to use a related table with many records,
instead of many columns in the one table (essentially a spreadsheet design.)

Once you have the relational design, it's dead easy to create a totals query
to give you the maximum date for each patient.

Post back if you need more details.

 

[GLS]

Hi Allen

I understand what you say about one to many relationships, however, this is
what I have inherited unfortunately. Accepting this idiosyncracy, is there
any way I can identify the max date across these fields?

Thanks again.

 

[John Spencer]

Since you are stuck with the structure, you can use a function to get the
maximum across the row. I've posted on example below.

In a query, you would add a calculated field

Field: MaxDate: fRowMax([Appt 1],[Appt 2],[Appt 3],[Appt 4])

Add the following code to a VBA Module and save the module with a name other
than fRowMax (perhaps mod_SpecialFunctions)

'------------- Code Starts --------------

'Version to handle dates, numbers, or text values
Public Function fGetRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum of a group of values passed to it.
'Sample call:
' fGetRowMax("-21","TEST","2", "3","4","5","6","7",0) returns "TEST"
' fGetRowMax(-21,2,3,4,5,6,7,0) returns 7
'Handles text, date, & number fields.

Dim i As Long, vMax As Variant
vMax = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) <= vMax Then
Else
vMax = Values(i)
End If
End If
Next

fGetRowMax = vMax

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County