Find members who have yet to pay

[Oregoncoder]

I have a members table and a payments table. The payments table consists of a record for every year the member has paid. I need to find the members who have yet to pay for 2024. So find the members who do not have a record in the payments table for 2024. The tables are linked via “memberid”.

[Micron]

Not much info to go on. The names of the tables and pertinent fields would give anyone something to start with. Or drag the tables into a query, join them on the proper fields if it doesn't happen automagically, add the fields to the query design grid that you want to see in the results and make sure the date field is one of them. If your date field data is a complete date (i.e. you haven't split dates into component parts) then in the date field criteria row try DatePart("yyyy","myDateFieldNameHere") = 2024

[Oregoncoder]

Not much info to go on. The names of the tables and pertinent fields would give anyone something to start with. Or drag the tables into a query, join them on the proper fields if it doesn't happen automagically, add the fields to the query design grid that you want to see in the results and make sure the date field is one of them. If your date field data is a complete date (i.e. you haven't split dates into component parts) then in the date field criteria row try DatePart("yyyy","myDateFieldNameHere") = 2024

that would show those that have a record in the payments table for 2024. I am trying to find those that don’t have a 2024 record.

appreciate the help

[Welshgasman]

There is a wizard for that I seem to recall?

[Oregoncoder]

THe wizard only shows payment records that have no member associated with them. I guess I code always create some via code to loop through and find those name that don’t have a related record in payments. Then create a temp table with those names, but hoping for a simple query to do so.

[Micron]

that would show those that have a record in the payments table for 2024. I am trying to find those that don’t have a 2024 record.
appreciate the help

Yes, I goofed on that. I do believe WGM is correct - there is a wizard for finding records that are not in a table (or query if need be). Also, if your table has the date field then wouldn't
Is Null work in a query? That way you would also capture those who didn't pay for prior years as well.

[Oregoncoder]

Yes, I goofed on that. I do believe WGM is correct - there is a wizard for finding records that are not in a table (or query if need be). Also, if your table has the date field then wouldn't
Is Null work in a query? That way you would also capture those who didn't pay for prior years as well.

THe wizard only shows payment records that have no member associated with them

This is a snap of my file structure. How would I find if I didnt have a record for the year 2024?

[CJ_London]

I note you haven't taken the advice provided in your other thread about table and field names - using spaces and reserved words. Your payments table also appears not to have a primary key. You don't have to take the advice, but a) every time you ask a question, it will be pointed out and b) at some point it will come back to bite you with inexplicable errors. All of which will lose you time trying to resolve it.

To answer your question, you need two queries, the first to find members who have paid

SELECT MemberID
FROM Payments
WHERE year =2024

call that qryPaid

Then a second query left joining the members to qryPaid to find those that haven't paid

SELECT MemberID
FROM WWWRoster LEFT JOIN qryPaid ON WWWRoster.MemberID=qryPaid.MemberID
WHERE qryPaid.MemberID is Null

[Oregoncoder]

I note you haven't taken the advice provided in your other thread about table and field names - using spaces and reserved words. Your payments table also appears not to have a primary key. You don't have to take the advice, but a) every time you ask a question, it will be pointed out and b) at some point it will come back to bite you with inexplicable errors. All of which will lose you time trying to resolve it.

To answer your question, you need two queries, the first to find members who have paid

SELECT MemberID
FROM Payments
WHERE year =2024

call that qryPaid

Then a second query left joining the members to qryPaid to find those that haven't paid

SELECT MemberID
FROM WWWRoster LEFT JOIN qryPaid ON WWWRoster.MemberID=qryPaid.MemberID
WHERE qryPaid.MemberID is Null

Thanks again for your help. Tried a simple change of name to fullname and had about 20 queries/forms/reports to fix then. Not sure of a simpler method? Planned to come back to it.

[Welshgasman]

THe wizard only shows payment records that have no member associated with them

Why would you have payments not linked to a member?

Surely if you created a query for 2024 payments and used that as the second source in the wizard, that would show you those who have not paid?

Been a while since I used the wizards though, I admit.

[moke123]

Why would you have payments not linked to a member?

I think it's the other way around. Members not linked to a payment.
My first thoughts were you also need a way to exclude inactive members as they also would not have payments each year.

[Oregoncoder]

I think it's the other way around. Members not linked to a payment.
My first thoughts were you also need a way to exclude inactive members as they also would not have payments each year.

a payment is only entered when it is made for that member. I have over a hundred members who do not have a 2024 payment record. I suppose I could’ve mass uploaded a 2024 record for each member and then identify those that have a zero dollar amount associated with the payment record.

[Welshgasman]

a payment is only entered when it is made for that member. I have over a hundred members who do not have a 2024 payment record. I suppose I could’ve mass uploaded a 2024 record for each member and then identify those that have a zero dollar amount associated with the payment record.

Yes, you could have done that with a DatePaid = Null.

So many ways to do a process in computer code. Up to you which is the best for you?

[ArviLaanemets]

Your tables must be like:
WWWRoster: MemberID, ForeName, LastName, ... (Leave FullName out of here!);
Payments: PaymentID, MemberID, PaymentYear, PaymentAmount (No member names here!)

To get the list of all members, not having paid in year 2024, the basic query will be like

SELECT mbr.MemberID, mbr.LastName & ", " & mbr.ForeName AS FullName 
FROM WWWRoster mbr LEFT JOIN (SELECT pmt.PaymentID, pmt.MemberID,  FROM Payments pmt WHERE pmt.PaymentYear = 2024) pmt2024 ON pmt2024.MemberID = mbr.MemberID
WHERW pmt2024.PaymentID Is Null

Probably you have to check, is the member active for year 2024 too. When not, then obviously he/she didn't have to pay at all. So the table WWWRoster must have some field(s) allowing to decide about membership being active for certain year, or not.
Another thing you have to take into account is, that person names aren't fixed really. Without them being put into some names history table, asking for info from past years you get person's current name, which can be different than real one in asked year. And when you then have to compare gotten data e.g. with some printed documents, you may be in trouble!

[CJ_London]

same suggestion as post #8