Results 1 to 6 of 6
  1. #1
    Pells is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    25

    Not Exists Between Query

    I am trying to write a query which finds where records dont exist between to sets of week numbers. My Access query looks like this at the moment:



    SELECT PEPERSON.PERSONNEL_NO, PEPERSON.SURNAME, PEPERSON.CENTRE, [Input Week Number] AS WEEK_NO
    FROM PEPERSON
    WHERE (((PEPERSON.CENTRE)>"0") AND ((Exists (SELECT JCBATCH.PERSONNEL_NO FROM JCBATCH WHERE JCBATCH.COMPANY = PEPERSON.COMPANY AND JCBATCH.WEEK_NO = [Input Week Number] AND JCBATCH.PERSONNEL_NO = PEPERSON.PERSONNEL_NO))=False) AND ((PEPERSON.DATE_LEFT) Is Null) AND ((PEPERSON.USE_IN_TB)=1) AND ((PEPERSON.COMPANY)="PFC1"))
    ORDER BY PEPERSON.PERSONNEL_NO;

    This works fine when I enter in the [Input Week Number] but I need to replace with this something like:

    Between "201001" and "201052" which also displays the result of the week number in the field called WEEK_NO.

    Many thanks for your time to look at my post and any help would be most appreciated.

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Can't you just add another where clause, something like;

    Code:
    WHERE (((PEPERSON.CENTRE)>"0") AND ((Exists (SELECT JCBATCH.PERSONNEL_NO 
    FROM JCBATCH WHERE JCBATCH.COMPANY = PEPERSON.COMPANY 
    AND JCBATCH.WEEK_NO = [Input Week Number]
    where [Input Week Number]between'201001'and'201052'
    .....
    
    Or do you want to add the Not Exists part to that query?
    Sorry if i've misunderstood.

  3. #3
    Pells is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    25
    Many thanks Rixxe.

    I think it needs adding to the Not Exist part to the query. Im getting a "syntax error in query expression" when I try the suggestion.

    Do you know how this can be done please?

  4. #4
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Well i didnt try it out, it was just an idea. But if you do want to add a Not Exists part to that query, id suggest either trial and error with: (It's just an idea, so you will have to find a way to make it work)

    Where Not Exists [Input Week Number]between'201001'and'201052'

    Or try using a subquery for just the [Input Week Number] part.
    (Not Exists does work in a subquery just so you know)

    Good luck

  5. #5
    Pells is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    25
    Quote Originally Posted by Rixxe View Post
    Well i didnt try it out, it was just an idea. But if you do want to add a Not Exists part to that query, id suggest either trial and error with: (It's just an idea, so you will have to find a way to make it work)

    Where Not Exists [Input Week Number]between'201001'and'201052'

    Or try using a subquery for just the [Input Week Number] part.
    (Not Exists does work in a subquery just so you know)

    Good luck
    Isnt it just possible to replace the [Input Week Number] to between "201001" and 201052" and have the results returned in a field called week_no?

    Apologies, but a complete novice with this and learning all the time.

  6. #6
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Give it a try, if it returns the correct records then it works.

    Finding records between those 2 dates (Exists)
    Finding records with no date between 2 dates (Not Exists)

    You have used:

    ((Exists (SELECT

    Which is fine as long as you know what you want!

    Good luck

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

Similar Threads

  1. Add columns if not exists
    By ysrini in forum Access
    Replies: 1
    Last Post: 02-16-2010, 06:39 PM
  2. if record exists...conditional query criteria
    By mbryanr in forum Queries
    Replies: 2
    Last Post: 02-12-2010, 11:50 AM
  3. How to use if not exists in access?
    By halifaxdalRaymondXie in forum Queries
    Replies: 1
    Last Post: 01-14-2010, 03:18 PM
  4. Replies: 1
    Last Post: 11-30-2009, 05:05 AM
  5. Create Table If Not Exists
    By jp2access in forum Queries
    Replies: 7
    Last Post: 07-14-2009, 12:49 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