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

[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;

[TinaCa]

You might be over complicating this a bit. First do a new query and select both tables.
Do a join from tblOrders to tblOrderDetails taking all from tblOrders and all that match from TblOrderDetails (i.e. you should get an arrow going from tblOrder to tblOrderDetails). Then select the fields you need. (when you view the query the SalesTax info will repeat but we'll take care of that in the report).
Save your query.
Use the report wizard and select the query.
After selecting the query and clicking next it will ask.... how do you want to see your report. Select tblOrders. Finish the wizard.
When you view the report you should see Order number and sales tax at the top and section below will show the line items from tblOrdersDetails and then go to the next orderno and show the same thing. You can page break in between if you need them on separate pages like on an invoice or something

[grippy]

You might be over complicating this a bit. First do a new query and select both tables.
Do a join from tblOrders to tblOrderDetails taking all from tblOrders and all that match from TblOrderDetails (i.e. you should get an arrow going from tblOrder to tblOrderDetails). Then select the fields you need. (when you view the query the SalesTax info will repeat but we'll take care of that in the report).
Save your query.
Use the report wizard and select the query.
After selecting the query and clicking next it will ask.... how do you want to see your report. Select tblOrders. Finish the wizard.
When you view the report you should see Order number and sales tax at the top and section below will show the line items from tblOrdersDetails and then go to the next orderno and show the same thing. You can page break in between if you need them on separate pages like on an invoice or something

Hello, thanks for the quick response. Im sure I was putting more effort into it than I needed. My version of access is 2003 so its a bit old. When I create a new query it doesnt have any joining options, the first step is to just select all the fields I need from whatever table. So I went to both tables from the drop down and selected the fields needed and clicked next. After that the only other option in the wizard was detailed or summary so I just did the default of detailed. I opened my query and so far its the same ole repeated information (as expected). When I go into design view it does list both tables with the relationship line going from one to the other. Let me know what you think I should do for the next step. Thank you.

[TinaCa]

Right Click on the Order number field in the first table, it should give you options from there. You've have been able to this by just clicking and dragging between the two tables since version 2.0.