Summary Query Changing Expected Field Values

[bsarro]

I have an access query that generates the following results:

Account Code	MOD_TERM
10.36	TERMINAL
20.03.12	TERMINAL
20.03.12	TERMINAL
20.03.14	TERMINAL
20.03.14	TERMINAL
20.06.02	TERMINAL
20.06.04	TERMINAL
20.06.08	TERMINAL
20.30.MOVES	INTERMEDIATE
20.30.MOVTDY	INTERMEDIATE
20.30.TDY	INTERMEDIATE
30.03.08.006	TERMINAL
30.03.10.006	TERMINAL
30.03.22	TERMINAL
30.03.94	TERMINAL
30.03.94	TERMINAL
30.06.02.002	TERMINAL
30.06.04	TERMINAL
30.06.10	TERMINAL
30.06.28	TERMINAL
30.06.32	TERMINAL
30.09.02	TERMINAL
30.09.04	TERMINAL
30.09.10	TERMINAL
30.09.16	TERMINAL
90.01	TERMINAL
90.01.04	INTERMEDIATE
90.01.04.002
90.01.04.006
90.01.09.001	INTERMEDIATE
90.01.09.004	INTERMEDIATE
90.04.90.002.04	INTERMEDIATE
90.04.90.003	INTERMEDIATE
90.04.90.022	INTERMEDIATE
90.04.90.048	INTERMEDIATE
90.04.CRAFT	TERMINAL
91.03	TERMINAL
91.06	TERMINAL

However, if I want to remove duplicate results, i try using the Group By on both Fields. The results i am getting are:

Account Code	MOD_TERM
10.36	TERMINAL
20.03.12	TERMINAL
20.03.14	TERMINAL
20.06.02	TERMINAL
20.06.04	TERMINAL
20.06.08	TERMINAL
20.30.MOVES	INTERMEDIATE
20.30.MOVTDY	INTERMEDIATE
20.30.TDY	INTERMEDIATE
30.03.08.006	TERMINAL
30.03.10.006	TERMINAL
30.03.22	TERMINAL
30.03.94	TERMINAL
30.06.02.002	TERMINAL
30.06.04	TERMINAL
30.06.10	TERMINAL
30.06.28	TERMINAL
30.06.32	TERMINAL
30.09.02	TERMINAL
30.09.04	TERMINAL
30.09.10	TERMINAL
30.09.16	TERMINAL
90.01	TERMINAL
90.01.04	INTERMEDIATE
90.01.04.002
90.01.04.006
90.01.09.001	INTERMEDIATE
90.01.09.004	INTERMEDIATE
90.04.90.002.04	INTERMEDIATE
90.04.90.003	INTERMEDIATE
90.04.90.022	INTERMEDIATE
90.04.90.048	INTERMEDIATE
90.04.CRAFT	INTERMEDIATE
91.03	TERMINAL
91.06	TERMINAL

You can see the value of 90.04.CRAFT in MOD_TERM is now "INTERMEDIATE" which was not even a record in the previous query. Any ideas how this might happen?

[isladogs]

This doesn't answer your question but grouping isn't a good way of hiding duplicates.
Unless you also sort your records, you could well end up with unexpected results.

Instead scrap the grouping and just set Unique Values = Yes.
In SQL view that is SELECT DISTINCT ....