Retrieve most recent address by date

[misscrf]

I have about 2000 members. Some have had address updates, so they have more than 1. I need to single out just 1 address for each member, using their most recent address update date.

Tables:

tblmembers:
ID (PK Autonumber)
MembID - Their acct #
MemberName

tblAddress:
ID (PK Autonumber)
memb - lookup to tblmembers ID
add1
add2
city
state
zip
addchangedate

even the original address has an address change date. What I need is 1 listing for each membId - account number. If that member has more than 1 address, I need just the most recent addchangedate.

Can anyone help with the query?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[NorthNone]

Create a query from tblAddress selecting the MAX date by ID.
Join that query to tblMembers to get your final query.


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[hneal98]

Select  tm.MembID,
        ta.add1,
        ta.add2,
        ta.city,
        ta.state,
        ta.zip,
        Max(ta.addchangedate) = MaxAddr
From
        tblMembers tm
        INNER JOIN tblAddress ta on tm.MembID = ta.Memb
Group by
tm.MembID,
        ta.add1,
        ta.add2,
        ta.city,
        ta.state,
        ta.zip;

This might work.

 

[misscrf]

Thanks. I actually ended up doing this the lazy way. Sorting by MembID and then date. lol

This is good though, for next time.

misscrf

It is never too late to become what you could have been ~ George Eliot