Results 1 to 15 of 15
  1. #1
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21

    Exclude records within same criteria

    I need to archive membership records and intend to use an Append Query to copy records to a new Archive Table. I have a Members Table and a Subscription Payments Table which seems the most appropriate table to use. This has four relevant fields: MEMBER_NO, FOR_YEAR, AMOUNT, PAID_ON.



    The table records every payment made by each member so that a single member may have several rows representing each year of payment.

    I want to select all records where the PAID_ON date is more than 2 years older than today's date. The expression <Date() - 730 seems to handle this ok. My problem is how to exclude those records of members who meet this criteria but who also have payment dates last year and this year.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    some backwards thinking may be useful here. instead of taking the ones you do want to archive, query for the ones you dont want to archive and take all but those.

    instead of thinking that you want to archive those with payments over 2 years old, think you want to archive anybody who hasnt made a payment within 2 years. Query for all accounts that have payments within 2 years and then archive all accounts NOT IN that list.

  3. #3
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    Clever approach - thanks. I'll try that.

  4. #4
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    Quote Originally Posted by brooke48 View Post
    Clever approach - thanks. I'll try that.
    OK. I've come a bit unstick with this and would appreciate a little more help !
    I've created a SELECT query "qryPayments Within 2 Yrs" as recommended and this runs successfully. SQL below:
    SELECT MEMBERS.*
    FROM MEMBERS INNER JOIN [SUBSCRIPTION PAYMENTS] ON MEMBERS.[MEMBER NUMBER] = [SUBSCRIPTION PAYMENTS].[MEMBER NO]
    WHERE ((([SUBSCRIPTION PAYMENTS].[PAID ON])>Date()-730));

    I'm having trouble with the next query which aims to return all payments excluding those returned by the first query "qryPayments Within 2 Yrs". I'm getting an "Enter Parameter Value" box every time but can't trace a typo or other reason for this. SQL for this query below:
    SELECT MEMBERS.*
    FROM MEMBERS INNER JOIN [SUBSCRIPTION PAYMENTS] ON MEMBERS.[MEMBER NUMBER]=[SUBSCRIPTION PAYMENTS].[MEMBER NO]
    WHERE ((([SUBSCRIPTION PAYMENTS].[PAID ON]) Not In ([qryPayments Within 2 Yrs])));

    This query is also not excluding the records it should so I've obviously missed the point somewhere. How do I progress this ?

  5. #5
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Try this.

    SELECT MEMBERS.*, [SUBSCRIPTION PAYMENTS].[MEMBER NO]
    FROM MEMBERS LEFT JOIN [SUBSCRIPTION PAYMENTS] ON MEMBERS.[MEMBER NUMBER] = [SUBSCRIPTION PAYMENTS].[MEMBER NO]
    WHERE ((([SUBSCRIPTION PAYMENTS].[PAID ON])>Date()-730) AND [SUBSCRIPTION PAYMENTS].[MEMBER NO] is null);


    If you wish to go with the compund query, change your initial query.
    in your first query, theres no need to return *. Just pull the Account Numbers. Then:
    SELECT *
    FROM MEMBERS
    WHERE [MEMBER NO] Not In([qryPayments Within 2 Yrs])

    Side note: When possible, try to remove spaces from your table names and your field names. It can cause issues if you forget the [].[] form and when youre coding in VBA.

  6. #6
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    Making some progress by following your advice but still need to refine this. The first query (Within 2 Yrs) includes exactly the records I want but the second query (Over 2Yrs) includes records for members with payments older than 2 yrs (good) AND within 2 Yrs (bad).

    For reference, my SQL for the 2nd query looks like this:
    SELECT MEMBERS.*
    FROM MEMBERS
    WHERE (((MEMBERS.[MEMBER NUMBER]) Not In ([qryPayments Within 2 Yrs])));

    One final point, when I run the Over 2 Yrs query, a parameter value box comes up for BOTH queries in turn but will run when I ok through them.

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    the parameter box is popping up because its not finding one of the fields you are referencing. check to make sure the spelling is exact, case, and brackets around anything with a space in it.

  8. #8
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    Thanks for that suggestion. Going back to the other problem with isolating records, the Subscription Payments table has multiple entries for each member where payments have occured for consecutive years. This obviously includes members' payments both over AND within 2 years and this is causing me the problem.

    I need all the records of payments for members who this applies to to be included in the 1st query - Within 2 Yrs. I think the query is only picking up the payments which meet the criteria <Date()-730. Can you help with the correct syntax to do this ?

  9. #9
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    in your SQL end with:
    HAVING max(paymentDate)<date()-730

  10. #10
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    I've added this and have been trying to resolve the "missing operator" syntax error unsuccessfully ! My SQL statement now looks like this:

    SELECT MEMBERS.*
    FROM MEMBERS INNER JOIN [SUBSCRIPTION PAYMENTS] ON MEMBERS.[MEMBER NUMBER] = [SUBSCRIPTION PAYMENTS].[MEMBER NO]
    WHERE ((([MEMBERS]![MEMBER NUMBER]) Not In ([qtyPaymentWithin2Yrs]![MEMBER NUMBER])) AND (([SUBSCRIPTION PAYMENTS]![PAID ON])HAVING max(PAID ON)<Date()-730));

    What's missing ?

  11. #11
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    looking at the first part of your WHERE clause, i think it should be [MEMBERS].[MEMBER NUMBER] as i believe MEMBERS is a table. also, wrap "Paid ON" with [].

    Those are two im noticing off the top of my head. change those and see if you get the same (or different) errors. Then, what I would recommend to make sure its not a logic issue, is to turn all the queries you reference into MakeTable queries. then replace the query reference with the table reference. if that solves the issue. then you know its a syntax error. if not, then theres something wrong with the logic of perhaps this query or how it is meshing with the rest of the project.

  12. #12
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    Thanks, I'll try it and get back to you. TheShabz never sleeps, eh ???!

  13. #13
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I get email notifications onto my phone. I am always near a computer. I am always willing to provide what little knowledge I do have. Besides, I can sleep when I'm dead. Why waste life sleeping?

  14. #14
    brooke48 is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    21
    I can't switch from SQL view to Design View to convert the Over 2 Yrs query to a MakeTable query without deleting the HAVING clause which has generated the error - "Syntax error (missing operator) in query expression 'max(PAID ON)<Date()-730'. " This would defeat the object ! Does the HAVING clause need to be referenced somewhere else in the SELECT query ? The SQL now reads:

    SELECT MEMBERS.*
    FROM MEMBERS INNER JOIN [SUBSCRIPTION PAYMENTS] ON [MEMBERS].[MEMBER NUMBER]=[SUBSCRIPTION PAYMENTS].[MEMBER NO]
    WHERE ((([MEMBERS].[MEMBER NUMBER]) Not In ([qtyPaymentWithin2Yrs]![MEMBER NUMBER])) AND (([SUBSCRIPTION PAYMENTS].[PAID ON])<Date()-730)) HAVING max(PAID ON)<Date()-730

  15. #15
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    the HAVING max(PAID ON) should have the [] around PAID ON as well.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-27-2010, 01:25 PM
  2. Replies: 3
    Last Post: 03-25-2010, 12:31 PM
  3. Date Criteria
    By tcollins02 in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 08:27 AM
  4. Query Criteria
    By jena in forum Queries
    Replies: 1
    Last Post: 04-29-2008, 11:00 AM
  5. Counting records based on specified criteria
    By GrnISSO in forum Queries
    Replies: 2
    Last Post: 10-04-2007, 03:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums