By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,778 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,778 IT Pros & Developers. It's quick & easy.

Order and Order Details Query trouble

P: 10
I have created a database, using Northwind as a foundation. It is used to create purchase orders and analyse costs of various machines for a given period.
My order form is very similar to that in Northwind - it has an Order Details subform. Ihave recently added a Transport Costs field to the Order form. (Each separate Order is for one machine only). My Purchase Order report prints perfectly (akin to the Invoice report in NW). The problem lies when I try to print my Plant Costs report. This report calculates all of the items that were bought for a machine between two specified dates. It was fine until I added the Transport Costs field. Should there be four items on Order 18, say, for Machine 3, then these three products and their costs are sent to the report. Problems arise because the Transport Costs are then trebled - each Order Detail takes on the full Transport Cost. Is there any way of forcing my query to take the first instance of the Transport Costs only.

Any help is absolutely gratefully accepted!

Paul.

PS: I am just using a Design View query - just ticking the boxes. Criteria would be nice.

Thanks again.
Dec 20 '06 #1
Share this Question
Share on Google+
18 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I have created a database, using Northwind as a foundation. It is used to create purchase orders and analyse costs of various machines for a given period.
My order form is very similar to that in Northwind - it has an Order Details subform. Ihave recently added a Transport Costs field to the Order form. (Each separate Order is for one machine only). My Purchase Order report prints perfectly (akin to the Invoice report in NW). The problem lies when I try to print my Plant Costs report. This report calculates all of the items that were bought for a machine between two specified dates. It was fine until I added the Transport Costs field. Should there be four items on Order 18, say, for Machine 3, then these three products and their costs are sent to the report. Problems arise because the Transport Costs are then trebled - each Order Detail takes on the full Transport Cost. Is there any way of forcing my query to take the first instance of the Transport Costs only.

Any help is absolutely gratefully accepted!

Paul.

PS: I am just using a Design View query - just ticking the boxes. Criteria would be nice.

Thanks again.
Sorry Paul I'm afraid we'll have to see the query. In Design view can you change the view to SQL and copy and paste the code here.

Mary
Dec 21 '06 #2

