Results 1 to 4 of 4
  1. #1
    HybridAK is offline Novice
    Windows 10 Access 2002
    Join Date
    May 2018
    Posts
    3

    Sum each field vs Count

    Hello. I need some help. I am trying to create a query that accesses 2 tables, Sales and Requests.



    SALES REQUESTS
    Keys Description Category NewQty InventoryKey Qty
    1 Item 1 2 1 1 1
    2 Item 2 3 0 2 2
    3 Item 3 1 1 1 2
    4 Item 4 1 1 1 1
    2 2
    3 1
    4 1
    4 1
    1 1
    2 1
    3 1
    4 1

    I want to have a sum of each quantity in the Request Table per key (Hopefully that makes sense). Then, I want to join the 2 tables and have my results look something like this:

    DESIRED RESULTS
    Description NewQty Total Requests
    Item 1 1 5
    Item 2 0 5
    Item 3 1 2
    Item 4 1 3

    I tried this with COUNT and the results were great but I need to SUM the qty from the Requests table.

    Here's my SQL using COUNT:

    SELECT Sales.Description, Sales.NewQty, COUNT(Requests.InventoryKey) AS [Total Requests]
    FROM Sales LEFT JOIN Requests ON Sales.Keys = Requests.InventoryKey
    WHERE Sales.Category <> 7 AND Sales.Category <> 38
    GROUP BY Sales.Description, Sales.NewQty, Requests.InventoryKey, Sales.Category
    ORDER BY Sales.Category, Sales.Description;

    I've tried replacing COUNT with SUM, but that just sums the whole column. It's probably something easy I'm missing, but any help would be appreciated.

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make Q1 to sum the totals in tRequests,
    select key, sum(Qty) from tRequests,

    then Q2, join it to sales,
    select tSales.*,Q1.SumOfQty from tSales, Q1 where tSales.keys= Q1.inventoryKey

  3. #3
    HybridAK is offline Novice
    Windows 10 Access 2002
    Join Date
    May 2018
    Posts
    3
    Thank you. I will try that.

  4. #4
    HybridAK is offline Novice
    Windows 10 Access 2002
    Join Date
    May 2018
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    Make Q1 to sum the totals in tRequests,
    select key, sum(Qty) from tRequests,

    then Q2, join it to sales,
    select tSales.*,Q1.SumOfQty from tSales, Q1 where tSales.keys= Q1.inventoryKey
    This works great, but now I have a new problem. How do I also include all records from tSales that may not be in tRequests? So my modified desired result:

    DESIRED RESULTS
    Description NewQty Total Requests
    Item 1 1 5
    Item 2 0 5
    Item 3 1 2
    Item 4 1 3
    Item 5 1 0

    Item 5 is in the sales table, but not in the request table.

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

Similar Threads

  1. Replies: 15
    Last Post: 02-09-2018, 08:53 PM
  2. Replies: 3
    Last Post: 09-26-2017, 11:19 PM
  3. Replies: 16
    Last Post: 01-05-2016, 04:13 PM
  4. Replies: 3
    Last Post: 01-04-2015, 03:00 PM
  5. Replies: 4
    Last Post: 07-28-2013, 12:40 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