Need help on creating a total in a seperate query column

[net]

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]

Sorry, WO #s are all "WO333". I have 3 work order numbers and I am looking for a combined total of each piece

 

[plog]

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:

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]

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]

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]

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.