Group by

[newbie87]

Hi All,

i want to write a query that allows me to view all records from my Details table, if multiple names have the same age, i just want to show one row with that name and age. For example below from "Details" i just want to view Peter and Adam

ID | Name | Age
1    Peter   19
2    Adam    18
3    John    19


Select ID, ForeName as Name, Age From Details
GROUP BY Age

I've attempted this but it just prints all rows

Many thanks

 

[MSAccessRookie]

Hi All,

i want to write a query that allows me to view all records from my Details table, if multiple names have the same age, i just want to show one row with that name and age. For example below from "Details" i just want to view Peter and Adam

ID | Name | Age
1    Peter   19
2    Adam    18
3    John    19
 
 
Select ID, ForeName as Name, Age From Details
GROUP BY Age

I've attempted this but it just prints all rows

Many thanks

You will probably need a Sub-Query scenario where in the Main Query you select the items, and in the Sub-Query you group them according to age. I believe that something like the following might work:

MAIN QUERY:
 
Select TOP 1 ID, ForeName as Name, Age From AgeSubQuery
 
SUB QUERY AgeSubQuery:
 
Select TOP 1 Age From Details
GROUP BY Age
ORDER BY ID