If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Group by function not working on certain fields
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]; |
#2
|
|||
|
|||
Group by function not working on certain fields
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" wrote in 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]; |
#3
|
|||
|
|||
Group by function not working on certain fields
Thanks Jeff, no luck though. Anything else I should be looking at?
"Jeff Boyce" wrote: 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" wrote in 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]; |
#4
|
|||
|
|||
Group by function not working on certain fields
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" wrote: Thanks Jeff, no luck though. Anything else I should be looking at? "Jeff Boyce" wrote: 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" wrote in 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]; |
#5
|
|||
|
|||
Group by function not working on certain fields
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" wrote in message ... 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" wrote: Thanks Jeff, no luck though. Anything else I should be looking at? "Jeff Boyce" wrote: 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" wrote in 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]; |
#6
|
|||
|
|||
Group by function not working on certain fields
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" wrote: 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" wrote: Thanks Jeff, no luck though. Anything else I should be looking at? "Jeff Boyce" wrote: 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" wrote in 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]; |
#7
|
|||
|
|||
Group by function not working on certain fields
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" wrote: 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" wrote: 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" wrote: Thanks Jeff, no luck though. Anything else I should be looking at? "Jeff Boyce" wrote: 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" wrote in 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]; |
#8
|
|||
|
|||
Group by function not working on certain fields
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" wrote in message ... 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" wrote: 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" wrote: 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" wrote: Thanks Jeff, no luck though. Anything else I should be looking at? "Jeff Boyce" wrote: 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" wrote in 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]; |
Thread Tools | |
Display Modes | |
|
|