problems with 'group by' result in select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I performed a select query with 3 fields, and grouped by (trying to see each unique combination of the three fields). The results don't make sense: there are duplicate entries of the same combination showing up.

ex: a 'group by' function using fields A, B, C result in 2 rows with the exact same values (A = 6, B = blank (is null), C = 0)

Any explanation as to why this is happening? FYI: The table I'm querying is the product of an append query that merged 2 tables (one originally from MS Excel, the other an MS Access table)...this may be the culprit

Thanks in advance.
 
Hi,

I performed a select query with 3 fields, and grouped by (trying to see each unique combination of the three fields). The results don't make sense: there are duplicate entries of the same combination showing up.

ex: a 'group by' function using fields A, B, C result in 2 rows with the exact same values (A = 6, B = blank (is null), C = 0).

Any explanation as to why this is happening? FYI: The table I'm querying is the product of an append query that merged 2 tables (one originally from MS Excel, the other an MS Access table)...this may be the culprit.

Thanks in advance.
Click to expand...

Please post the SQL view of the query.
 
Hi
Table = Bird93-03
Fields = B1, B2 and BRWT
SQL

SELECT [BIRD93-03].B1, [BIRD93-03].B2, [BIRD93-03].BRW
FROM [BIRD93-03
GROUP BY [BIRD93-03].B1, [BIRD93-03].B2, [BIRD93-03].BRW
HAVING ((([BIRD93-03].B1)=16) AND (([BIRD93-03].BRWT)=0))

Thanks

----- John Vinson wrote: ----

On Thu, 26 Feb 2004 16:46:05 -0800, "warbler
Hi
Click to expand...

Please post the SQL view of the query

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
 
Hi.
Table = Bird93-03.
Fields = B1, B2 and BRWT.
SQL:

SELECT [BIRD93-03].B1, [BIRD93-03].B2, [BIRD93-03].BRWT
FROM [BIRD93-03]
GROUP BY [BIRD93-03].B1, [BIRD93-03].B2, [BIRD93-03].BRWT
HAVING ((([BIRD93-03].B1)=16) AND (([BIRD93-03].BRWT)=0));
Click to expand...

Grasping at straws here but... are either B1 or BWRT Float or Double
fields? This might be roundoff error - e.g. if in one record B1 is
16.0000000000002 and in another it's 15.99999999999997 they'd group
separately but look the same.
 
Nope, that's not it - these numbers were all entered as integers (although the field is double), thus rounding isn't an issue

I think i fixed it in a roundabout way: I took the original table, made a copy of it, deleted all records, then copied and pasted the new table records into the 'shell' of the original table. This ensured the design of the two tables were identical, so when I appended, there was no problem (earlier, validation rule violations were occuring), and there are no longer duplicate records showing up in the select query with identical values. Yay

Still don't know what the problem was, but at least I have a solution
Thanks for your thoughts though, much appreciated
Theresa
 
Back
Top