Multiple rows in query

J

jonmarcr

my query gives me results like this:

6 Joe Bloggs n.a.
6 Joe Bloggs n.a.
6 Joe Bloggs n.a.
6 Joe Bloggs n.a.
14 Fred Smith 123
14 Fred Smith 123
14 Fred Smith 123
14 Fred Smith 123

I only want to pick one result for each person.
I thought that could be accomplished by using a SELECT and GROUP BY
instruction but I cannot get it to work.
Any ideas pls?
Thanks
 
D

Dennis

I assume there are 3 fields in this query. Put these fields in a query and
click the totals button on the toolbar. This will show Group By for each of
your columns. Running this query will give 1 result for each person. Click on
the SQL view for this query and you can see and use the SELECT & GROUP BY
syntax that is shown elsewhere in your database
 
J

jonmarcr

Hi Dennis,
Thanks - it sort of works but the statement I have been trying to make work is
SELECT Table1.Id, Table1.Name, Table1.StatOrd
FROM Table1
INNER JOIN
(SELECT Table1.Name, Max(Table1.StatOrd) AS MaxOfStatOrd
FROM Table1
GROUP BY Table1.Name) AS Max
ON Table1.Name=Max.Name AND Table1.StatOrd = Max.MaxOfStatOrd

But that gets me into a pickle!
Jon
 
Top