Querying latest chronological entry for a client

[vangogh228]

I have a database that has a table of address updates, so that each new address is added to the table as the client moves. Now I am being asked to provide a report that includes the clients CURRENT address. So, I need to query the table for the last-entered address for a client. The goal is to put a button on the client's main screen that will print a report that includes the current address. In the address table, I have a field with an autonumber, a field with the client number in it, then the necessary address fields.

I do not know any VBA code except that which I have learned from the wizards and copying from objects like buttons. I know this complicates things... but any help is appreciated. This database is for a nonprofit organization that provides services to semi-homeless people with mental illness, and this new individual-client data report is required for funding when services are provided by the organization. Thanks so much for any help!

 

[Dwight]

Do you have to keep the old address?

Can't you have your user update the old address to the new rather than having both?

If not maybe you could use a checkmark to denote the current address. This is a yes/no value. So if the person moved 3 times only 1 address would have the current address box checked.

This makes it easy to set as a criteria when you run queries. You just put "yes" in the criteria field.

Dwight

 

[neileg]

If you have an effective date on the address record you can use the Max function in a query to retrieve the one with the most recent date.

 

[vangogh228]

Dwight: Yes, unfortunately all the addresses have to be kept so that the client activity ("where they've been living") can be tracked. State funding requirement. UGH! Thanks for the idea on checkbox.

Neil: I'll try that. I've never used the MAX value before, but I certainly can study on it. Thanks!

Any other ideas are certainly welcome.

Thanks again.

Tom