Find a New Member

[Kassy]

I have several queries to find expired members, current members etc, however I can't get new members this year. Iv'e tried the unmatched query wizard using these two queries, also several other queries and tables but I either end up with all records or none. I'm going round in circles. I don't want a parameter query to ask the date, just some code to get the new records for this year and when the query is run next year it will show the new records for that year etc. Any help would be appreciated. My year date starts August 1st and ends following July 31st.

 

[JimmyK]

Can you paste what your queries are?

 

[Kassy]

Too much SQL so attachment:-

There is a lot of SQL to post so am adding an attachment with it in. At least I haven't used this facility in the forum before so hopefully it will work OK for me - if not will have to re-post!

 

[Kassy]

NewQueries tried again from unmatched Query wizard

Trying to find New Members using unmatched query wizard: -

LastYearQ (Returns 267 Records)
SELECT DISTINCT S_Members_Table.MemberID, S_Members_Type.MemberTypeID, S_Members_Type.Type, S_Members_Type.MemberDues, S_Members_Table.FirstName, S_Members_Table.LastName, S_Members_Table.Address1, S_Members_Table.Address2, S_Members_Table.Address3, S_Members_Table.PostCode, S_Payments_Table.PaymentAmount, S_Payments_Table.PaymentDate, GetSeason([PaymentDate]) AS Season
FROM S_Members_Type INNER JOIN (S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID) ON S_Members_Type.MemberTypeID = S_Members_Table.MemberTypeID
WHERE (((S_Payments_Table.PaymentDate) Between IIf(Month(Date())<8,DateSerial(Year(Date())-2,8,1),DateSerial(Year(Date())-1,8,1)) And IIf(Month(Date())<8,DateSerial(Year(Date())-1,7,31),DateSerial(Year(Date()),7,31))));

ThisYearQ (Returns 274 Records)
SELECT DISTINCT S_Members_Table.MemberID, S_Members_Type.MemberTypeID, S_Members_Type.Type, S_Members_Type.MemberDues, S_Members_Table.FirstName, S_Members_Table.LastName, S_Members_Table.Address1, S_Members_Table.Address2, S_Members_Table.Address3, S_Members_Table.PostCode, S_Payments_Table.PaymentAmount, S_Payments_Table.PaymentDate, GetSeason([PaymentDate]) AS Season
FROM S_Members_Type INNER JOIN (S_Members_Table INNER JOIN S_Payments_Table ON S_Members_Table.MemberID = S_Payments_Table.MemberID) ON S_Members_Type.MemberTypeID = S_Members_Table.MemberTypeID
WHERE (((S_Payments_Table.PaymentDate) Between IIf(Format(Date(),"mmdd")>"0731",DateSerial(Year(Date()),8,1),DateSerial(Year(Date())-1,8,1)) And Date()));

This is code produced from wizard

NewMemberQ (Returns 198 Records)
SELECT [ThisYearQ].[MemberID], [ThisYearQ].[MemberTypeID], [ThisYearQ].[Type], [ThisYearQ].[MemberDues], [ThisYearQ].[FirstName], [ThisYearQ].[LastName], [ThisYearQ].[Address1], [ThisYearQ].[Address2], [ThisYearQ].[Address3], [ThisYearQ].[PostCode], [ThisYearQ].[PaymentAmount], [ThisYearQ].[PaymentDate], [ThisYearQ].[Season]
FROM ThisYearQ LEFT JOIN LastYearQ ON [ThisYearQ].[MemberID] = [LastYearQ].[MemberID]
WHERE ([LastYearQ].[MemberID] Is Null);
LastYearQ and ThisYearQ both appear to work and produce records one set for last year and one set for this year but when combined the statement 'where LastyearQ memberID is null' doesn't produce members who had no membership number last Year -i.e new this year.