Results 1 to 5 of 5
  1. #1
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194

    Multiple levels of criteria for query returning ALL values..

    hey pplz So ill try to explain as best as possible!



    -3 tables
    [chemist]
    -ChemistName
    -Active (yes/no)

    [batch]

    [script]
    -PaymentNumber (NOT paid if NULL)


    Each chemist has multiple batches with multiple scripts.


    Anyway, I need a dropdown box to check on chemists (names) but there are 3 circumstances I need them in list...
    1. ACTIVE chemist with UNPAID scripts (paymentid is null)
    2. ACTIVE chemists with NO unpaid scripts (payment ID is not null)
    3. INACTIVE chemists with unpaid scripts.

    So what I did was connect all the tables as per their ID etc. Set the relationship to show "all chemists" even if no batch etc etc

    I can get the first line working and just show active chemists with scripts with no payments, but as soon as I add more "or" criteria underneath ALL the chemists occur!

    So I have 115 chemists with scripts that are unpaid
    and 15 chemists that are inactive with unpaid

    but when I put both criteria lines on then all 195 chemists come up!!!

    I must be doing something wrong but I just cant figure out what!


    Would a screenshot be more helpful?

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Actually, if you post your SQL statement you are using for each scenario.

  3. #3
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    ok ill do now thanks

  4. #4
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    Code:
    SELECT Chemist.ChemShortCode,
           Chemist.ChemistName,
           Chemist.ChemShortCode,
           Chemist.Active,
           Count(Scripts.ScriptID) AS CountOfScriptID
    FROM (Chemist
          LEFT JOIN ScriptBatch ON Chemist.ChemShortCode = ScriptBatch.ChemistID)
    LEFT JOIN Scripts ON ScriptBatch.ScrBatchID = Scripts.ScrBatchID
    WHERE (((Scripts.PaymentID) IS NULL)
           AND ((Scripts.InvoiceID) IS NOT NULL))
      OR (((Scripts.PaymentID) IS NULL))
    GROUP BY Chemist.ChemShortCode,
             Chemist.ChemistName,
             Chemist.ChemShortCode,
             Chemist.Active,
             Chemist.ChemistName
    HAVING (((Chemist.Active)=TRUE))
    OR (((Chemist.Active)=FALSE))
    ORDER BY Chemist.ChemistName;
    Now what is happening is too mayn chemists are there. the INACTIVE chemists are there even if they HAVE payment ID

  5. #5
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    ok so I haven't dealt much with SQL but ive just noticed that the ACTIVE is under HAVING instead of WHERE

    That could be the issue.

    But why is it doing this via the query design?

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

Similar Threads

  1. Replies: 4
    Last Post: 10-13-2015, 02:29 PM
  2. Query not returning all values
    By whitelexi in forum Queries
    Replies: 12
    Last Post: 09-06-2014, 11:40 PM
  3. Replies: 2
    Last Post: 05-16-2012, 04:45 PM
  4. Query Criteria not returning any records
    By SgtSaunders69 in forum Access
    Replies: 2
    Last Post: 12-19-2011, 07:45 PM
  5. Query not returning null values
    By janelgirl in forum Access
    Replies: 5
    Last Post: 10-11-2011, 10:31 AM

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