Joins on number (double, fixed, 2) fields produce inconstant results

[Andi]

I have two tables that contain a REQ_ID column with the
format of Double, Fixed width with 2 decimals (ie 155.05).

TABLE1:
REQ_ID
REQ_TEXT

TABLE2:
REQ_ID
TEST_CASE

My sql view looks like this...

SELECT TABLE1.[Req_Id], TABLE2.*
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.[Req_Id] =
TABLE2.Req_ID;

When I create a query (see above) that joins the tables
together using the REQ_ID it retrieves most of the data,
however, there are certain records where the join never
works. I've checked and double checked and triple checked
that the REQ_IDs are identical and yet the join doesn't
work for those particular records.

I've also noticed this same type of error using the Excel
VLOOKUP function. If I try to look up the REQ_TEXT where
the REQ_ID from TABLE2 = REQ_ID from TABLE1, it returns
#N/A. I have looked closely at the values and at the
datatypes and they are exactly the same, soooooo... there
must be a bug in Microsoft. Is there a work around?

Thanks for your help!!

-Andi

 

[Allen Browne]

Hi Andi

Even though the numbers are displayed as identical, there is a very high
probably that they are not exactly the same, i.e. there are rounding errors
preventing the results you expect.

That problem is inherent in floating point numbers such as Single and
Double, making them unsuitable as key fields.

If you need no more than 4 decimal places, you could solve the problem by
using a Currency field instead, and formatting to display as Fixed, 2
places. The Currency type is a fixed point number, so it does not have the
problems of floating point numbers.

 

[Andi]

Thanks Allen. That worked! I'm a bit surprised that even
though I specified the format to be "Fixed" with a decimal
of "2" that there would be hidden numbers beyond the
second decimal. I certainly hope Microsoft is attempting
to fix this. I have run into this over the years and have
given up and used an alternate method.

Thanks again!

-----Original Message-----
Hi Andi

Even though the numbers are displayed as identical, there is a very high
probably that they are not exactly the same, i.e. there are rounding errors
preventing the results you expect.

That problem is inherent in floating point numbers such as Single and
Double, making them unsuitable as key fields.

If you need no more than 4 decimal places, you could solve the problem by
using a Currency field instead, and formatting to display as Fixed, 2
places. The Currency type is a fixed point number, so it does not have the
problems of floating point numbers.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have two tables that contain a REQ_ID column with the
format of Double, Fixed width with 2 decimals (ie 155.05).

TABLE1:
REQ_ID
REQ_TEXT

TABLE2:
REQ_ID
TEST_CASE

My sql view looks like this...

SELECT TABLE1.[Req_Id], TABLE2.*
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.[Req_Id] =
TABLE2.Req_ID;

When I create a query (see above) that joins the tables
together using the REQ_ID it retrieves most of the data,
however, there are certain records where the join never
works. I've checked and double checked and triple checked
that the REQ_IDs are identical and yet the join doesn't
work for those particular records.

I've also noticed this same type of error using the Excel
VLOOKUP function. If I try to look up the REQ_TEXT where
the REQ_ID from TABLE2 = REQ_ID from TABLE1, it returns
#N/A. I have looked closely at the values and at the
datatypes and they are exactly the same, soooooo... there
must be a bug in Microsoft. Is there a work around?

Thanks for your help!!

-Andi

.

 

[Allen Browne]

Hi Andi

Glad you have it fixed.

Although it is probably frustrating for you, it is important to understand
that:
1) The bit about "Fixed" and "2 decimal places" applies to the *display* of
the number only, and not to how it is stored.

2) The issue with floating point numbers occurs in all all computer
languages. It has nothing to do with using Access, JET or VBA, and is
therefore not something that Microsoft can fix.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. That worked! I'm a bit surprised that even
though I specified the format to be "Fixed" with a decimal
of "2" that there would be hidden numbers beyond the
second decimal. I certainly hope Microsoft is attempting
to fix this. I have run into this over the years and have
given up and used an alternate method.

Thanks again!

-----Original Message-----
Hi Andi

Even though the numbers are displayed as identical, there is a very high
probably that they are not exactly the same, i.e. there are rounding errors
preventing the results you expect.

That problem is inherent in floating point numbers such as Single and
Double, making them unsuitable as key fields.

If you need no more than 4 decimal places, you could solve the problem by
using a Currency field instead, and formatting to display as Fixed, 2
places. The Currency type is a fixed point number, so it does not have the
problems of floating point numbers.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have two tables that contain a REQ_ID column with the
format of Double, Fixed width with 2 decimals (ie 155.05).

TABLE1:
REQ_ID
REQ_TEXT

TABLE2:
REQ_ID
TEST_CASE

My sql view looks like this...

SELECT TABLE1.[Req_Id], TABLE2.*
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.[Req_Id] =
TABLE2.Req_ID;

When I create a query (see above) that joins the tables
together using the REQ_ID it retrieves most of the data,
however, there are certain records where the join never
works. I've checked and double checked and triple checked
that the REQ_IDs are identical and yet the join doesn't
work for those particular records.

I've also noticed this same type of error using the Excel
VLOOKUP function. If I try to look up the REQ_TEXT where
the REQ_ID from TABLE2 = REQ_ID from TABLE1, it returns
#N/A. I have looked closely at the values and at the
datatypes and they are exactly the same, soooooo... there
must be a bug in Microsoft. Is there a work around?

Thanks for your help!!

-Andi