Need help on creating a total in a seperate query column (1 Viewer)

net

Registered User.
Local time
Today, 01:31
Joined
Mar 12, 2006
Messages
50
Hello,

I have a Microsoft Access query that has a column “Piece Price”. I would like to get a total in a separate column of the total for all of the total pieces per work order. See sample below.

Sample:

WO # Piece Price Total Price
WO3333 $200.00 $1,000.00
WO3333 $700.00 $1,000.00
WO333 $100.00 $1,000.00

Thank you for your help. :)
 

net

Registered User.
Local time
Today, 01:31
Joined
Mar 12, 2006
Messages
50
Sorry, WO #s are all "WO333". I have 3 work order numbers and I am looking for a combined total of each piece
 

plog

Banishment Pending
Local time
Today, 03:31
Joined
May 11, 2011
Messages
11,611
First, fix your field names. Never uses spaces or special characters (e.g. #). Makes coding and querying harder.

Second, build a query to get the total price per work order:

Code:
SELECT WorkOrder, SUM(PiecePrice) AS WorkOrderTotal
FROM YourTableNameHere
GROUP BY WorkOrder

Then, bring that into your query, JOIN it via the WorkOrder fields and bring WorkOrderTotal into your query.
 

net

Registered User.
Local time
Today, 01:31
Joined
Mar 12, 2006
Messages
50
Hello Plog,

Thank you so much for your suggestion. I do not know how to put my fields into a table format in these message fields. I hate the spaces myself. I apologize.

Is there anyway to add the formula into the query design view? I am not familiar with the SQL part.
 

June7

AWF VIP
Local time
Today, 00:31
Joined
Mar 9, 2014
Messages
5,423
In design view, click the Totals button on ribbon then build the aggregate query. Switch to SQL view to see the resulting statement. That's one way to learn SQL structure.
 

net

Registered User.
Local time
Today, 01:31
Joined
Mar 12, 2006
Messages
50
Thank you Plog and June7 for your help with the SQL and aggregate query. For the most part I am able to get what I want.

Thanks again. :)
 

Users who are viewing this thread

Top Bottom