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.