Group by function not working on certain fields

[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];

 

[Jeff Boyce]

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]

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];

 

[Duane Hookom]

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

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];

 

[Michel Walsh]

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

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

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 Moore]

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...

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

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 Moore]

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....

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...

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

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];

 

[Jeff Boyce]

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

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....

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...

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];