Need Inactive/Expiring Records but not those who have renewed.

[sweeneysmsm]

I have 2 linked tables connected by ContactID: Contacts and Membership.

The membership subform includes a StartDate field and an EndDate field
(StartDate plus 365.25) and a Status field which shows as Active, Expiring,
Inactive depending on that record's EndDate field.

I want a report which shows those individuals whose FINAL status is Inactive
or Expiring. In other words, I don't want it to show me individuals who have
some inactive records but who have renewed their membership. How do I create
such a query?

While I could accomplish this by keeping only one record for each
individual, I do not want to do this as I want to be able to compare
Membership and Membership fees for various periods of time.

Any insight greatly appreciated...

Mary

 

[John Spencer]

Assuming that the fields in membership table are
ContactID
StartDate
EndDate
Status

The SQL statement for the query could look like the following.

SELECT Contacts.*
, Membership.StartDate
, Membership.EndDate
, Membership.Status
FROM (Contacts INNER JOIN Membership
ON Contacts.ContactID = Membership.ContactID)
INNER JOIN
(SELECT ContactID, Max(EndDate) as LastDate
FROM Membership
Group By ContactID) as X
ON MemberShip.ContactID = X.ContactID
And Membership.EndDate = X.LastDate
WHERE Membership.Status In ("Inactive","Expiring")

You could also do this by "stacking" two queries.
The first query would just get the Max EndDate for each contact from
membership table. Save it as qLastMembershipAction

Now build a second query that uses the membership table, the contacts
table, and qLastMembershipAction. Join Contacts to membership on the
ContactID fields and join Membership to the query on two sets of fields
(drag twice) ContactID to ContactID and EndDate to MaxEndDate.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Allen Browne]

Take a look at:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The solution uses subqueries. If that's a new idea, this might give you an
introduction:
http://allenbrowne.com/subquery-01.html

Ultimately, I don't think you want a Status field in the membership table.
It would be better if the database could calculate for you those where
Max(EndDate) < Date() etc.