Multiple Addresses and Mail Labels (1 Viewer)

[Steven Deetz]

I am developing a membership database and am trying to incorporate a yearly phenomenon with our members. Many head south during the winter months for warmer climates and return in the spring. For our members to continue receiving their newsletter I could just have the database user go in and change the complete address of each member when they head south and change the address back when the member heads north. This is not very elegant.

I have created a Member table with a MemberId primary key and an Address table with both an AddressID and a MemberId. There is a relationship created where one member can have many addresses. In the Address table I also have a DateUpdated field. My reasoning goes that a user will change the DateUpdated field for each member only when each member will head south and when each member will return.

The problem is trying to have a query list only the "current" address. I have one query that looks at the address table and lists only those addresses where DateUpdated<=Date(). I can also create another query based on the first query's results that list the MemberId and the DateUpdated where the query pulls only the Max of the DateUpdated..

What I want to do next, but am truly stumped, is to find the AddressId for each MemberId and MaxOfDateUpdated. I think a subquery might be the answer, but I am not sure.

Thanks for any suggestions or ideas!

 

[David R]

How about..

Your main table contains the members. If you had a subtable that had a StartDate, EndDate, and the Address fields (plus, of course, a reference field to the MembershipID so it knew where to look), you could have your query pull the address for which today's date (or the NewsletterPrintDate, or whatever) is included.

(alternatively you could just call one entry "Winter" and one "Summer", but not everyone leaves on the same day of the year to fly south).

Try this topic to see how to check if a date is between two stored dates: http://www.access-programmers.co.uk...=&threadid=30233&highlight=date+between+query

 

[Steven Deetz]

Thanks for the idea David! I had set up a series of queries and tables so the user would only have to enter the date a member left one residence for another. However, your idea of having them enter two dates (a DateStart and a DateEnd) involves only one simple query. Here is the SQL that did the trick!

SELECT ADDRESSES.AddressID, ADDRESSES.Address, ADDRESSES.City, ADDRESSES.State, ADDRESSES.ZipCode, ADDRESSES.MemberID, Date() AS Today
FROM ADDRESSES
WHERE (((Date()) Between [DateStart] And [DateEnd]));

Thanks again for the idea.

 

[David R]

Glad it worked...

I thought it was a neat idea when I saw it, so when you mentioned the problem I remembered where to look it up.