Discrepancy on returned number of records

[mtagliaferri]

I have a discrepancy with the number of records returned...the original functioning qry was that returned the correct number of records was

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
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 added to the qry a further try "qryHotacBills" to extract further data and the new qry 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 AdditionalAllowances, qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+Nz([AmmountAdditionalAllowances],0) AS TOT
FROM ((tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip) LEFT JOIN qryHotacBill ON tblTrip.IDTrip = qryHotacBill.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], qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+Nz([AmmountAdditionalAllowances],0)
ORDER BY tblTrip.TripDate;

As I added the "qryHotacBill" I have extra records extracted, I researched what records are and I have discovered that in the "tblAdditionalAllowances" I have occasionally 2 or 3 records with the same date and these should be added as total under that date hence this bit of code (qryHotacBill run separately works)

Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS AdditionalAllowances

In the first qry it does sum the amounts however when I added "qryHotacBill" It no longer returns the sum of additional allowances. I have checked the joint properties and I cant see the error...

What am I getting wrong? :banghead:

 

[Ranman256]

If it doesn't get the same # records, the the 2 queries are different.
If you have joins,this can produce extra records.
You'd have to analyze the return records in each tables, and the join

 

[mtagliaferri]

The 2 qry return the correct records when run individually, but when I had qryHotacBills then I have extra records, I think the join are correct as per picture attached on previous post, Hence me being puzzled.

 

[plog]

Can you post a database of sample data?

 

[mtagliaferri]

Here it is skimmed to the essentials but will all data!

So the original qry returns 1118 records (Remove qryHotacBill from qryTrips) which is correct, but with the addition of qryHotacBill it returns 1139 records...
An example of double records are under the dates 25/08/2007, 24/09/2007, 28/03/2008 and so on..

Hope it makes sense

 

[plog]

tblAdditionalAllowances has duplicate IDTrip values. That's the issue.

When you JOIN tblTrip to it via that field it matches those duplicate records. For example you have 2 records in tblAdditionalAllowances with IDTrip=303, so when you JOIN another table to it, it produces 2 records in the query you do so.

Most likely you need to create a subquery on tblAdditionalAllowances, then use that subquery in your query instaead of the table itself.