Select query criteria

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

Guest

My membership database contains a table for annual membership contributions.
I record information about each check received, as well as the membership
"season" which it covers.

I have set up a multi-table query to give me the names and addresses of
members who have not paid for a given season so I can send them reminders. I
would like to be able to query those members who haven't paid for two seasons
running, but I can't figure out how to structure the query. Can anyone help
me with the query format OR suggest how to re-structure my table in order to
permit this kind of query?

Thanks,
Bruce
 
I'm sure this isn't the best way to do it, but I couldn't think of how to
structure it either.
Firstly, instead of a "checks received" table, make it a "dues" table.
Create a DUE amount for each person for each year. when they pay, update the
PAID amount. That way you can not only tell how much they have (or haven't)
paid, but how much they should have paid. Each person MUST have a dues record
for each season before you run the query...

I'm assuming Tables
Tbl_Person with PersonID, PersonName
Tbl_Dues with DuesID, Season, Amount_Due, Amount_Paid, PersonID

Then, create a query like this:
SELECT Tbl_Person.PersonName, Tbl_Dues.Season, Tbl_Dues.Amount_Due,
Tbl_Dues.Amount_Paid, Tbl_Dues_1.Season, Tbl_Dues_1.Amount_Due,
Tbl_Dues_1.Amount_Paid
FROM (Tbl_Person LEFT JOIN Tbl_Dues ON Tbl_Person.PersonID =
Tbl_Dues.PersonID) LEFT JOIN Tbl_Dues AS Tbl_Dues_1 ON Tbl_Person.PersonID =
Tbl_Dues_1.PersonID
WHERE (((Tbl_Dues.Season)="2004") AND
((Tbl_Dues.Amount_Due)>[Tbl_Dues].[Amount_Paid]) AND
((Tbl_Dues_1.Season)="2005") AND
((Tbl_Dues_1.Amount_Due)>[Tbl_Dues_1].[Amount_Paid]));


Notice that I've joined the dues table to the person table TWICE, with two
left joins (easier to see if you input the SQL then switch to design view).
This is because we're looking for a different record on the table each time -
once we're looking at season "2004", once "2005".

Hope this helps... and apologies for not coming up with a neater way.
 
My membership database contains a table for annual membership contributions.
I record information about each check received, as well as the membership
"season" which it covers.

I have set up a multi-table query to give me the names and addresses of
members who have not paid for a given season so I can send them reminders. I
would like to be able to query those members who haven't paid for two seasons
running, but I can't figure out how to structure the query. Can anyone help
me with the query format OR suggest how to re-structure my table in order to
permit this kind of query?

Thanks,
Bruce
Click to expand...

Jack's suggestion (a new table) may be the best bet; but you can use a
(slow and inefficient but it does work) NOT EXISTS query to do this.

Assuming that you have a Payments table with a PaymentDate field (note
that you can see your database structure - we cannot!), try a
criterion on MemberID of

NOT EXISTS(SELECT MemberID FROM Payments WHERE Payments.MemberID =
Members.MemberID AND PaymentDate BETWEEN [Enter start date:] AND
[Enter end date:])

The criterion within the NOT EXISTS select could reference your
"season" field instead, but I have no notion how you might have stored
that information!

John W. Vinson[MVP]
 
Thanks to Jack and John for their suggestions. I tried something else,
however, that seems to work: added the "Checks" table again to the query so
it appears twice, and specified a different season from each check table. The
resulting SQL is:

SELECT tblChecks_1.[Membership season], tblChecks_1.Payer,
tblChecks.[Membership season], tblChecks.Payer, tblMembers.LastName,
tblMembers.FirstName, tblMembers.PrefEmail
FROM (tblMembers INNER JOIN tblChecks ON
tblMembers.MemberID=tblChecks.MemberID) INNER JOIN tblChecks AS tblChecks_1
ON tblMembers.MemberID=tblChecks_1.MemberID
WHERE (((tblChecks_1.[Membership season])="2004-2005") AND
((tblChecks_1.Payer) Is Null) AND ((tblChecks.[Membership
season])="2005-2006") AND ((tblChecks.Payer) Is Null))
ORDER BY tblMembers.LastName;

and it seems to pull the records I need. Any problems that you can see with
it?

Thanks,
Bruce

John Vinson said:
My membership database contains a table for annual membership contributions.
I record information about each check received, as well as the membership
"season" which it covers.

I have set up a multi-table query to give me the names and addresses of
members who have not paid for a given season so I can send them reminders. I
would like to be able to query those members who haven't paid for two seasons
running, but I can't figure out how to structure the query. Can anyone help
me with the query format OR suggest how to re-structure my table in order to
permit this kind of query?

Thanks,
Bruce
Click to expand...

Jack's suggestion (a new table) may be the best bet; but you can use a
(slow and inefficient but it does work) NOT EXISTS query to do this.

Assuming that you have a Payments table with a PaymentDate field (note
that you can see your database structure - we cannot!), try a
criterion on MemberID of

NOT EXISTS(SELECT MemberID FROM Payments WHERE Payments.MemberID =
Members.MemberID AND PaymentDate BETWEEN [Enter start date:] AND
[Enter end date:])

The criterion within the NOT EXISTS select could reference your
"season" field instead, but I have no notion how you might have stored
that information!

John W. Vinson[MVP]
Click to expand...
 
Back
Top