Show latest Record

[Daniel]

Hi Guys

I have a table wich relies on a master table. For example a Person table and
a table of a medical incident. A person can have multiple medical incidents.

What I want to do is show each persons latest medical incident. My medical
incident table has a mandatory date feild in it.

What do I need to use in mySQL to show this?

 

[Allen Browne]

In the Control Source of a text box on your form, try an expression like
this:
=DMax("IncidentDate", "tblIncident", "PersonID = " & Nz([PersonID],0))

If you prefer, you can type this into the Field row in table design.

If you don't mind a read-only result, a subquery might be faster. See
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

Note that is is really important *not* to store this value in your table. It
must be calculated on the fly. Otherwise you are creating yourself a
maintenance nightmare, trying to cope with every possible combination of
inserting new records, deleting old ones, and modifying existing ones which
could affect the latest date. Having Access look up the date when you need
it is the only reliable, normalized approach.