How do I "Group by" more than 1 field in a query?

S

SquirrelToothAnnie

I'm trying to group by ID No' , then by address so that multiple IDs at the
same address will only appear once in the query results. Please answer
clearly as I'm fairly new to this. Many thanks
 
S

SquirrelToothAnnie

Hi,

I tried that but for some reason it isn't working. Any clues as to why would
be much appreciated. The Query is based on another query and involves 2
tables.

Thanks
 
D

David F Cox

If you are grouping by ID you will get one record per ID. (Been there -
Duuuh)

Just group by address. Be prepared for Addresses not to match because of the
smallest differences, like an extra space somewhere.
 
D

Douglas J. Steele

What's the SQL of your query?

(If you're not familiar with SQL, open your query, then select SQL View from
the View menu. Copy what appears in the query window, and paste it into your
response)
 
S

SquirrelToothAnnie

Hi,

Pasted from SQL view
SELECT QryLast2MonthsYCEntrancesdGrouped.Address
FROM QryLast2MonthsYCEntrancesdGrouped
GROUP BY QryLast2MonthsYCEntrancesdGrouped.Address;

The original query (QryLast2MonthsYCEntrancesGrouped) groups by ID no' as
there are multiple records for each ID number.

Thanks
 
R

Rick Brandt

SquirrelToothAnnie said:
Hi,

Pasted from SQL view
SELECT QryLast2MonthsYCEntrancesdGrouped.Address
FROM QryLast2MonthsYCEntrancesdGrouped
GROUP BY QryLast2MonthsYCEntrancesdGrouped.Address;

The original query (QryLast2MonthsYCEntrancesGrouped) groups by ID
no' as there are multiple records for each ID number.

Thanks
Click to expand...

That query does not have the ID field in it. If you only include one field
in the query then that is all you will get in the output. Simplified your
query is...

SELECT Field1
FROM TableName
GROUP BY Field1

....and you are asking why you don't see Field2 in the output. What you need
for that is...

SELECT Field1, Field2
FROM TableName
GROUP BY Field1, Field2
 
D

David F Cox

Apologies, I misunderstood your post.

Group by Address, and select First instead of Group By so that you only get
an ID number once per address. (Last, Max and Min would also achieve the
same result.)


..
 
Top