Help with Query

[Elizabeth]

I need help in determining why I'm getting duplicate labor
and travel costs in my query. For instance, if a service
order has one or more different parts and there is also
labor and travel cost - the labor and travel cost are
listed again each time the parts are listed. E.g.

$330.00 $0.00 1 $325.13 800- 0410- 01 Digital PCB Program/

$330.00 $0.00 1 $227.76 203005- 550 Top Enc. Assembly with

The entry should look like:
Labor Travel Qty Parts
$330.00 $00.00 1 $56.00
$000.00 $00.00 2 $156.00

 

[John Vinson]

I need help in determining why I'm getting duplicate labor
and travel costs in my query. For instance, if a service
order has one or more different parts and there is also
labor and travel cost - the labor and travel cost are
listed again each time the parts are listed. E.g.

$330.00 $0.00 1 $325.13 800- 0410- 01 Digital PCB Program/

$330.00 $0.00 1 $227.76 203005- 550 Top Enc. Assembly with

The entry should look like:
Labor Travel Qty Parts
$330.00 $00.00 1 $56.00
$000.00 $00.00 2 $156.00

Please open your Query in SQL view and post the SQL text here.

I'm guessing that you have a main table joined both to the Labor costs
table, and also to the Travel costs table. Since there is no
relationship between these latter two tables, you will indeed get
duplication - every Labor cost will be linked to every Travel cost and
vice versa.

I'd suggest either using a Report with two Subreports and calculating
the totals there, or creating two Totals queries summing the Labor
costs and the Travel costs (and perhaps a third summing the Parts
costs), and join these three queries - which will have one record per
service order - to your service orders table.

John W. Vinson[MVP]

 

[Ken Hudson]

Hi Elizabeth,
I don't think that we have enough information to assist you. What is your
table structure? Do you have only one table? What are the table field names?
What is your primary key field(s)?

 

[Elizabeth]

-----Original Message-----


Please open your Query in SQL view and post the SQL text

here.
SELECT DISTINCT [qryOrders_Labor_Travel].[List name],
[qryOrders_Labor_Travel].[City], [qryOrders_Labor_Travel].
[State], [qryOrders_Labor_Travel].[Order],
[qryOrders_Labor_Travel].[Caller Name],
[qryOrders_Labor_Travel].[Clarify Number],
[qryOrders_Labor_Travel].[Ship To List name],
[qryOrders_Labor_Travel].[Ship to City],
[qryOrders_Labor_Travel].[Ship to State],
[qryOrders_Labor_Travel].[Created on],
[qryOrders_Labor_Travel].[Type], [qryOrders_Labor_Travel].
[Mn Wk Ctr], [qryOrders_Labor_Travel].[Material],
[qryOrders_Labor_Travel].[User status],
[qryOrders_Labor_Travel].[Description],
[qryOrders_Labor_Travel].[Equipment1],
[qryOrders_Labor_Travel].[Sales doc],
[qryOrders_Labor_Travel].[Purchase Order],
[qryOrders_Labor_Travel].[Equipment description],
[qryOrders_Labor_Travel].[Product No],
[qryOrders_Labor_Travel].[Product Serial],
[qryOrders_Labor_Travel].[SumOfRepair Cost],
[qryOrders_Labor_Travel].[SumOfTravel Cost], [CSP_Parts].
[Quantity], [CSP_Parts].[Cust Cost]
FROM qryOrders_Labor_Travel LEFT JOIN CSP_Parts ON
[qryOrders_Labor_Travel].[Order]=[CSP_Parts].[Order]
GROUP BY [qryOrders_Labor_Travel].[List name],
[qryOrders_Labor_Travel].[City], [qryOrders_Labor_Travel].
[State], [qryOrders_Labor_Travel].[Order],
[qryOrders_Labor_Travel].[Caller Name],
[qryOrders_Labor_Travel].[Clarify Number],
[qryOrders_Labor_Travel].[Ship To List name],
[qryOrders_Labor_Travel].[Ship to City],
[qryOrders_Labor_Travel].[Ship to State],
[qryOrders_Labor_Travel].[Created on],
[qryOrders_Labor_Travel].[Type], [qryOrders_Labor_Travel].
[Mn Wk Ctr], [qryOrders_Labor_Travel].[Material],
[qryOrders_Labor_Travel].[User status],
[qryOrders_Labor_Travel].[Description],
[qryOrders_Labor_Travel].[Equipment1],
[qryOrders_Labor_Travel].[Sales doc],
[qryOrders_Labor_Travel].[Purchase Order],
[qryOrders_Labor_Travel].[Equipment description],
[qryOrders_Labor_Travel].[Product No],
[qryOrders_Labor_Travel].[Product Serial],
[qryOrders_Labor_Travel].[SumOfRepair Cost],
[qryOrders_Labor_Travel].[SumOfTravel Cost], [CSP_Parts].
[Quantity], [CSP_Parts].[Cust Cost];