P: 10
Expand|Select|Wrap|Line Numbers
  1. SELECT Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice] AS Subtotal, Orders.Transport
  2. FROM Products INNER JOIN (Plant INNER JOIN ((Orders INNER JOIN [Transport Query] ON Orders.OrderID = [Transport Query].OrderID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Plant.PlantID = Orders.PlantID) ON Products.ProductID = [Order Details].ProductID
  3. WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
  4. ORDER BY Orders.OrderDate;

Please excuse any crudeness. Thanks for all efforts and merry christmas.
Paul.
Dec 22 '06 #3

P: 10
I tried to bypass the problem by creating a Transport Query containing the Average of the Transport costs but I feel I was being a bit too clever...
Dec 22 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Assuming Orders.Transport is the transport costs field you're refering to.

Expand|Select|Wrap|Line Numbers
  1. SELECT Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice] AS Subtotal, First(Orders.Transport)
  2. FROM Products INNER JOIN (Plant INNER JOIN ((Orders INNER JOIN [Transport Query] ON Orders.OrderID = [Transport Query].OrderID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Plant.PlantID = Orders.PlantID) ON Products.ProductID = [Order Details].ProductID
  3. WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
  4. GROUP BY Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice]
  5. ORDER BY Orders.OrderDate;
  6.  
Mary
Dec 22 '06 #5

P: 10
Orders.Transport is the Transport field referred to. Unfortunately this has not fixed my problem. As my report lists the each of the Order Details of each Order, the Transport cost is added in each time.

An example of a Cost Report could be:
Plant 20 has had 3 products purchased for on Order 18 - Transport 100, and 1 product on Order 33 - Transport 50.

The main body of the report looks something like this:

Expand|Select|Wrap|Line Numbers
  1. OrderID   Product        Quantity     Price    Subtotal
  2.    18        XXXXX               2              5           10
  3.    18        YYYY               1              3            3
  4.    18        ZZZZZ               2              2            4
  5.    33        PPPP               1              5            5
  6.  
  7.                                             Transport Costs 350
  8.                                             Total                 372
My problem is that each Order Detail adds the full Transport Cost to the Total.
I need Transport Costs to be added once only for each Order.
The true Transport Costs should be 150. Therefore the true Total should be 172.

Paul.
Dec 29 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
This sounds like a grouping problem. What are the formulas being used in the following and what footers are they in

Transport Costs 350
Total 372


Mary
Jan 1 '07 #7

P: 10
The Transport Costs formula is =Sum([FirstOfTransport])
The Total is =Sum([Subtotal])+Sum([FirstOfTransport]).

They are both in the PlantID footer. I only have PlantID Header and Footer turned on.

Each Plant has its own separate report, starting on a new page.

In the Report Footer I have an Overall Total which is supposed to sum the total for each Plant and true Transport Costs to give one final figure for the boss. He doesn't want to know the rest!

By the way, is there any simple way of selecting a group of reports to print, say PlantIDs 1,4,7 and 9, instead of having to print them out separately. I have already this single PlantID printing set up.


PS
Thanks for all of your help and Happy New Year.
Jan 1 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
The Transport Costs formula is =Sum([FirstOfTransport])
The Total is =Sum([Subtotal])+Sum([FirstOfTransport]).
Not sure I understand

You said you didn't want transport costs included in the total but you've put them in the calculation
Jan 2 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok, try the following ....

change your query to this.

Expand|Select|Wrap|Line Numbers
  1. SELECT Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice] AS Subtotal, Orders.Transport
  2. FROM Products INNER JOIN (Plant INNER JOIN ((Orders INNER JOIN [Transport Query] ON Orders.OrderID = [Transport Query].OrderID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Plant.PlantID = Orders.PlantID) ON Products.ProductID = [Order Details].ProductID
  3. WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
  4. ORDER BY Orders.OrderDate;
  5.  
Then create a second group on the report for orderID with header and footer. Then come back to me.
Jan 2 '07 #10

P: 10
In order to find the total costs for the Plant, Transport must be factored in. An afterthought by the crowd who want the database, I'm afraid.

This last query does show the correct Transport Costs for each OrderID:
(In the example I test stuff on there are 4 items in Order 18 and 1 in Order 3. The report now shows Transport Costs to be 100 for Order 18 and 300 for Order 33. Normally (when I did it!) these would be 400 and 300, respectively. These values now have to be factored into the Total for the Plant, and then into the Final total (the sum of all of the Plant totals plus the sum of all of the Order Transport costs).

How now will I add the correct Transport Costs to the Plant totals?
Jan 2 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
In order to find the total costs for the Plant, Transport must be factored in. An afterthought by the crowd who want the database, I'm afraid.

This last query does show the correct Transport Costs for each OrderID:
(In the example I test stuff on there are 4 items in Order 18 and 1 in Order 3. The report now shows Transport Costs to be 100 for Order 18 and 300 for Order 33. Normally (when I did it!) these would be 400 and 300, respectively. These values now have to be factored into the Total for the Plant, and then into the Final total (the sum of all of the Plant totals plus the sum of all of the Order Transport costs).

How now will I add the correct Transport Costs to the Plant totals?
In the plant footer you should be able to total the costs in the order footer
Jan 2 '07 #12

P: 10
It's still giving the Transport total as 700. Costs are being displayed correctly using the OrderID footer but the number of items in the order are being multiplied by the transport cost when it comes to getting the Transport total.
In the OrderID footer I have [Transport].
In the PlantID footer I have =Sum([Transport]).

Is there an arithmetic function, other than Sum, that allows the average of a field to be summed? And would it work [if there is]?
Jan 2 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
It's still giving the Transport total as 700. Costs are being displayed correctly using the OrderID footer but the number of items in the order are being multiplied by the transport cost when it comes to getting the Transport total.
In the OrderID footer I have [Transport].
In the PlantID footer I have =Sum([Transport]).
In the PlantID footer you should have

=Sum([Name of Control in OrderID Footer])

Mary
Jan 3 '07 #14

P: 10
I have named the Text box in the OrderID footer "Courier", so as to avoid any ambiguity. When I ask it to =Sum([Courier]), I get prompted to enter a value for Courier. Is this what you meant by =Sum([Name of Control in OrderID Footer]), or is there a specific way to call on Controls?
Jan 3 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
I have named the Text box in the OrderID footer "Courier", so as to avoid any ambiguity. When I ask it to =Sum([Courier]), I get prompted to enter a value for Courier. Is this what you meant by =Sum([Name of Control in OrderID Footer]), or is there a specific way to call on Controls?
Open the properties window for the Courier control and under the other tab what is the value in the Name property. This is what you should use.

Mary
Jan 3 '07 #16

P: 10
The name under the Other tab is Courier. I'm still being prompted for Courier. I put in "1" as a test, and the Total Transport Costs were given as 5, though the individual costs for order 18 is 100, and for order 33 is 300. There are four items in order 18 and one in order 33.
Jan 4 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
I have named the Text box in the OrderID footer "Courier", so as to avoid any ambiguity. When I ask it to =Sum([Courier]), I get prompted to enter a value for Courier. Is this what you meant by =Sum([Name of Control in OrderID Footer]), or is there a specific way to call on Controls?
Ok, Access reports can have problems summing on calculated controls. Put another control in OrderID footer and set control source to =[Courier].

Now try summing this control instead.

Mary
Jan 5 '07 #18

P: 10
No joy. The Transport sum is still including the full transport cost for each item on the order although the costs are being displayed properly on the report.
Jan 6 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.