Query Results - Concatenating

[jgarwood]

I'm havign a little trouble getting a query to return the data in the exact format I want.

Currently the data returns like:
HomeTeam HomeGoals AwayGoals AwayTeam GameUID Scorer
Wheatsheaf 4 2 Sudbury Court 7 A Ellis
Wheatsheaf 4 2 Sudbury Court 7 S Ellis
Wheatsheaf 4 2 Sudbury Court 7 D Lish
Wheatsheaf 4 2 Sudbury Court 7 AChatterton
Brimsdown Rovers 1 4 Wheatsheaf 8 Jay Richardson 4
Wheatsheaf 4 1 SPRB 9 Jay Richardson
Wheatsheaf 4 1 SPRB 9 A Ellis 3

I want to concatenate the Scorer field as per below.

HomeTeam HomeGoals AwayGoals AwayTeam GameUID Scorer
Wheatsheaf 4 2 Sudbury Court 7 A Ellis, S Ellis, D Lish, AChatterton
Brimsdown Rovers 1 4 Wheatsheaf 8 Jay Richardson 4
Wheatsheaf 4 1 SPRB 9 Jay Richardson, A Ellis 3

Is there anyway to do this in access?
Any help would be apreciated.
Thanks

 

[DJN]

Not too sure that you can get it exactly as formatted, but do the formatting in a report and not the query. Use the report wizard. The wizard asks you for what level of grouping you want. Choose Home Team, Home Goals, Away Team and Away Goals. Drag the Home Goals header, Away Team header and the Away Team goals up to the Home Team header. Close up the spaces left and you should see something that is close to what you want. The only difference is that the scorers ar in a list and not in a row.

 

[jgarwood]

Thanks DJN.

The data will be hosted on the web, so I fguess I can manage this with vbs, well I could if I knew how. If anyone has any suggestions on this front that would help.

 

[reclusivemonkey]

I think you could accomplish this in VBA, by declaring the scorers into an array, then just putting each value from the array into a string. However, I think that may be overkill. You may be able to get it into the format you want with a Pivot Table. If this is for the web, are you wanting to have a 'live' page with dynamic HTML or are you just wanting some HTML pages to upload to a server? Either way, I don't think you'll need VBS. DAP will give you an interactive HTML page and Reports can export to HTML also. Give us a bit more information and I am sure you can get the output you want.

 

[cavscout]

KenHigg submitted this sample in another thread but it is an A2K format. If anyone can convert this to 97 that would be great.