Number of records returned

[mtagliaferri]

I have the following qry that works fine returning me a 1000 more records:

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 am trying to add some date from another table but the number of records returned its halved and I don't understand why!!!
The new code is:

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, tblHotac.RoomBill
FROM ((tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip) INNER 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], tblHotac.RoomBill
ORDER BY tblTrip.TripDate;

 

[boerbende]

When using query builder, double click on the line between the last join you created.
Read carefully and select the join you want (something like: All the records from table tbltrip)
I guess you need the right join...

 

[mtagliaferri]

Thanks for the tip, I got it working now!!!!