Find 'last' non-empty value for a field

[deekaye]

Hi
I have a list of contacts in a contacts table and list of activities
in activities table that includes all interactions with each client.

In each activity their is a 'status' field. I need a way to bring up
the latest 'status' field value for each contact. The latest would be
the one with the latest 'log date' (a field in the activity table).

What makes things tricky is that status is not a required field, it
only gets filled in when the status of the contact changes, so for some
records status may be blank. I need the latest (ie latest log date)
status value that excluding all blank entries.

How would I bring this up in a query?

 

[deekaye]

I should mention in this case that status is a text description such as
unhappy, happy, OK but should be able to be anything and not
necessarily able to put on a numerical scale.

 

[Jeff Boyce]

Create a query in design mode.

Add your table.

Add the Status field and the [Log Date] field.

Add the field that identifies which client the record pertains to.

Depending on the data type of the [Status] field, put a selection criterion
in the "cell" under the field -- you might use Not IsNull, or perhaps <>"".
Uncheck the Show checkbox.

Click on the Totals button (Greek sigma character).

GroupBy the Client field, use Where for the Status field, and use Max for
the [Log Date] field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[deekaye]

Perfect, I could have spent my whole life struggling on something so
simple.

Thanks.

Create a query in design mode.

Add your table.

Add the Status field and the [Log Date] field.

Add the field that identifies which client the record pertains to.

Depending on the data type of the [Status] field, put a selection criterion
in the "cell" under the field -- you might use Not IsNull, or perhaps <>"".
Uncheck the Show checkbox.

Click on the Totals button (Greek sigma character).

GroupBy the Client field, use Where for the Status field, and use Max for
the [Log Date] field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi
I have a list of contacts in a contacts table and list of activities
in activities table that includes all interactions with each client.

In each activity their is a 'status' field. I need a way to bring up
the latest 'status' field value for each contact. The latest would be
the one with the latest 'log date' (a field in the activity table).

What makes things tricky is that status is not a required field, it
only gets filled in when the status of the contact changes, so for some
records status may be blank. I need the latest (ie latest log date)
status value that excluding all blank entries.

How would I bring this up in a query?