How would I query this?

[Jojojo]

I have a members table that contains member information, including the type of member that they are (fitness, massage etc). There is also a registration table, that stores the info for a fitness class that the member has registered for.



I need to run an expired member report. However - the user did not enter ALL of the past registrations into the database (due to having to go back years to do this).

So I need to be able to query, those members who DON'T have a registration ID as well as those that do - based on their expiration date of their membership.

I can figure out everything except how to pull in those who do not have a registration. As soon as I add that table into the query - it automatically filters those who don't out.

Thanks!!

[orange]

Please show table and fields.

[Jojojo]

here is the relathipship view with the fields

[orange]

The members that don't have a registration id, are those members in the Members table that do not appear in the Registration table.

See Left Join

SELECT Members.AttendeeId, Members.AttendeeFirstName, Members.AttendeeLastName
FROM Members LEFT JOIN Registrations ON Members.Attendeeid = Registrations.Attendeeid
WHERE (((Registrations.Attendeeid) Is Null));

[Jojojo]

Thanks! All members are in the members table, whether they have registrations or not. I modified it to sort that list by the Member Type's that I need. The code is pasted below. But now - I can't wrap my head around how to add in those members who actually do have a registration ID, but whose registration date is before a certain date (I would use a form to have the user input the date)

SELECT Members.AttendeeID, Members.AttendeeFirstName, Members.AttendeeLastName, Members.MemberTypeID
FROM Members LEFT JOIN Registration ON Members.AttendeeID = Registration.AttendeeID
WHERE (((Registration.AttendeeID) Is Null) AND ((Members.MemberTypeID)="Fitness" Or (Members.MemberTypeID)="Massage/Fitness"));

Also - if a member has 5 registrations, I just want them to appear once in the list. In the end the report that this will print to, doesn't have to show registration dates.