Calculations being multiplied

D

DawnTreader

hello all

i have a table tblwarranty that is related one to many to tblwarrantyparts.
each part in the subtable has a dollar value. there is also tables that
stores the labour hours and travel hours and the other credits.
tblwarrantylabourhours, tblwarrantytravelhours, tblwarrantyothercredits are
all on the many side of the relationship.

when i query these tables to get a line that shows the total value of the
parts, labour, travel and other credits i get a value that is multiples of
what it should be. for example if the number of part lines was 4 and the
total parts was worth $100, the query would give a result of $400.
additionally the labour, travel and other would also be multiplied.

what causes this?

here is the sql:

SELECT
IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID])
AS Claim, tblWarrantyClaim.ClaimNumber, tblWarrantyClaim.WarrantyClaimID,
subtblWarrantyStatus.Status, Year([DateofClaim]) AS DOCYear,
Format([DateofClaim],"mmm") AS DOCMonth, tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure, tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.DatePreauthorized, tblWarrantyClaim.DatePreauthorizedDone,
tblWarrantyClaim.DateSubmittedForAuthorization,
tblWarrantyClaim.DateApproved, tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
IIf([Status]="Credited",Sum(IIf(IsNull([QTY]*[UnitCreditValue]),0,[QTY]*[UnitCreditValue])),0)
AS PartsValue, Sum(IIf(IsNull([Hours]*[Rate]),0,[Hours]*[Rate])) AS
LabourValue, Sum(IIf(IsNull([THours]*[TRate]),0,[THours]*[TRate])) AS
TravelValue, Sum(IIf(IsNull([Value]),0,[Value])) AS TotalOther,
(Sum(IIf(IsNull([QTY]*[UnitCreditValue]),0,[QTY]*[UnitCreditValue])))+(Sum(IIf(IsNull([Hours]*[Rate]),0,[Hours]*[Rate])))+(Sum(IIf(IsNull([THours]*[TRate]),0,[THours]*[TRate])))+(Sum(IIf(IsNull([Value]),0,[Value])))
AS TotalWarranty, tblWarrantyClaim.CreditNumber, tblServiceReps.ServiceRepID,
tblServiceReps.Name AS ServiceRep
FROM ((tblServiceReps RIGHT JOIN (subtblWarrantyStatus INNER JOIN
((tblWarrantyClaim LEFT JOIN tblWarrantyTravelHours ON
tblWarrantyClaim.WarrantyClaimID = tblWarrantyTravelHours.WarrantyClaimID)
LEFT JOIN tblWarrantyLabourHours ON tblWarrantyClaim.WarrantyClaimID =
tblWarrantyLabourHours.WarrantyClaimID) ON subtblWarrantyStatus.StatusID =
tblWarrantyClaim.StatusID) ON tblServiceReps.ServiceRepID =
tblWarrantyClaim.ServiceRepID) LEFT JOIN tblWarrantyOtherCredits ON
tblWarrantyClaim.WarrantyClaimID = tblWarrantyOtherCredits.WarrantyClaimID)
LEFT JOIN tblWarrantyReplacedParts ON tblWarrantyClaim.WarrantyClaimID =
tblWarrantyReplacedParts.WarrantyClaimID
WHERE (((tblWarrantyClaim.WCDateDeleted) Is Null))
GROUP BY
IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]),
tblWarrantyClaim.ClaimNumber, tblWarrantyClaim.WarrantyClaimID,
subtblWarrantyStatus.Status, Year([DateofClaim]),
Format([DateofClaim],"mmm"), tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure, tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.DatePreauthorized, tblWarrantyClaim.DatePreauthorizedDone,
tblWarrantyClaim.DateSubmittedForAuthorization,
tblWarrantyClaim.DateApproved, tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated, tblWarrantyClaim.CreditNumber,
tblServiceReps.ServiceRepID, tblServiceReps.Name, subtblWarrantyStatus.Order
ORDER BY subtblWarrantyStatus.Order;
 
J

Jerry Whittle

Are all the joins on primary key / foriegn key combinations? If not you may
have introduced a cartisian product.

You have a combination of Left and Right joins. This could also cause a
cartisian product. For testing change all of them to Inner Joins and see what
happens. Probably a bunch of records will be missing, but are those that show
correct?

Also for testing, remove the Sum statement and Group By. Then sort on the
data in such a way that you might see duplicates being returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


