My query does not yeild the results desired.

[Guest]

I am working on a database for collections. I have a separate table for
receipts, Member List, and Invoice year. Collections are recorded per
invoice year and have a yes/no question on paid in full. I have attempted to
get a query that will provide unpaid members per invoice year. If any member
has paid for one year, they are excluded from this query. I have three
collections for 2006 and 8 for 2007. No matter what invoice year parameter I
use, all 11 collections are excluded. I would like to exclude only those in
the invoice year parameter. My selection criteria are the invoice year from
the Invoice Year table and null value for paid in full.

 

[John Nurick]

Hi Chigger,

Do it in stages.

1) create a query that returns nothing but a list of invoice years.
Lets call this qryYears.

2) create a query that returns nothing but a list of Members,
qryMembers

3) in a third query (qryAllMembersYears) join these (cartesian join)
to get a recordset with one entry per member per year, e.g.
SELECT InvoiceYear, MemberID
FROM qryYears, qryMembers;

4) a fourth query that returns a list of all members in each year that
have paid in full (qryPaidInFull)

4) finally use the Find Unmatched query wizard to build a query that
finds all records in qryAllMembersYears that don't have counterparts
in qryPaidInFull.

 

[Guest]

Collections are recorded per invoice year and have a yes/no question on
paid in full.If your 'yes/no question' is a Yes/No datatype field then checking 'paid in
full' for null will not work. A Yes/No fo\ield stores data as a 0 (zero) or
-1 (minus one). A minus one is a Yes or True.