Select Query not returning the correct results

[snypa]

Hi all

I'm just having a problem with a select query that is not returning all the results that match the join conditions for two tables.

The results that are missing when one condition ie field has not value in both table though the other 2 conditions match. Is this the way access behaves and if so what is the best way to structure my query to overcome that

Thanx for the help

 

[John Big Booty]

At a guess, I'd say the query is returning exactly what you have asked it to return. It is far more likely that the query is not behaving in the manner you expect. Without seeing the data or the query it's very difficult to say exactly where the error lies, but most probably it is in the logic behind the query.

 

[John Big Booty]

Remember Access is essentially dumb it will do exactly as you ask, which is not always the same as what you expect.

 

[snypa]

SELECT tblSuccessfullIncurreds2.Group,
tblSuccessfullIncurreds2.PONumber, tblSuccessfullIncurreds2.ItemNumber, tblSuccessfullIncurreds2.CMSFacility, tblSuccessfullIncurreds2.CMSCommodity, tblSuccessfullIncurreds2.CARNumber, tblSuccessfullIncurreds2.IncurredCosts, tblSuccessfullIncurreds2.PaymentDate, tblPaymentTransactionHeader.PaymentNumber, [tblSuccessfullIncurreds2].[FlagNumber]+1 AS FlagNumber, tblSuccessfullIncurreds2.InvoiceNumber INTO tblSuccessfullIncurredsFinal

FROM tblSuccessfullIncurreds2 INNER JOIN tblPaymentTransactionHeader

ON (tblSuccessfullIncurreds2.MaxOfPaymentNumber = tblPaymentTransactionHeader.PaymentNumber) AND (tblSuccessfullIncurreds2.InvoiceNumber = tblPaymentTransactionHeader.PaymentReference) AND (tblSuccessfullIncurreds2.PONumber = tblPaymentTransactionHeader.PackageNumber);

The problem is (tblSuccessfullIncurreds2.InvoiceNumber = tblPaymentTransactionHeader.PaymentReference). If there is no Invoice Number in both tables that entry is not returned

 

[steeffie]

Remember Access is essentially dumb it will do exactly as you ask, which is not always the same as what you expect.

Access does not do exactly as you ask, at least not always. Though I agree with you saying Access is dumb.

 

[steeffie]

Try:

(Nz(tblSuccessfullIncurreds2.InvoiceNumber,0) =
Nz(tblPaymentTransactionHeader.PaymentReference,0))

 

[John Big Booty]

Access does not do exactly as you ask, at least not always. Though I agree with you saying Access is dumb.

I'm sorry I have to disagree with you there. The problem is often the gulf of understanding between what you think you have asked and what you have actually asked.

 

[steeffie]

I'm sorry I have to disagree with you there. The problem is often the gulf of understanding between what you think you have asked and what you have actually asked.

Well, maybe you can tell me what's going on here:
http://www.access-programmers.co.uk/forums/showthread.php?t=167624

It's obvious that Access is doing something wrong. Not always, but occasionaly.

 

[John Big Booty]

Ah hoisted on my own petard