Query Help

E

Elizabeth

I need help in determining why I'm getting a duplicate
entries for labor and travel cost if there are more one
line item on a service order. For example:

Labor Travel Qty Part $
$330.00 $199.00 2 125.30
$330.00 $199.00 1 56.79

Should be:

Labor Travel Qty Part $
$330.00 $199.00 2 $125.30
$00.00 $00.00 1 $56.79

I've included my SQL for review:

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];
 
D

Douglas J. Steele

It's because you're joining the Labor/Travel table to the Parts table.

You could try UNIONing the tables together instead, so that you'll end up
with

Part 1
Part 2
Labor
Travel

rather than

Labor Travel Part 1
Labor Travel Part 2

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Elizabeth said:
I need help in determining why I'm getting a duplicate
entries for labor and travel cost if there are more one
line item on a service order. For example:

Labor Travel Qty Part $
$330.00 $199.00 2 125.30
$330.00 $199.00 1 56.79

Should be:

Labor Travel Qty Part $
$330.00 $199.00 2 $125.30
$00.00 $00.00 1 $56.79

I've included my SQL for review:

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];
Click to expand...
 
G

Guest

Doug, thanks for the response but I'm not familiar with Unioning Tables.
Please explain how to.

Douglas J. Steele said:
It's because you're joining the Labor/Travel table to the Parts table.

You could try UNIONing the tables together instead, so that you'll end up
with

Part 1
Part 2
Labor
Travel

rather than

Labor Travel Part 1
Labor Travel Part 2

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Elizabeth said:
I need help in determining why I'm getting a duplicate
entries for labor and travel cost if there are more one
line item on a service order. For example:

Labor Travel Qty Part $
$330.00 $199.00 2 125.30
$330.00 $199.00 1 56.79

Should be:

Labor Travel Qty Part $
$330.00 $199.00 2 $125.30
$00.00 $00.00 1 $56.79

I've included my SQL for review:

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];
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Easiest way would probably be to create two separate queries.

Create one that gets the necessary fields from the Labour/Travel table. (For
the sake of argument, let's assume you name it qryLabourTravel)

Create one that gets the necessary fields from the Parts table (qryParts)

It's critical that the two queries correspond: they must have the same
number of fields and each field in the same position must be the same data
type. If one of the queries must have more fields than the other, you'll
have to pad the smaller one.

Once you've got those two queries, create a third one. This time, don't
bother dragging any tables into the query builder, but select the SQL view
(you can do this on the View menu, or choose it from the left-most button on
the button bar). In there, type something like:

SELECT * FROM qryLabourTravel
UNION
SELECT * FROM qryParts

Now, you can alter this somewhat if you want. You can list the names of the
actual fields rather than using *.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Elizabeth said:
Doug, thanks for the response but I'm not familiar with Unioning Tables.
Please explain how to.

Douglas J. Steele said:
It's because you're joining the Labor/Travel table to the Parts table.

You could try UNIONing the tables together instead, so that you'll end up
with

Part 1
Part 2
Labor
Travel

rather than

Labor Travel Part 1
Labor Travel Part 2

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Elizabeth said:
I need help in determining why I'm getting a duplicate
entries for labor and travel cost if there are more one
line item on a service order. For example:

Labor Travel Qty Part $
$330.00 $199.00 2 125.30
$330.00 $199.00 1 56.79

Should be:

Labor Travel Qty Part $
$330.00 $199.00 2 $125.30
$00.00 $00.00 1 $56.79

I've included my SQL for review:

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];
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Question 2
Help with Query 3
Query is Retrieving too few Record 1
The query retrieves too few records. 1
What the Hell is going on here? 3
How to make a module out of a query 4
Include additional fields in or from a totals qry 12
Refer to a query in qry of rpt 1

Top