Query to add multiple fields together (1 Viewer)

tmyers

Well-known member
Local time
Today, 12:16
Joined
Sep 8, 2020
Messages
1,090
I am trying to wrap my head around a query that takes like products (based off some identifying field, not the part number it self), and add everything together.

I will try and detail my thoughts here:
Product A has a quantity of (1) and a cost of $2.
Product B has a quantity of (2) and a cost of $4.

Over the life of the job, you add a handful of A's twice. So the first time you add (3) and the second time you add (1). For B, you add (3), but the cost goes up to $5.

Using that, I would want the query to show A as having a total of (5) with its cost of $2, and B would have two entries. One showing the quantity of (2) @ $4 and one showing the quantity of (3) at $5.

I have messed with using "Like" in my queries before, but nothing quite this...robust?

TLDR:
I am trying to make a query for a report to show a running total for a job. It would keep adjusting the quantities, but in the event of a cost change, would show the items quantities for each cost of the item.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:16
Joined
Oct 29, 2018
Messages
21,358
Hi. Sounds like a straight Totals query to me. Have you tried it?
 

Ranman256

Well-known member
Local time
Today, 12:16
Joined
Apr 9, 2015
Messages
4,339
use NZ() in case the field is null

=nz([qty],0) * nz([cost],0)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 19, 2002
Messages
42,970
If you don't care about sort order but just need a to-date summary, create a totals query that groups by Item and price and sums the amount

Select JobID, ItemID, ItemPrice. Sum(ItemPrice * Qty) As ExtendedPrice
From yourtable
Group by JobID, ItemID, ItemPrice

This isn't a running sum though. A running sum is done by date order.
 

tmyers

Well-known member
Local time
Today, 12:16
Joined
Sep 8, 2020
Messages
1,090
If you don't care about sort order but just need a to-date summary, create a totals query that groups by Item and price and sums the amount

Select JobID, ItemID, ItemPrice. Sum(ItemPrice * Qty) As ExtendedPrice
From yourtable
Group by JobID, ItemID, ItemPrice

This isn't a running sum though. A running sum is done by date order.
I had forgotten that a totals query could do this.
 

Users who are viewing this thread

Top Bottom