Grouping Problem????

[Paul W Smith]

I have the a query which produces the following results:

100 A Z 1/1/2006 Orange Dog
99 A Y 2/1/2006 Red Duck
105 A X 3/1/2006 Green Cat
102 B Y 2/1/2006 Blue Cat
106 B X 1/1/2006 Red Dog
98 B Z 3/1/2006 Orange Duck
101 C Z 1/1/2006 Blue Dog
96 C X 3/1/2006 Green Cat
98 C Y 2/1/2006 Green Cat


I want to extract from this a refined query showing the line for each entity
in the second column, where the figure in the first column is the maximum.
I need the whole line and this is my problem using 'Max' and group by does
not seem to refine anything it just returns everything.

e.g.
105 A X 3/1/2006 Green Cat
106 B X 1/1/2006 Red Dog
101 C Z 1/1/2006 Blue Dog

Please offer some assistance someone - this got missed when it was first
posted.

Paul Smith

 

[Jeff Boyce]

Paul

One way (perhaps not the most elegant) to do this would be to use more than
one query. In your first query, get the Max of your column1, GroupBy
column2.

Then use this query as one source in a new query, and the original table
(query) as the other source. Join by column1 and column2, and return
(display) the columns you wish to see.

Not pretty, but functional.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Paul W Smith]

Thanks Jeff for the prompt response.

If anyone else has a more elegant solution I would be very grateful to see
it. In the mean time I will work with Jeff's.

PWS

 

[John Spencer]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Sample query - using generic names
SELECT A.*
FROM TableName as A
WHERE A.ColumnA =
(SELECT Max(B.ColumnA)
FROM TableName as B
WHERE B.ColumnB = A.ColumnB)

 

[Paul W Smith]

SELECT tDivisionTeams.Year, tClubs.ClubName, tUpdates.Score, tUpdates.Wkts,
tUpdates.Dec, tClubs_1.ClubName AS OppoTeam, tClubs.ClubID, tClubs_1.ClubID
AS OppoClubID
FROM (tDivisions INNER JOIN (tClubs INNER JOIN (tDivisionTeams INNER JOIN
tPairings ON tDivisionTeams.TeamID=tPairings.TeamID) ON
tClubs.ClubID=tDivisionTeams.ClubID) ON
tDivisions.DivisionID=tDivisionTeams.DivisionID) INNER JOIN (tClubs AS
tClubs_1 INNER JOIN ((tUpdates INNER JOIN tPairings AS tPairings_1 ON
tUpdates.OppoPairID=tPairings_1.PairingID) INNER JOIN tDivisionTeams AS
tDivisionTeams_1 ON tPairings_1.TeamID=tDivisionTeams_1.TeamID) ON
tClubs_1.ClubID=tDivisionTeams_1.ClubID) ON
tPairings.PairingID=tUpdates.PairingID
GROUP BY tDivisionTeams.Year, tClubs.ClubName, tUpdates.Score,
tUpdates.Wkts, tUpdates.Dec, tClubs_1.ClubName, tUpdates.PairingID,
tUpdates.OppoPairID, tDivisions.Team, tClubs.ClubID, tClubs_1.ClubID
HAVING (((tUpdates.Score)>0) AND ((tDivisions.Team)=1));

The above looks pretty complicated to me - good luck with it.

I tried to simplify things in my example so my issue was easier to
understand. The table below is what the above query produces. I now want
to refine this so I get one line per different item in ClubName column.

qry_SeasonClubHighScores1 Year ClubName Score Wkts Dec OppoTeam ClubID
OppoClubID
2006 Barnes 158 10 No Ickenham 3 18
2006 Brentham 150 10 No Ealing 6 8
2006 Brentham 197 7 No Richmond 6 23
2006 Brondesbury 94 4 No Stanmore 7 27
2006 Brondesbury 266 6 Yes Winchmore Hill 7 32
2006 Ealing 152 8 No Brentham 8 6
2006 Ealing 173 10 No Teddington 8 28
2006 Eastcote 240 6 No Southgate 9 25
2006 Enfield 84 10 No Wembley 11 31
2006 Finchley 137 9 No Uxbridge 12 30
2006 Finchley 265 5 No Teddington 12 28
2006 Hampstead 123 2 No Winchmore Hill 13 32
2006 Hampstead 323 5 Yes Richmond 13 23
2006 Harrow 189 6 No South Hampstead 14 26
2006 Hornsey 194 9 No Shepherds Bush 17 24
2006 Ickenham 159 8 No Barnes 18 3
2006 Richmond 179 10 No Hampstead 23 13
2006 Richmond 272 9 No Brentham 23 6
2006 Shepherds Bush 200 10 No Hornsey 24 17
2006 South Hampstead 250 3 Yes Harrow 26 14
2006 Southgate 303 5 Yes Eastcote 25 9
2006 Stanmore 93 10 No Brondesbury 27 7
2006 Stanmore 209 8 No Uxbridge 27 30
2006 Teddington 176 2 No Ealing 28 8
2006 Teddington 264 7 Yes Finchley 28 12
2006 Uxbridge 136 7 Yes Finchley 30 12
2006 Uxbridge 228 10 No Stanmore 30 27
2006 Wembley 86 2 No Enfield 31 11
2006 Winchmore Hill 122 10 No Hampstead 32 13
2006 Winchmore Hill 198 6 No Brondesbury 32 7

 

[John Spencer]

That's nice. Which fields are you interested in getting the Maximum of ?

You could do this with two queries. The first query would get the Maximum
of Whichever field you are interested in along with other information you
might need for grouping.

QueryOne: Save it as Q_MaxClubYear
SELECT D.ClubID, Max(D.Year) as TheYear
FROM tDivisionTeams
GROUP BY D.ClubID

Now Add that query to your existing query as another table joining on ClubID
and TheYear as appropriate. Drop the Grouping in the existing query.