query for something and not something else

[Guest]

I am making invoices for a gym database. IN the query for the invoices there
are 2 tables 1) clients
2) membership

I want all the people who in the clients table are "monthly" members i.e pay
every month

Besides for the people who have a record in the membership table of January
'05 and have paid ie. amount owing is naught.

I'm not sure how to write the query and besides for that i am worried that
if the query sees a certain fell has january 05 it wont give me that record
but it will give me all the guys other records eg. february 05, march 05 and
so he'll still come up on the invoices whereas i want that if he has paid for
January 05 that guy shouldn't come up at all?

please can someone help me.

 

[Jeff Boyce]

Queries are based on underlying data in tables. Unless you provide us more
of an idea of how you've structured your data (e.g., how is "monthly"
determined), it will be tough to offer more than a guess about how to query
it.

 

[John Spencer]

It would be nice if you gave us a bit more information on your tables.

One way of doing this would be to use an SQL statement that looks something
like:

Select Clients.*
FROM Clients
WHERE Clients.Payment= "Monthly"
AND NOT Exists (SELECT * FROM Membership
WHERE TheMonth="Jan05"
AND Membership.ClientID = Clients.ClientID)

Two Query solution.
Query One: save as MonthPaid
From the membership table get all members that HAVE PAID for the month

Now using the unmatched query wizard
Select the Clients table
and find the unmatched records in the Membership table