SQL Statement?

[Bentleybelle]

I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?

 

[Jon K]

SELECT *
FROM tblPayments
WHERE PdForYr ="2004/5" and [MembershipNumber] Not In (SELECT [MembershipNumber]
FROM tblPayments
WHERE PdForYr ="2005/6");


However, "Not In" cannot be optimized by Access. If the table is large, you can use a series of three queries:-

Build two queries to return the records respectively for 2004/5 and 2005/6. Then use the Find Unmatched Query Wizard to find those membership numbers that exist in the 2004/5 query but not in the 2005/6 query.
.

 

[Bentleybelle]

Brilliant - thanks. . I have done it both ways and got the same results, however, the db will be large in due course (well, hopefully) so will rely on the unmatched query (never thought of that!)

Many thanks

 

[Kassy]

Same problems

I am also trying to use similar data because the membership database I've inherited is a copy of an example which does not return the data I want. Ive struggled to find a way of getting records for members for current season because my season starts Sept 1st each year and runs for 12 months so the year rolls over. Without running a query asking for start date, end date I wanted to use the system date (Short Date) so that every year the query doesnt have to be changed for the Current year! In other words something like:- CurrentMembers = members who registered from any date after 31/08 for 12 months.
If you run the query between Aug 31st up to Dec 31st then that is the current year and you should only display records covering that period.
'but' if you run the query after Dec 31st you would display all registrations from Aug 31st last year up to current date this year.
I'm Pulling my hair out with this one! Did you find it neccessary to have a seperate table for seasons?

 

[Jon K]

Try this, using the correct table name and date field name:-

SELECT *
FROM [TableName]
WHERE [DateFieldName] Between IIf(Format(Date(),"mmdd")>"0831", DateSerial(Year(Date()),9,1), DateSerial(Year(Date())-1,9,1)) And Date();
.

 

[Kassy]

Many Thanks in anticipation....

Sounds good will try code ASAP!

 

[Kassy]

At Last

Really does work. I hadn't been able to figure it out and tried all types of code but never seemed to get it quite right , nearly gave up. Thanks.