Format of Query output

[Sean]

I have written a query that displays the finishing
positions of golfers (in their respective tournaments)
over the last twelve years.

I have placed a sort on GolferName & Date so that my
report looks something like this:

GOLFERNAME DATE POSITION
A 01/01/03 2
A 01/03/03 3
A 01/07/03 5
B 01/03/03 6
B 04/05/03 4

What I would like to see is the following:
GOLFERNAME POSITIONS
A 2,3,5
B 6,4

i.e. A line for each golfer showing finishing positions
from left to right (separated by a comma)for each of his
tournaments.

I would appreciate if someone could show me how to do this.

Thanks

 

[Michel Walsh]

Hi,


You can do it using a temporary table, in which we will read the result.


Delete the temp table if it exists ( DROP TABLE Results).

Create it with: SELECT DISTINCT GolferName, iif(false, ' ', null) AS concat
INTO Results FROM Golfers

Generate the result:

UPDATE Results
INNER JOIN (SELECT * FROM Golfers ORDER BY GolferName, Date) AS g
ON Results.Golfer=g.Golfer
SET concat = ( concat + ", " ) & g.position



(with Access 97, try to use a saved query rather than the virtual table, "
(select... )" as g )

Hoping it may help,
Vanderghast, Access MVP

 

[Sean]

Vanderghast

Thanks for that.

There is one small problem. The results do not appear in
date order.

For example, for golfer A I was expecting to see 2,3,5 and
for golfer B I was expecting to see 6,4 but it does not
appear that way.

 

[Michel Walsh]

Hi,


It is possible that the inner join does not use a table scan, after all,
and thus, that the ORDER BY clause is without effect, since another plan of
execution is used... On the other hand, you can try to reverse the position
of the two tables in the join, ie,, instead of:

UPDATE Results
INNER JOIN (SELECT *
FROM Golfers
ORDER BY GolferName, Date) AS g
ON Results.Golfer=g.Golfer
SET concat = ( concat + ", " ) & g.position


try:

UPDATE (SELECT *
FROM Golfers
ORDER BY GolferName, Date) AS g
INNER JOIN Results
ON Results.Golfer=g.Golfer

SET concat = ( concat + ", " ) & g.position



If that does not work either, I am afraid there is no other simple
alternative than to proceed with a loop, in VBA, on a recordset (which
definitively honors the ORDER BY clause).



Hoping it may help,
Vanderghast, Access MVP

 

[Sean]

Michel

Thanks for your response.

That did not work unfortunately.

Sean