One to many relationship between tables

[derekd99]

Hi, I am missing something here. I have used Access for years but taught myself how to use it.
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.

I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.

I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.

I would be grateful for any pointers.
Thank you.

 

[plog]

This is going to take a sub-query. To find out those who haven't paid in 2014-2015, first build a query to tell you who has. Call that query 'sub1'.

Then in a new query LEFT JOIN sub1 to your member table. You want to show all records from members, and just those matching in sub1. Bring in all the data from members and the ID field from sub1. Underneath the ID field from sub1 add criteria to show NULL values. Run that query and there's your unpaid list.

 

[derekd99]

Many thanks for your help. It worked perfectly. Sub queries are new to me. I don't know SQL and thought you needed that for sub queries.