Microsoft Access Query Return Issue

Question

I have made an Access database to keep track of monthly payments for a soccer and basketball club. The database includes a Clients table and a Payments table.

Each payment record is linked to a specific client. Each month, payments are made by clients. I have created a query to return all of those who have not made their monthly payment.

For my query to return these clients who have not paid, I enter the month I am searching for and if they don't have a record that matches that then they will be included in the list.

I have run into an issue with the second payment month. For example, if I type in "June" as the month I'm searching for, people who have a record for "May" show up in the list, even if they've paid for "June" as well.

I have tried looking into different ways to filter the table but am not sure of how to filter it in such a way that these unwanted values will not show up, nor do I know if that's possible. I also realize that this issue could be done away with if I were to delete old data as I update the table monthly, but I would like to keep track of who is paying each month and not have to delete records. I have not added any additional code to try to mitigate this issue, as I'm unsure of what to do.

SELECT Clients.[Player ID], Clients.[Player Name], Clients.[Contact Number], Payment.[Payment Amount], Payment.[Payment Month]
FROM Clients INNER JOIN Payment ON Clients.[Player ID] = Payment. [Player ID]
WHERE (((Payment.[Payment Month]) Not Like [Enter the month:])) OR (((Payment.[Payment Month]) Is Null));

To reiterate, I'd like for someone's name to show up when I type in "June" if they have not paid for that particular month. I do not want their name to show up if they've paid for "June" but also "May" as the record for "May" shows up in the query results. Any help would be greatly appreciated.

You must change your table schema to hold the year and month as numbers, not literals. Then you can easily filter whatever you need regarding (un)paid year/months. — Gustav

Lee Mac Lee Mac · Accepted Answer · 2019-06-21T17:31:10

To obtain the appropriate results from your query, I would suggest using a subquery in conjunction with a left join in the following way:

select
    clients.[player id], 
    clients.[player name], 
    clients.[contact number]
from
    clients left join 
    (
        select payment.[player id] 
        from payment
        where payment.[payment month] = [Enter the month:]
    ) payments
    on clients.[player id] = payments.[player id]
where
    payments.[player id] is null

Alternatively, you can use a correlated subquery such as:

select
    clients.[player id], 
    clients.[player name], 
    clients.[contact number]
from
    clients
where not exists 
(
    select 1 from payment 
    where 
    clients.[player id] = payment.[player id] and 
    payment.[payment month] = [Enter the month:]
)

Here, I have used the equality operator (=) in place of the like operator given that I suspect you are unlikely to want to match multiple month names using wildcards.

However, as @Gustav correctly points out - both of these will include results from multiple years unless you also include a field to store the payment year alongside the payment month.

I would also suggest storing the month as an integer value (as opposed to a string) so that it may be indexed more efficiently; then, should you wish to display the month name in a query or as a parameter, either use a separate Months table, or use the MonthName function.

Alternatively, use a Date field to store the payment date, and then use the Year & Month functions or a date range in your selection criteria.

Microsoft Access Query Return Issue

1 Answers