Incorrect returned value

[mtagliaferri]

I have a fully functional query

SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS SumOfAmmountAdditionalAllowances
FROM (tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors]
ORDER BY tblTrip.TripDate;

I have added a table to the query for further data, however with new table added the value returned is double for the "Additional Allowances" files only for those records where I have a value in the new table...hope iit makes sense...

SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS AdditionalAllowances, Sum(tblHotac.RoomBill) AS [Hotel Bill]
FROM ((tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip) LEFT JOIN tblHotac ON tblTrip.IDTrip = tblHotac.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors]
ORDER BY tblTrip.TripDate;

:banghead:

 

[plog]

That's too much code to take in, but your explanation leads insight:

I have added a table to the query for further data, however with new table added the value returned is double

That new table has duplicate records for the fields you are joining it on. You either need to further define your JOIN, or make a query on that table to produce unique permutations of the fields you will be using to JOIN.