DawnTreader said:
hello all

i have a table tblwarranty that is related one to many to tblwarrantyparts.
each part in the subtable has a dollar value. there is also tables that
stores the labour hours and travel hours and the other credits.
tblwarrantylabourhours, tblwarrantytravelhours, tblwarrantyothercredits are
all on the many side of the relationship.

when i query these tables to get a line that shows the total value of the
parts, labour, travel and other credits i get a value that is multiples of
what it should be. for example if the number of part lines was 4 and the
total parts was worth $100, the query would give a result of $400.
additionally the labour, travel and other would also be multiplied.

what causes this?

here is the sql:

SELECT
IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID])
AS Claim, tblWarrantyClaim.ClaimNumber, tblWarrantyClaim.WarrantyClaimID,
subtblWarrantyStatus.Status, Year([DateofClaim]) AS DOCYear,
Format([DateofClaim],"mmm") AS DOCMonth, tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure, tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.DatePreauthorized, tblWarrantyClaim.DatePreauthorizedDone,
tblWarrantyClaim.DateSubmittedForAuthorization,
tblWarrantyClaim.DateApproved, tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated,
IIf([Status]="Credited",Sum(IIf(IsNull([QTY]*[UnitCreditValue]),0,[QTY]*[UnitCreditValue])),0)
AS PartsValue, Sum(IIf(IsNull([Hours]*[Rate]),0,[Hours]*[Rate])) AS
LabourValue, Sum(IIf(IsNull([THours]*[TRate]),0,[THours]*[TRate])) AS
TravelValue, Sum(IIf(IsNull([Value]),0,[Value])) AS TotalOther,
(Sum(IIf(IsNull([QTY]*[UnitCreditValue]),0,[QTY]*[UnitCreditValue])))+(Sum(IIf(IsNull([Hours]*[Rate]),0,[Hours]*[Rate])))+(Sum(IIf(IsNull([THours]*[TRate]),0,[THours]*[TRate])))+(Sum(IIf(IsNull([Value]),0,[Value])))
AS TotalWarranty, tblWarrantyClaim.CreditNumber, tblServiceReps.ServiceRepID,
tblServiceReps.Name AS ServiceRep
FROM ((tblServiceReps RIGHT JOIN (subtblWarrantyStatus INNER JOIN
((tblWarrantyClaim LEFT JOIN tblWarrantyTravelHours ON
tblWarrantyClaim.WarrantyClaimID = tblWarrantyTravelHours.WarrantyClaimID)
LEFT JOIN tblWarrantyLabourHours ON tblWarrantyClaim.WarrantyClaimID =
tblWarrantyLabourHours.WarrantyClaimID) ON subtblWarrantyStatus.StatusID =
tblWarrantyClaim.StatusID) ON tblServiceReps.ServiceRepID =
tblWarrantyClaim.ServiceRepID) LEFT JOIN tblWarrantyOtherCredits ON
tblWarrantyClaim.WarrantyClaimID = tblWarrantyOtherCredits.WarrantyClaimID)
LEFT JOIN tblWarrantyReplacedParts ON tblWarrantyClaim.WarrantyClaimID =
tblWarrantyReplacedParts.WarrantyClaimID
WHERE (((tblWarrantyClaim.WCDateDeleted) Is Null))
GROUP BY
IIf([tblWarrantyClaim]![WarrantyClaimID]<7000,[tblWarrantyClaim]![ClaimNumber],[tblWarrantyClaim]![WarrantyClaimID]),
tblWarrantyClaim.ClaimNumber, tblWarrantyClaim.WarrantyClaimID,
subtblWarrantyStatus.Status, Year([DateofClaim]),
Format([DateofClaim],"mmm"), tblWarrantyClaim.DateofClaim,
tblWarrantyClaim.DateOfFailure, tblWarrantyClaim.DateOfRepair,
tblWarrantyClaim.DatePreauthorized, tblWarrantyClaim.DatePreauthorizedDone,
tblWarrantyClaim.DateSubmittedForAuthorization,
tblWarrantyClaim.DateApproved, tblWarrantyClaim.DateRejected,
tblWarrantyClaim.DateCreditCreated, tblWarrantyClaim.CreditNumber,
tblServiceReps.ServiceRepID, tblServiceReps.Name, subtblWarrantyStatus.Order
ORDER BY subtblWarrantyStatus.Order;
Click to expand...
 
Top