Results 1 to 3 of 3
  1. #1
    Ultimateneo is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    19

    Using Sum in a query

    Hello, I Am using this SQL query.
    Code:
    SELECT DataT.TNo, DataT.SensorSN, DataT.TestedDate, DataT.Pass, DataT.Initial, DataT.Readings
    FROM DataT
    WHERE (((DataT.TestedDate) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date())+1,0)))
    GROUP BY DataT.TNo, DataT.SensorSN, DataT.TestedDate, DataT.Pass, DataT.Initial, DataT.Readings
    HAVING (((Sum(DataT.Pass))=False))
    ORDER BY DataT.TNo;
    However, when i run the query it will show the 1st instance of a fail regardless of a pass. Pass is -1 and 0 is false

    How i want it work, is i have multiple runs of the same unit i run it until i get a pass in most instances i never get a pass and that is what i want to show. If a unit never passes then show the details within the last 2 months.

    Any help would be much appreciated.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Remove the having clause, and inspect your data, I suspect you aren't grouping the data correctly.

    Try adding the sum to the initial select.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use the query designer. It wont get the syntax wrong.

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

Tags for this Thread

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