My query does not yeild the results desired.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
Back
Top