GROUP BY???

[Faraz A. Qureshi]

Upon creating a simple query requiring a table with following fileds:
Field1, Field2, Field3

To be grouped as follows:

SELECT Table1.Field1, Table1.Field2, Sum(Table1.Field3) AS SumOfField3
FROM Table1
GROUP BY Table1.Field1, Table1.Field2;

I have found that still duplicates are occurring and Field1 & Field2,
although grouped, appear more than once.

What might be the reason? and the way out???

 

[Tom Wickerath]

Hi Faraz,

Try simplifying your query to see if you can locate the problem. As a first
step, I'd try individual grouped queries, like this:

SELECT [Field1], Count([Field1]) AS NumOccurances
FROM [Table1]
GROUP BY [Field1]
ORDER BY [Field1]

SELECT [Field2], Count([Field2]) AS NumOccurances
FROM [Table1]
GROUP BY [Field2]
ORDER BY [Field2]

SELECT [Field3], Count([Field3]) AS NumOccurances
FROM [Table1]
GROUP BY [Field3]
ORDER BY [Field3]

Note: I added the square brackets to accomodate any special characters
(spaces, etc.) or reserved words.

Do any of the resulting queries appear to have duplicate records? If you
imported the data from another source, you may very well have some trailing
blanks that are not visible. You can use the built-in Trim functions to help
fix this type of anomally.

Once you get the above three queries returning properly grouped data, then
try increasing the complexity of the query:

SELECT [Field1], Count([Field1]) AS Num1Occurances,
[Field2], Count([Field2]) AS Num2Occurances
FROM [Table1]
GROUP BY [Field1], [Field2]
ORDER BY [Field1], [Field2]


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Allen Browne]

Tom has given you a good answer

I also answered where you posted the same q. in another group. Please don't
do that, as it fragments the discussion (wasting all our time.)