Query returns incorrect sum

[Guest]

The below query is taking the number of records for the particular unit in
the MLRS Destroyed Ammo and using it as a multiplier when the query returns
the result. I.E. I have four records for destroyed ammo for a unit and one
record in the MLRS Fire Mission Log for number of rounds fired=6. The query
returns 24 rounds fired. If I delete a destroyed ammo record it returns 18.
How do I prevent my query from using the number of records in the destroyed
ammo table as a multiplier?


SELECT Units.UnitID, [MLRS Ammo Types].Nomenclature, [MLRS Starting
Ammo].[Number Starting], [MLRS Resupply Ammo].Delivered, Sum([MLRS Fire
Mission Log].NumberFired) AS SumOfNumberFired, Sum([MLRS Destroyed
Ammo].[Number Destroyed]) AS [SumOfNumber Destroyed], Sum([MLRS Transferred
Ammo].[Number Transferred]) AS [SumOfNumber Transferred], Sum([MLRS Resupply
Ammo].[Number Resupplied]) AS [SumOfNumber Resupplied]
FROM [MLRS Ammo Types] INNER JOIN (((((Units INNER JOIN [MLRS Transferred
Ammo] ON Units.UnitID = [MLRS Transferred Ammo].UnitID) INNER JOIN [MLRS
Resupply Ammo] ON ([MLRS Transferred Ammo].TransferAmmoID = [MLRS Resupply
Ammo].ResupplyAmmoID) AND (Units.UnitID = [MLRS Resupply Ammo].UnitID)) INNER
JOIN [MLRS Destroyed Ammo] ON Units.UnitID = [MLRS Destroyed Ammo].UnitID)
INNER JOIN [MLRS Fire Mission Log] ON Units.UnitID = [MLRS Fire Mission
Log].UnitID) INNER JOIN [MLRS Starting Ammo] ON Units.UnitID = [MLRS Starting
Ammo].UnitID) ON ([MLRS Ammo Types].MunitionTypeID = [MLRS Destroyed
Ammo].MunitionTypeID) AND ([MLRS Ammo Types].MunitionTypeID = [MLRS Fire
Mission Log].MunitionTypeID) AND ([MLRS Ammo Types].MunitionTypeID = [MLRS
Resupply Ammo].MunitionTypeID) AND ([MLRS Ammo Types].MunitionTypeID = [MLRS
Starting Ammo].MunitionTypeID) AND ([MLRS Ammo Types].MunitionTypeID = [MLRS
Transferred Ammo].MunitionTypeID)
GROUP BY Units.UnitID, [MLRS Ammo Types].Nomenclature, [MLRS Starting
Ammo].[Number Starting], [MLRS Resupply Ammo].Delivered
HAVING ((([MLRS Resupply Ammo].Delivered)=True));

 

[Guest]

Well I just answered my own question. Instead of one big Query, I broke it
up into 5 queries. One for Starting Ammo, One for Fired, One for Destroyed,
One for Resupplied, and one for transfered. Then I used a Query on those
five to bring it all together and then used that combining query to put it
all into a report.