Need Help With Query Criteria

[Madmartigan]

I don't know why I can't wrap my head around this one because it seems like it should be pretty simple but it just isn't working. I'm writing a Select Query against a Table that has these fields:

Customer ID
Move Date
Customer Name
Address


City
State
Zip
Phone

A given customer can have multiple move dates and addresses for different places they have lived. What I want to do is write a query that pulls all customers by customer ID and selects their most recent move date and whatever address information happens to be associated with that record. In other words I want all customers and their most recent addresses. I've tried max, first, sorting, and I keep getting skewed or incorrect results when I validate. Any help is appreciated.

[pbaldy]

See if this helps:

http://www.baldyweb.com/LastValue.htm

[Madmartigan]

Ok, let me see if I understand this correctly.

I know that my customerID as it stands does not create a primary key. But the combination of customerID + Move Date does.

So query #1 would find for each customer ID, the max move date.

Query #2 would be linking my table to query #1 as a primary key to pull the rest of the records? Worded poorly, I know, but is that about it?

[Madmartigan]

I tested it out and it appears to have worked! Thank you Paul for the help! Much appreciated!

[pbaldy]

Happy to help!