Records Quadrupling in Query - TableDesign Prob? Pls Help

Where does tbl_MIGWorkorder come from? Or did I miss something?

"Ross" wrote:

Hi,
I posted this to the querydesign group and was told I may have a
design problem, so I'm posting it here in hope that someone can help.
I am trying to create a recordset from a query to display customer
workorders, parts used, the employee who serviced the account and
amounts to be billed. The results are to be formatted as per customer
request and exported to an Excel spreadsheet.

Here are the tables and fields I am using to build this query:

tbl_Customers
tbl_Customers.FirstName
tbl_Customers.LastName
tbl_Customers.HomePhone

tbl_Employees
tbl_Employees.FirstName
tbl_Employees.LastName

tbl_Workorders
tbl_Workorders.WorkorderID - primary key
tbl_Workorders.AccountNumber
tbl_Workorders.InstallDate

tbl_WO_Parts
tbl_WO_Parts.PartID
tbl_WO_Parts.SerialNumber

tbl_WO_ClaimAmt
tbl_WO_ClaimAmt.ClaimCode
tbl_WO_ClaimAmt.RequestType
tbl_WO_ClaimAmt.ClaimAmount

When I create the query using both tbl_WO_ClaimAmt and tbl_WO_Parts,
the records returned appear to have quadrupled for each customer.
Running the query with ONLY tbl_WO_Parts and the other tables(omitting
tbl_WO_Claim) returns the correct amount of records for each customer.
Running the query with ONLY tbl_WO_Claim and the other tables(omitting
tbl_WO_Parts) returns the correct amount of records for each customer.
As soon as I add both of these tables to the query is when the record
duplication occurs.

Here is the SQL, I hope it will be helpful:

SELECT DISTINCTROW tbl_WorkorderParts.PartID,
tbl_WorkorderParts.SerialNumber, tbl_Customers.[First Name],
tbl_Customers.[Last Name], tbl_Customers.[Home Phone],
tbl_MIGWorkorder.[Install Date], tbl_Employees.FirstName,
tbl_Employees.LastName, tbl_MIGWorkorder.AccountNum,
tbl_WorkOrderClaimAmt.ClaimCode, tbl_WorkOrderClaimAmt.RequestType,
tbl_WorkOrderClaimAmt.ClaimAmount
FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN
tbl_MIGWorkorder ON tbl_Customers.CustomerID =
tbl_MIGWorkorder.CustomerID) LEFT JOIN tbl_WorkOrderClaimAmt ON
tbl_MIGWorkorder.WorkOrderID = tbl_WorkOrderClaimAmt.WorkOrderID) ON
tbl_Employees.EmployeeID = tbl_MIGWorkorder.EmployeerID) INNER JOIN
tbl_WorkorderParts ON tbl_MIGWorkorder.WorkOrderID =
tbl_WorkorderParts.WorkorderID;

I'm not sure if the order I've added the fields makes a difference
here, but this is how the client wants the data to appear.

Each workorder only has 1 CustomerID and 1 WorkorderID, but as you can
imagine many different parts and serial numbers and many different
claim codes and prices can be associated to 1 customer.
So we might have something like:

WorkOrderID
CustomerName
AccountNumber
PartID1
PartID2
PartID3
SerialNum1
SerialNum2
SerialNum3
ClaimCode1
ClaimCode2
ClaimCode3
ClaimAmount1
ClaimAmount2
ClaimAmount3

As noted earlier omitting either the tbl_Parts or tbl_ClaimAmt from
the query produces the correct results. But as soon as both
tables.fields are used the records quadruple. I'v also tried creating
the left joins in seperate queries and using those to generate the
records I need, this also produces the duplicate records.

Any help would be greatly appreciated.

Best Regards,
Ross



--
No good deed will go unpunished.