Only displaying latest record for each person

[Waterfly]

Hi,

I am currently building a database that requires each person to have a membership, now they can be a "non-member", "ordinary" member which is a 1 year membership or a "life" member during different phases of time, what I want to do is be able to generate a report which shows me all the people who are non members or have expired membership, currently I have two tables, a people table containing all the people information and a membership table holding what memberships they have had over time.

I've tried creating a query using "Max" but that displays all the records e.g. if a person was a non-member between 2000-2005 then an ordinary member from 2005-2006 my report shows both rows, I only want to see the 2005-2006 row.

Any ideas on how I could do this?

Thanks

 

[ypma]

If your report is based on a query have you considered adding a criteria in the date column of the query example > #01/01/17# or even beween two dates .
Hope this will be of help to you

Regards Ypma

 

[Waterfly]

Attached is the form that is used to enter in data if that helps.

 

[Waterfly]

If your report is based on a query have you considered adding a criteria in the date column of the query example > #01/01/17# or even beween two dates .
Hope this will be of help to you

Regards Ypma

Thanks for the response, I have tried putting in something similar to the above, but it shows me the entire membership history as just opposed to the last membership record.

 

[Minty]

You need a sub query or two queries - have a read here for excellent advice on how to use them. http://allenbrowne.com/subquery-01.html

Basically get the latest entry for a member ID in one query, then link that query back to your member list to get the desired output.

 

[ypma]

WaterFly : I was thinking how I would do it prior to Minty's post and will post if for what is worth .
My solution seems to work fine providing a members status is only valid for one of the Membership status. w

sql view #SELECT PersonDetails.MemName, IIf([LifeMem]>Date(),[LifeMem],Null) AS LMember, IIf([NonMem]>Date(),[NonMem],Null) AS NMem, IIf([Ordinary]>Date(),[Ordinary],Null) AS OMem
FROM PersonDetails INNER JOIN memberstatus ON PersonDetails.ID = memberstatus.memberID;#

Regards Ypma

 

[Waterfly]

You need a sub query or two queries - have a read here for excellent advice on how to use them.

Basically get the latest entry for a member ID in one query, then link that query back to your member list to get the desired output.

Hi, afraid this is my first time doing subqueries so a little confused. I've created this query looking to get the latest expiration date:

SELECT Membership.MembershipID, Max(Membership.MembershipExpiry) AS MaxOfMembershipExpiry
FROM Person_Details INNER JOIN Membership ON Person_Details.PersonID = Membership.PersonID
GROUP BY Membership.MembershipID;

I've then created a second query which links the first query back to the membership table, but it doesn't seem to work. This is the SQL for the second query:
SELECT Membership.Membership, Membership.MemberSince, Membership.PersonID, Membership.MembershipID, Person_Details.Title, Person_Details.Forename, Person_Details.Surname
FROM (Person_Details INNER JOIN Membership ON Person_Details.PersonID = Membership.PersonID) INNER JOIN [Query 1 For Membership Expiry] ON ([Query 1 For Membership Expiry].MaxOfMembershipExpiry = Membership.MembershipExpiry) AND (Membership.MembershipID = [Query 1 For Membership Expiry].MembershipID);

 

[ypma]

Waterfly: is your problem resolved ? I Ask the questions, as you have not responded to mine or Mint's post.
Regards Ypma

 

[pbaldy]

Post 7 was moderated, posting to trigger notification emails.

 

[Waterfly]

Post 7 was moderated, posting to trigger notification emails.

Hi,

Yeah, the post went for moderation but has now been published above.

 

[pbaldy]

Hi,

Yeah, the post went for moderation but has now been published above.

Yes, I'm the one who approved it.

 

[ypma]

Waterfly. Not sure what moderated means, still I wish to know if my example database was of any use ? I have not attempted to address the sub query solution that Minty gave you . If my demo db. did not address your problem please let me know why so that I can lean something too.

Regards Ypma

 

[Waterfly]

Waterfly. Not sure what moderated means, still I wish to know if my example database was of any use ? I have not attempted to address the sub query solution that Minty gave you . If my demo db. did not address your problem please let me know why so that I can lean something too.

Regards Ypma

Sure, unfortunately my data is structured slightly differently, in that I have "Membership" which holds the type of membership and has an associated start and end date.

Your query seems to work, but I would also like to be able to pull the last record for a person even if that last record is expired.

 

[ypma]

WaterFly: Thank you for responding, I have read you post again and tried to create the db. as per your posts table structure. Using my latest db. demo what would your expect to seen in the query for Jane ? and any other examples of data you would expect to see the query ?

If my structure is wrong change it and post it back .

Regards Ypma

 

[Waterfly]

Hi Ypma,

I've updated the database, main things I've done is add an ID field to Memberstatus and linked this to the ID in Person Details. (It's a one to many relationship, e.g. one person can have many memberships).

I've also created a form which will show you more easily what record I want to get out. If we look at Jack for example, it should return "Ordinary" with a start date of 01/03/2018, but instead it returns the first record (start date of 01/02/2017). I've also done the same thing with Justin.

Many thanks for your help on this Ypma, it is much appreciated as I've been banging my head on the wall for a while now.

 

[ypma]

WaterFly: My cunning plan did not work out, so I took Minty's suggestion and created a sub form . I not sure if I am quite there, but close . query1 is the sub query which gives the result .

USED ACCESS 2010 Hope that's ok
Regards Ypma

 

[Waterfly]

Hi Ypma,,

Thanks, just had a look at query1 but it still shows Jack and Justin twice? It should only show Jack's ordinary membership and Justin's life membership.

Thanks for all your help!

 

[ypma]

Waterfly: I think I have run up against a brick wall as I cannot workout how to show just the one record . Hopefully Minty or one of the other members can salvage this post .
Regards Ypma

 

[isladogs]

Hi

As requested, a fresh perspective on this.
See attached.

I've added a boolean field 'Active' to the tblMemberStatus and modified the query and subform to filter for Active=True

If you're happy with this idea, all you need to do is when members are added or changed is
a) set Active=True for the current membership type
b) set Active = False for any previous membership types

You could easily do both of these using VBA

 

[jdraw]

I have taken one of the databases that was posted.
Created a data model as attached.
Created the tables and queries to use the structure in the model.
All my tables and queries start with "j"...
I hope I have understood the requirement. If not, please clarify.

Hope this helps.