Writing a query to limit only one year when multiple years can occur

[klaidlaw]

I have built a membership database that has a dues table linked to membership table through member ID. The dues table contains dues for all members and the expiration month and year. This is a running list (i.e. it tracks every year a person pays dues) so a member has multiple expirations. I need to figure out how to write a query that allows me to see records that have an expiration year of x (2014) but if there is also a record for the member with an expiration date of y (2015) it doesn't pull that record. What I am trying to do is effectively say show me the records where x is the highest year for this member so I can check who has not renewed their membership. I tried ExpiresYr = x but not y, but that just shows the records that have a 2014 date regardless of whether there is a 2015 date. I hope this is clear enough to follow. I guessing I need to write an expression but I can't figure out if there is a way to use MAX to get what I want of if I need something completely different. Any help is appreciated.

[orange]

Please tell us more about your tables and relationships. How many ExpirationDates are current?
You might consider a boolean field that indicates that the member is current. Each year when a member renews, you update his/her boolean to No, create your new year record and make that the Current (Y).

I'm a little confused with

a member has multiple expirations.

.

Only 1 expiration date per member can be the EffectiveExpirationDate, right? I would think any ExpirationDates < Today are ""history"".

You may want to find the Max(ExpirationDate) for each member.

****untested code****

Select MemberId, ExpirationDate 
FROM  yourTable
WHERE
ExpirationDate = (Select Max(ExpirationDate) from yourTable as X
                            WHERE x.MemberId = MemberId)

This should give you the most recent ExpirationDate for each Memberid

Good luck.

[klaidlaw]

Thanks for the reply Orange,
Member table includes member name, ID, address, email etc. The dues table is linked through the ID on a one to many. Dues table includes the member ID, membership type, expiration month and expiration year etc. Because we need to keep track of all the money we get from any particular member, the same member ID will have a record each time they pay dues with an expiration date. So if a person pays dues in 2013 for a year, he has an expiration date of 2014. When he renews in 2014, he gets a new record with an expiration date of 2015. The 2013 and 2014 records are still in the dues table. The membership volunteer runs a query to see who has expiration dates for a paticular month, say May, and the current year so she can send out renewal reminders. The query I currently have shows the people who have May expirations, but doesn't recognize that some of them have already renewed and now have records for their 2015 and 2014 expirations. The volunteer gets a list of expirations that includes those who have already renewed because I haven't figured out how to eliminate them from the query.
Here is a sample for a single member with two dues payments recorded.

MemberID	Date	Membership Type	NumberYrs	Amount	Pay't Type	Tangible	AdtlContribution	Tax Deduction	Foreign	ExpiresMonth1	Year Expires	New Member Source	Membership Card	Comment
A117	4 /9 /2013	Sustaining	1	$250.00		$34.00	$0.00	$216.00	False	April	2014
A117	4 /17/2014	Sustaining	1	$250.00	VISA	$34.00	$0.00	$216.00	False	April	2015			2014 membership

I hope that helps make the issue a little more understandable. What I am trying to come up with is a way to say if this is the highest year show the record. If there is a record for the same ID with a newer year, disregard that record. I'm not sure the boolean field would do the trick because of how the table is set up unless I put the current boolean field in the membership table instead of the dues table.