Results 1 to 5 of 5
  1. #1
    harshbloke is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    2

    Tallying amounts according to specific criteria

    Hello. First-time poster here.
    I'd like to ask about using a query to tally up figures based on a specific ID.
    My company's database is used to track orders from clients and jobs outsourced to suppliers. At the moment, those are run on separate tables, and each works well enough.
    What I want to do is calculate the profitability of each specific job; i.e. subtract outsourcing fees from income received.

    Here is the premise:

    • Orders received are recorded in the Orders table, and each order has a unique ORDER ID.
    • Outsourced tasks are recorded in the Outsourcing table; each task outsourced has a unique OUTSOURCING ID, and each record is linked to the pertinent order via the ORDER ID.
    • Some orders involve no outsourcing transactions (because we do them in-house) and others involve multiple outsourcing transactions.


    I want to make a query that tallies of the outsourcing fees for each ORDER ID. For example, if in the Outsourcing table I have...

    OUTSOURCING ID
    ORDER ID


    SUPPLIER
    FEE
    OUTSRC001 ORD400 Mary McGonagle 500
    OUTSRC002 ORD401 Jiro Suzuki 125
    OUTSRC003 ORD401 Ramit Singh 200
    OUTSRC004 ORD401 Bill Jones 350
    OUTSRC005 ORD405 John Smith 24

    ... I want to make a query that returns 500 for ORD400, 675 for ORD401, and 24 for ORD405, and so on...

    I've had a look through some of the threads here and found some great info (the links to RogersAccessLibrary and Access Diva are very helpful!) but not the answer to the above question.

    I'd really appreciate any help you can offer.
    Thanks very much in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This is a simple query....
    select orderID, sum(fee) as total from table

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    SQL for query
    Code:
    SELECT Harsh.ORDERID, Sum(Harsh.FEE) AS SumOfFEE
    FROM Harsh
    GROUP BY Harsh.ORDERID;
    ORDERID SumOfFEE
    ORD400 500
    ORD401 675
    ORD405 24

    I recommend you do NOT use field names with embedded spaces.

    Good luck.

  4. #4
    harshbloke is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    2
    Thanks ranman256 and orange. Problem solved.
    Much appreciated!

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Criteria to filter a specific date.
    By RickK in forum Queries
    Replies: 2
    Last Post: 07-27-2016, 08:58 AM
  2. Flag specific criteria
    By chosenonee in forum Access
    Replies: 3
    Last Post: 02-08-2015, 01:51 AM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Tallying a listbox's items
    By lyrasphere in forum Access
    Replies: 3
    Last Post: 05-22-2014, 08:12 AM
  5. Report with a specific criteria
    By augcorv@gmail.com in forum Reports
    Replies: 3
    Last Post: 03-24-2014, 08:18 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