Need Help on Query for Orders. 90% Done just cant figure out the last

[grippy]

Ok I havent used Access in a while to actually create something so Im trying to make a sales report. I have two tables:

First Table
dbo_tblOrders with orderNumber, salesTaxRate, orderShipping, orderTotal, orderDate, shipFirstName and shipLastName

Second Table
dbo_tblOrderDetails with orderNumber, quantity, salePrice

Then in my Query i have to fields I made one which is SubTotal (SUM([quanity] * [salePrice]) from another query and TaxedSubtotal (Sum([salesTaxRate]*[SubTotal])

Now I open the report I made in the wizard and it prompts me for the begin date and end date for the query range and displays all the data fine except for orders that have multiple items then is just displays another row with the same order number and different info. Here is what its looking like now:

Orders Query_orderNumber, OrderDetails Query_orderNumber, orderDate, shipFirstName, shipLastName, SubTotal, Taxed Subtotal, orderShipping, orderTotal
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
26550 26550 12/1/11 Bobby McNugget 183.80 14.24 24.95 222.99
26569 26569 12/2/11 Mr Man 103.50 16.04 31.80 434.27
26569 26569 12/2/11 Mr Man 166.52 12.90 31.80 434.27

So basically it should only have one row for each order and be summarized and be displayed as:

26550 26550 12/1/11 Bobby McNugget 183.80 14.24 24.95 222.99
26569 26569 12/2/11 Mr Man 270.02 28.94 31.80 434.27

Let me know how I can fix my query. Thank you for the help. Here is my current SQL code on my query:

SELECT [Sales Journal Orders Query].orderDate, [Sales Journal Orders Query].orderNumber AS [Sales Journal Orders Query_orderNumber], [Sales Journal Orders Query].shipFirstName, [Sales Journal Orders Query].shipLastName, [Sales Journal Orders Query].salesTaxRate, [Sales Journal Orders Query].orderShipping, [Sales Journal Orders Query].orderTotal, [Sales Journal OrderDetails Query].orderNumber AS [Sales Journal OrderDetails Query_orderNumber], [Sales Journal OrderDetails Query].SubTotal, Sum([salesTaxRate]*[SubTotal]) AS [Taxed Subtotal]
FROM [Sales Journal Orders Query] INNER JOIN [Sales Journal OrderDetails Query] ON [Sales Journal Orders Query].orderNumber = [Sales Journal OrderDetails Query].orderNumber
GROUP BY [Sales Journal Orders Query].orderDate, [Sales Journal Orders Query].orderNumber, [Sales Journal Orders Query].shipFirstName, [Sales Journal Orders Query].shipLastName, [Sales Journal Orders Query].salesTaxRate, [Sales Journal Orders Query].orderShipping, [Sales Journal Orders Query].orderTotal, [Sales Journal OrderDetails Query].orderNumber, [Sales Journal OrderDetails Query].SubTotal;

 

[dhookom]

I would either remove or Sum the column [Sales Journal OrderDetails Query].SubTotal.

You can also use a non-grouped query and use the OrderNumber group header or footer to display the data with no detail section.

Duane
Hook'D on Access
MS Access MVP