Order and Order Details Query trouble

[creightonp]

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.

[MMcCarthy]

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

[creightonp]

SELECT Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice] AS Subtotal, Orders.Transport
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
WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
ORDER BY Orders.OrderDate;

Please excuse any crudeness. Thanks for all efforts and merry christmas.
Paul.

[creightonp]

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...

[MMcCarthy]

Assuming Orders.Transport is the transport costs field you're refering to.

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)
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
WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
GROUP BY Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice]
ORDER BY Orders.OrderDate;
 

Mary

[creightonp]

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:

OrderID   Product        Quantity     Price    Subtotal
   18        XXXXX               2              5           10
   18        YYYY               1              3            3
   18        ZZZZZ               2              2            4
   33        PPPP               1              5            5
 
                                            Transport Costs 350
                                            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.

[MMcCarthy]

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

[creightonp]

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.

[MMcCarthy]

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

[MMcCarthy]

Ok, try the following ....

change your query to this.

SELECT Orders.OrderDate, Plant.PlantID, Plant.Make, Plant.Model, [Order Details].Quantity, Orders.OrderID, Products.ProductName, Products.UnitPrice, [Quantity]*[UnitPrice] AS Subtotal, Orders.Transport
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
WHERE (((Orders.OrderDate) Between [Start Date] And [End Date]) AND ((Plant.PlantID)=[Enter Plant ID]))
ORDER BY Orders.OrderDate;
 

Then create a second group on the report for orderID with header and footer. Then come back to me.

[creightonp]

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?

[MMcCarthy]

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

[creightonp]

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]?

[MMcCarthy]

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

[creightonp]

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?

[MMcCarthy]

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

[creightonp]

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.

[MMcCarthy]

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

[creightonp]

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.