Results 1 to 11 of 11
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Query that sum values

    Dear Gents ,
    I have a table shown in the below pic.
    Click image for larger version. 

Name:	111.JPG 
Views:	18 
Size:	27.0 KB 
ID:	35567


    I need to have a combo box that filter how many expired dates with quantities more than zero
    The work flow is the following :
    item 001 have 2 quantity with expired date 20/9/2018 - Input field means in or out ( In is True Out is false )
    item 001 have 1 quantity Input In with expired date 21/9/2018
    item 001 have 2 quantity input OUT with expired date 20/9/2018



    So now , item 001 have no quantity with expired date 20/9/2018

    I need to show in the combo box only value 21/9/2018

    Forgive me for my bad english

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe:

    SELECT ItemID, ExpiredDate, Sum([Qty]*IIf([Input],1,-1)) AS SumQty
    FROM Table1
    GROUP BY ItemID, ExpiredDate
    HAVING (((Sum([Qty]*IIf([Input],1,-1)))<>0));

    And on form, if you want conditional on the ItemID for current record put SQL statement directly in combobox RowSource

    SELECT ItemID, ExpiredDate, Sum([Qty]*IIf([Input],1,-1)) AS SumQty
    FROM Table1
    WHERE ItemID = [ItemID]
    GROUP BY ItemID, ExpiredDate
    HAVING (((Sum([Qty]*IIf([Input],1,-1)))<>0));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Thanks alot !!! That's exactly what i was looking for !!.

  4. #4
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Now i need one more thing instead of opening new thread .. I need onload event give me an alert what will be expired soon .. and let's assume soon is next month .

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe:

    SELECT * FROM table1 WHERE ExpiredDate BETWEEN Date() AND DateAdd("m", 1, Date());
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    I am so sorry for having one more request , I meant what will be expired soon and the count of these products not zero .. Because i do what you wrote and it selected the expired date soon as requested , But it selects all the expired dates with all quantities .

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, try adding that Date criteria into the aggregate query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Actually I need them onload event shows msgbox that counts how many items are going to be expired next month but exclude zero counts .

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need code (VBA or macro).

    Either build a query object with the suggested SQL and do a DCount() on that query or open Recordset object with that SQL statement and use its RecordCount property. Attempt code and when you encounter specific issue, post question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    That's what i have tried to do

    Code:
    Dim CountRecords As Integer
    CountRecords = DCount("ItemID", "MainStore", "ExpiredDate BETWEEN Date() AND " & DateAdd("m", 1, Date) & " And Sum([Qty]*IIf([Input],1,-1))<>0")
    MsgBox "You are having " & CountRecords & " Items will be expired in a month "

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And what happens - error message, wrong result, nothing?

    Is MainStore table or query? I suggest you build query and reference that in DCount. Don't do the Sum() calc in the DCount. Do it in the query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Macro Loop values in Form ang get values to query
    By mauryc1990 in forum Programming
    Replies: 13
    Last Post: 12-22-2017, 08:30 AM
  2. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  3. Change Table Index Values Based on query Values
    By thuzkee02 in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2015, 11:45 PM
  4. Replies: 15
    Last Post: 10-18-2015, 04:05 PM
  5. Replies: 2
    Last Post: 11-12-2012, 03:52 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