Results 1 to 15 of 15
  1. #1
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11

    Help with query to sum multiple fields

    Hi i'm pretty new to Access and would really like some help.

    I have a stock table with stock in and out records :
    SKU stock_in stock_out date
    wab001 10 10/11/2017
    wab001 5 11/11/2017
    tab002 6 11/11/2017
    tab002 5 12/11/2017


    wab001 4 12/11/2017
    tab002 3 13/11/2017

    I would like to query this and produce the following and also call the query with a report
    SKU Stock_in Stock_out current_stock
    wab001 15 4 11
    tab002 11 3 8

    Can anyone help ?

    Thanks Kev

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I dont understand your values.
    wab001 sum = 19
    why is it:
    15, 4, 11
    what is the 11?
    why is the 4 alone?

  3. #3
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Thanks for replying so quick ,

    Its because its Stock_in and Stock_out so WAB001 has a total of 15 stock_in 10 + 5 and 4 stock_out . So current_stock is 15 - 4 , thats means the current_stock is 11

    Sorry if i'm not explaining well enough .

    Regards Kevin

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why is 5 added to 10?
    then why is 4 subtracted?

    There is nothing in the data to tell the number to add or minus.

  5. #5
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Click image for larger version. 

Name:	Screen Shot 2017-11-16 at 13.43.59.png 
Views:	17 
Size:	15.2 KB 
ID:	31296

    So the idea is that they raise a record everytime they get a delivery of WAB001
    so they have had 2 deliveries of WAB001 one of 10 items and one of 5 items on different dates
    But then they dispatch some WAB001 items 4 for delivery so the stock is reduced by the amount dispatched.
    When they stock check they can tell how much stock is left and on what dates they received stock and dispatched stock.


    I would like a query to be able to produce this Click image for larger version. 

Name:	Screen Shot 2017-11-16 at 13.47.37.png 
Views:	17 
Size:	16.8 KB 
ID:	31297

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    SELECT SKU, SUM(Nz(Stock_In,0)) AS Stock_In, SUM(Nz(Stock_Out,0)) AS Stock_Out, SUM(Nz(Stock_In,0)-Nz(Stock_Out,0)) AS Current_Stock FROM YourTable GROUP BY SKU;

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    sorry,your data was not clear. The graphic is.

  8. #8
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Thanks , i'll go test .

  9. #9
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Getting an error

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	10.1 KB 
ID:	31300


    Below is the design view of the table


    Click image for larger version. 

Name:	Capture1.PNG 
Views:	14 
Size:	9.8 KB 
ID:	31301
    Last edited by poleacre; 11-16-2017 at 01:16 PM. Reason: more information

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Rename aliases, like "AS In_Stock" / "AS Out_Stock"

    Usually using for alias same name as field name in source doesn't cause error, but probably having same named used in calculation for 3rd field did make a difference.

  11. #11
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Thanks so much for your help , i'll re test today .
    Its great to communicate with someone so helpful.
    Regards Kev

  12. #12
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    I'm still struggling , can you help ?

    Click image for larger version. 

Name:	Capture3.PNG 
Views:	9 
Size:	11.2 KB 
ID:	31321

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    SELECT SKU, SUM(Nz(Stock_In,0)) AS In_Stock, SUM(Nz(Stock_Out,0)) AS Out_Stock, SUM(Nz(Stock_In,0) - Nz(Stock_Out,0)) AS Current_Stock
    ...

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build report. Use Sorting & Grouping features. Do Sum() calcs in group footer section. Expression for Current_Stock would be like:

    =Sum([Stock-in]) - Sum([Stock-out])

    Report will allow display of detail records as well as summary calcs.
    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.

  15. #15
    poleacre is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    11
    Awesome , works perfectly , thanks so much

    Regards Kevin

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

Similar Threads

  1. Replies: 3
    Last Post: 02-20-2017, 08:27 AM
  2. Replies: 3
    Last Post: 10-08-2014, 10:28 AM
  3. Replies: 1
    Last Post: 04-12-2013, 03:03 PM
  4. Replies: 12
    Last Post: 05-07-2012, 12:41 PM
  5. Replies: 1
    Last Post: 06-28-2010, 11:04 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