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.Fi eld4
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.
6  9043 
Your Select statement is referencing a 'Field4' not 'filed1', so
what's in field4?
bobh.
On Jun 18, 3:10 pm, shira <shira...@gmail .comwrote:
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.Fi eld4
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.
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 <vulca...@isp.c omwrote:
Your Select statement is referencing a 'Field4' not 'filed1', so
what's in field4?
bobh.
On Jun 18, 3:10 pm, shira <shira...@gmail .comwrote:
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.Fi eld4
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 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.Fi eld4
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.
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******** ************@d3 0g2000prg.googl egroups.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.Fi eld4
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.
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" <AllenBro...@Se eSig.Invalidwro te:
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" <shira...@gmail .comwrote in message
news:11******** ************@d3 0g2000prg.googl egroups.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.Fi eld4
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.
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******** *************@i 38g2000prf.goog legroups.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" <AllenBro...@Se eSig.Invalidwro te:
>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" <shira...@gmail .comwrote in message
news:11******* *************@d 30g2000prg.goog legroups.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.Fi eld4
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Florian |
last post by:
Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.
Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:
|
by: Rich |
last post by:
My table looks like this:
char(150) HTTP_REF,
char(250) HTTP_USER,
char(150) REMOTE_ADDR,
char(150) REMOTE_HOST,
char(150) URL,
smalldatetime TIME_STAMP
There are no indexes on this table and there are only 293,658 records total.
|
by: Fred Zuckerman |
last post by:
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
Thanks,
Fred Zuckerman
|
by: Don Bowman |
last post by:
I have a table with a large number of rows (10K in the example below,
but >1M in some databases). I would like to find the distinct
values for one of the columns. The column is indexed.
I would have expected that this would be a very fast operation,
simply walking down the index. In the example below, there is
only 1 unique value, but it takes 2 seconds. I would have
expected more like ~50ms.
explain analyze select distinct element...
|
by: zaphod |
last post by:
I need to select unique combinations of 4 columns from one table and insert
them into a new table but I can't think of any way of finding unique
combinations of more than 1 column since SELECT DISTINCT only works on
single columns. Any ideas?
zaphod
| | |
by: Daniel Wetzler |
last post by:
Dear MSSQL experts,
I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .
If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
|
by: erbrose |
last post by:
Hey, I keep searching the forum, but can't exactly what I need.
I have a table (tbl_road) with tons of fields. I need to create a new table (tbl_road_distinct) with unique values of an field (road_id), as there are multiple values,. So, this works fine
CREATE TABLE tbl_road_distinct
SELECT DISTINCT (road_id)
FROM tbl_road
thats fine, but i want it to bring the rest of the attributes associated with the unique records over too (ideally the...
|
by: NeoGeo |
last post by:
I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE...
|
by: newnewbie |
last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our system and thinks I am omnipotent now and can extract any data out of it in the form he wants….The truth is, though I know SOME Access, I am not a programmer…and many queries that he wants me to do have a potential of being monstrous towers of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
