Inner join on numeric values

[Bill Murphy]

I have a simple join between two tables which is for some reason not
returning all the expected rows. Both tables have a double numeric field
named Amount, and I'm trying to join them on this one field. One row is
returned, but I expected at least three since I can visually see two
additional matching numeric amounts in each table that should have joined.
tblDetail is located in my backend mdb and tblGroups is located in my app.
Here's the query:

SELECT tblDetail.Amount
FROM tblDetail INNER JOIN tblGroups ON tblDetail.Amount = tblGroups.Amount;

I've tried recreating tblGroups from scratch thinking it might be corrupted,
but get the same results described above. I've also decompiled my app and
compacted and repaired the backend mdb file, but same results.

Any thoughts will be appreciated.

Bill

 

[John W. Vinson/MVP]

I have a simple join between two tables which is for some reason not
returning all the expected rows. Both tables have a double numeric field
named Amount, and I'm trying to join them on this one field. One row is
returned, but I expected at least three since I can visually see two
additional matching numeric amounts in each table that should have joined.

A Double is stored as a binary fraction with a binary mantissa (scaling
factor); as such it is an *APPROXIMATION*, accurate to some 14 decimals but
nonetheless not exact. Depending on how the numbers were generated, you
might have two values which look the same (to two decimals say) but are in
fact different in the thirteenth or fourteenth decimal place. They ARE
different and won't join.

If the information in these fields refers to money, or if you can get by
with exactly four (no more and no fewer) decimals, I'd use a Currency
datatype; if not, and you're using AccessXP or later, consider using a
Decimal datatype. I would not recommend ever using Float or Double for a
primary key or a join field for exactly the reason you describe.

 

[Bill Murphy]

John,

Thanks for your help. To work around this I created a formatted text field
for each of these double fields, and used these formatted fields for the
join. This worked just fine.

Bill