Your problem is with your concept of how the Group By works in a query.
This is your SQL [View -> SQL View in Query Design Mode]:
Code:
SELECT DISTINCT [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price] AS SubTotal, [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges] AS Total
FROM [Shipping Info] LEFT JOIN [SO Parts/Pricing] ON [Shipping Info].[SalesOrder#] = [SO Parts/Pricing].[SalesOrder#]
GROUP BY [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price], [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges]
HAVING ((([Shipping Info].[SalesOrder#])=[Sales Order?]));
You'll notice it says:
Code:
GROUP BY [Shipping Info].[SalesOrder#], [SO Parts/Pricing].PartOrdered, [SO Parts/Pricing].Color, [SO Parts/Pricing].ActualSF, [SO Parts/Pricing].Price, [ActualSF]*[Price], [Shipping Info].[Shipping Charges], [ActualSF]*[Price]+[Shipping Charges]
This is where the problem resides.
You have three different 'PartOrdered' items.
You told Access to Group By '[SO Parts/Pricing].PartOrdered'.
Those three parts are different - so they HAVE to be on separate rows.
Once each of those is on a separate row - all the data for each of them is ALSO on a separate row.
So . . .
Open your Query.
Click View [Top - Left].
Click SQL View.
Paste the following into your SQL View window.
Code:
SELECT DISTINCT [Shipping Info].[SalesOrder#], Sum([SO Parts/Pricing].ActualSF) AS SumOfActualSF, Sum([SO Parts/Pricing].Price) AS SumOfPrice, Sum([ActualSF]*[Price]) AS SubTotal, Sum([Shipping Info].[Shipping Charges]) AS [SumOfShipping Charges], Sum([ActualSF]*[Price]+[Shipping Charges]) AS Total
FROM [Shipping Info] LEFT JOIN [SO Parts/Pricing] ON [Shipping Info].[SalesOrder#] = [SO Parts/Pricing].[SalesOrder#]
GROUP BY [Shipping Info].[SalesOrder#]
HAVING ((([Shipping Info].[SalesOrder#])=[Sales Order?]));
Now - if you run your query, you will get one row of data with all the numeric fields Totalled.
I don't know if you really want to Sum all the numeric fields.
For instance - your three Shipping Values are now totalled to $75.00.
I'm not sure if you want to charge $25.00 * 3 for shipping . . .
You can 'Avg' the Shipping column & get $25 - which might be ok for THIS order - but what about other orders . . . will the same logic still be good?
Go back to Query Design.
Two rows under Actual_SF: ActualSF - you will see 'Sum' with a down arrow to the right of it.
If you click the down arrow - you will see the different types of 'aggregate' functions you can do with the values in the field.
Play around with it and let me know if you need more help.