Joint conditions

[Ben]

Hi all,

I have a two tables where I use a six joint condition to do a left
joint, but for some reason, I still get back records on the left side
with the right side return null.

When I pick on of these records on the left side and drill down to the
table for the records on the right side, the records are there. The
joint condition uses 3 text fields, 1 date field and 2 double fields.

I am beginning to agree with my co-workers that Access is very "flaky".
Can you share some thoughts perhaps I am overlooking?

Thanks in advance,

Ben

 

[Kardan via AccessMonster.com]

Hi Ben

If you require only records where there is data on BOTH SIDES of the join,
then you need to use an INNER JOIN and not a left join.

Left and right joins are only used where you want to return all the records
from one of the tables regardless of whether there is a matching record in
the other.

Do not listen to your colleagues who say that Access is 'flaky' as this is
simply not true. Access is a good RAD Development database with lots of
tools to help the beginner. If you are unsure of your SQL, use the design
view or the wizards to help create them for you.

 

[Kardan via AccessMonster.com]

Ben

I have just re-read you OP and may have jumped in too quick with my last
reply.

Are you sure that the records you think should match actually do?

What I mean by this is that date fields do not store just dd/mm/yyyy, but
also hh:mm:ss regardless of the dislpay mode. So if the time of the records
does not match exactly as well as the date, no match will be made. Also you
are matching on double fields which store numbers to a very large number of
decimal places, again regardless of the display and so may not actually match.


A way to solve this could be to store the data in the matching fields as text
strings to ensure the level of accuracy is that which you require for
matching. Then when you need to perform calculation etc on the contents you
could convert the strings using CDate to convert the date field and CDbl to
convert the double fields.

This solution would increase the size of the data and the processing time for
queries that need to perform calculations on the values, but unless the
volumes of data are very large, this should not be very significant.