distinct (unique values) is not working

[shira]

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

[bobh]

Your Select statement is referencing a 'Field4' not 'filed1', so
what's in field4?
bobh.

On Jun 18, 3:10 pm, shira <[e-mail]:

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

[shira]

Thanks for catching that, but it was just a typo when I wrote this
message. It is actually Field4 in both the table and query.

On Jun 18, 1:52 pm, bobh <[e-mail]:

Your Select statement is referencing a 'Field4' not 'filed1', so
what's in field4?
bobh.

On Jun 18, 3:10 pm, shira <[e-mail]:

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

[salad]

shira wrote:

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

Are you sure the numbers are unique? Is the number displayed is
formatted to display 1 decimal point? IOW, could record 1 have 231.2 as
the value and the other be 231.201...and if the display is 1 decimal
then you'd get both rec's. Just guessing.

[Allen Browne]

Although both fields look the same, internally they will be slightly
different values.

The Double has the rounding errors that are always associated with floating
point numbers. For an introduction to this issue, see:
http://en.wikipedia.org/wiki/Floatin...uracy_problems

If you will never need more than 4 decimal places, an alternative would be
to use a Currency field instead of a Double. (You can set the Format
property of the field so it doesn't display as money.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"shira" <sh******@gmail.comwrote in message
news:11********************@d30g2000prg.googlegrou ps.com...

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

[shira]

Thanks very much Allen. I will look into this. I'm just really
surprised that a number as seemingly simple as 231.2 is exhibiting
this problem. It only has 1 digit after the decimal.

The reason it was such a problem is that we were trying to find
duplicate records in the database (they were entered by mistake) and
were relying on this field to help match the duplicates. Since Access
couldn't see them as the same value, we were stuck. I then used the
round function using 5 decimal places (because some of my numbers have
more digits after the decimal than 231.2) and it seemed to then
identify same values as the same. I'm pleased to have a solution, but
I'm just wary that this issue may crop up again in other ways.

Thanks again.

On Jun 18, 6:56 pm, "Allen Browne" <[e-mail]:

Although both fields look the same, internally they will be slightly
different values.

The Double has the rounding errors that are always associated with floating
point numbers. For an introduction to this issue, see:
http://en.wikipedia.org/wiki/Floatin...uracy_problems

If you will never need more than 4 decimal places, an alternative would be
to use a Currency field instead of a Double. (You can set the Format
property of the field so it doesn't display as money.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"shira" <[e-mail] in message

news:11********************@d30g2000prg.googlegrou ps.com...

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.

[Larry Linson]

You might want to see what you can Google on how computers store Floating
Point numbers... it's a "computer feature", not an "Access problem". It is
a case of "what you see isn't what you get."

Larry Linson
Microsoft Access MVP

"shira" <sh******@gmail.comwrote in message
news:11*********************@i38g2000prf.googlegro ups.com...

Thanks very much Allen. I will look into this. I'm just really
surprised that a number as seemingly simple as 231.2 is exhibiting
this problem. It only has 1 digit after the decimal.

The reason it was such a problem is that we were trying to find
duplicate records in the database (they were entered by mistake) and
were relying on this field to help match the duplicates. Since Access
couldn't see them as the same value, we were stuck. I then used the
round function using 5 decimal places (because some of my numbers have
more digits after the decimal than 231.2) and it seemed to then
identify same values as the same. I'm pleased to have a solution, but
I'm just wary that this issue may crop up again in other ways.

Thanks again.

On Jun 18, 6:56 pm, "Allen Browne" <[e-mail]:

>Although both fields look the same, internally they will be slightly
different values.

The Double has the rounding errors that are always associated with
floating
point numbers. For an introduction to this issue, see:
http://en.wikipedia.org/wiki/Floatin...uracy_problems

If you will never need more than 4 decimal places, an alternative would
be
to use a Currency field instead of a Double. (You can set the Format
property of the field so it doesn't display as money.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"shira" <[e-mail] in message

news:11********************@d30g2000prg.googlegro ups.com...

Hi,

Looking to see if someone might have an explanation for this behavior.
Is it a bug? Corruption?

I have been able to reproduce the problem with only 2 rows and 1
field. Here is the table:

Field1
231.2
231.2

where Field1 is a double.

When I run a query to select this field from the table and set "Unique
Values" to YES, it still gives me these two rows. It should give me
only one row because they're not unique. Here is the query:

SELECT DISTINCT Table_1field.Field4
FROM Table_1field;

Just to rule out that there might be hidden characters or something
like that making the two rows distinct, I copied and pasted the value
in one row to the other so they should be identical. But the query is
still returning both rows when "Unique Values" is set to YES.

More info:

- I used the Compact/Repair tool on the database and the problem
didn't go away.
- I imported the table into a brand new database. The problem didn't
go away.
- I copied the rows into a new table in a brand new database. The
problem DID go away.

Any ideas why this might be happening? This is a huge database and I'm
concerned this issue could be creating serious problems in my query
results. Would the best solution? Create a new database? And copy all
the data into from each table into a new table? Yikes. Lots of work!

Your ideas are appreciated.