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

[Guest]

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

 

[Rick Brandt]

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

Just put Group By under both fields in the query design grid.

 

[Guest]

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

 

[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.

 

[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)

 

[Guest]

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

 

[Rick Brandt]

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

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

 

[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.)


..