Group by function not working on certain fields

  • Thread starter Thread starter Chris Moore
  • Start date Start date
C

Chris Moore

Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I am
able to query some fields using the Group By function and get back a list of
all the unique values in that field. However when I perform the same query on
a different field I get some duplicate values. The only field that I am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
 
Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff, no luck though. Anything else I should be looking at?

Jeff Boyce said:
Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chris Moore said:
Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I am
able to query some fields using the Group By function and get back a list
of
all the unique values in that field. However when I perform the same query
on
a different field I get some duplicate values. The only field that I am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
 
What is the data type of DupData?

How about trying

SELECT [Table1].[DupData]
FROM [Table1]
WHERE [DupData] Is Not Null
GROUP BY [Table1].[DupData]
ORDER BY [Table1].[DupData];

--
Duane Hookom
Microsoft Access MVP


Chris Moore said:
Thanks Jeff, no luck though. Anything else I should be looking at?

Jeff Boyce said:
Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chris Moore said:
Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I am
able to query some fields using the Group By function and get back a list
of
all the unique values in that field. However when I perform the same query
on
a different field I get some duplicate values. The only field that I am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
Click to expand...
 
Indeed, if the dupData is text (rather than numerical), you may get what
looks like duplicated values but which is different because of leading
spaces or other unprintable characters, or 1 (one) instead of l (lower case
L ), O instead of 0, etc. If data is floating point, two numbers may be
printed the same but differ in the 10th decimal, such as 0.499999999 and
0.500000001 will both be printed 0.5 in most cases.

Vanderghast, Access MVP


Duane Hookom said:
What is the data type of DupData?

How about trying

SELECT [Table1].[DupData]
FROM [Table1]
WHERE [DupData] Is Not Null
GROUP BY [Table1].[DupData]
ORDER BY [Table1].[DupData];

--
Duane Hookom
Microsoft Access MVP


Chris Moore said:
Thanks Jeff, no luck though. Anything else I should be looking at?

Jeff Boyce said:
Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I
am
able to query some fields using the Group By function and get back a
list
of
all the unique values in that field. However when I perform the same
query
on
a different field I get some duplicate values. The only field that I
am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
Click to expand...
 
DupData is Text. I did try the slight variation on the SELECT statement that
you provided but I'm still getting more than one row for much of the
DupData...

Duane Hookom said:
What is the data type of DupData?

How about trying

SELECT [Table1].[DupData]
FROM [Table1]
WHERE [DupData] Is Not Null
GROUP BY [Table1].[DupData]
ORDER BY [Table1].[DupData];

--
Duane Hookom
Microsoft Access MVP


Chris Moore said:
Thanks Jeff, no luck though. Anything else I should be looking at?

Jeff Boyce said:
Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I am
able to query some fields using the Group By function and get back a list
of
all the unique values in that field. However when I perform the same query
on
a different field I get some duplicate values. The only field that I am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
Click to expand...
 
Thanks for everyone's help. I now feel stupid. The DupData field is a string
of 15 numbers. In the query results the column was cutting off the last four
numbers. Once I widened the column I could see that these were in fact not
duplicate values. Half a day spent spinning my wheels on this one....

Chris Moore said:
DupData is Text. I did try the slight variation on the SELECT statement that
you provided but I'm still getting more than one row for much of the
DupData...

Duane Hookom said:
What is the data type of DupData?

How about trying

SELECT [Table1].[DupData]
FROM [Table1]
WHERE [DupData] Is Not Null
GROUP BY [Table1].[DupData]
ORDER BY [Table1].[DupData];

--
Duane Hookom
Microsoft Access MVP


Chris Moore said:
Thanks Jeff, no luck though. Anything else I should be looking at?

:

Chris

Instead of trying GroupBy, how about if you change the Unique Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized data. I am
able to query some fields using the Group By function and get back a list
of
all the unique values in that field. However when I perform the same query
on
a different field I get some duplicate values. The only field that I am
including in the query is the one I am trying to group. Any thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
Click to expand...
 
Thanks for posting back your "solution". Other folks may be able to benefit
from your pain in the future... <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chris Moore said:
Thanks for everyone's help. I now feel stupid. The DupData field is a
string
of 15 numbers. In the query results the column was cutting off the last
four
numbers. Once I widened the column I could see that these were in fact not
duplicate values. Half a day spent spinning my wheels on this one....

Chris Moore said:
DupData is Text. I did try the slight variation on the SELECT statement
that
you provided but I'm still getting more than one row for much of the
DupData...

Duane Hookom said:
What is the data type of DupData?

How about trying

SELECT [Table1].[DupData]
FROM [Table1]
WHERE [DupData] Is Not Null
GROUP BY [Table1].[DupData]
ORDER BY [Table1].[DupData];

--
Duane Hookom
Microsoft Access MVP


:

Thanks Jeff, no luck though. Anything else I should be looking at?

:

Chris

Instead of trying GroupBy, how about if you change the Unique
Values
property to Yes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
Using Access 2002 on XP Pro.

I have a table containing a lot of redundant or un-normalized
data. I am
able to query some fields using the Group By function and get
back a list
of
all the unique values in that field. However when I perform the
same query
on
a different field I get some duplicate values. The only field
that I am
including in the query is the one I am trying to group. Any
thoughts?

SQL:

SELECT [Table1].[DupData]
FROM [Table1]
GROUP BY [Table1].[DupData]
HAVING ((([Table1].[DupData]) Is Not Null))
ORDER BY [Table1].[DupData];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top