Top 3

[janf]

Access 2007
I have two tables; Medlemmer (name, address, phone etc.) and Resultater
(holds information for (0 to n) competition results for each member), and one
form with a 3 page tabctrl. (or MultiPage??). Record source is Medlemmer.
On tab (page) 1 I enter info for each member. Resultater is linked to
Medlemmer as a subdatasheet on tab (page) 2 where Medlemmer.[M-ID]
(Autonumber) = Resultater.ID.

So far all is perfect.

I want to have the 3 best results for each member.
(Not all members have results, and some have only one or two.)

The table Resultater can hold the results like this:

[ID] - [Competitionname] - [Res]
1 - (...) - 236
1 - (...) - 224
3 - (...) - 234
2 - (...) - 241
1 - (...) - 230
2 - (...) - 242
1 - (...) - 239
1 - (...) - 225
3 - (...) - 235
2 - (...) - 236
3 - (...) - 235
.....
n - 212 - <empty> - <empty>

and I want to show the result in a report (and in textboxes on the form) like:

[ID] - [Res1] - [Res2] - [Res3]
1 - 239 - 236 - 230
2 - 242 - 241 - 236
3 - 235 - 235 - 234
.....
n - 212 - <empty> - <empty>

Any suggestions?
Thanks

 

[Allen Browne]

Use VBA to concatenate the related records into a string.

Details in:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

You will need to change the line:
strSql = "SELECT " & strField & " FROM " & strTable
to read:
strSql = "SELECT TOP 3 " & strField & " FROM " & strTable

 

[janf]

Hello, thanks for your answer. It took som time to figure it out, but now it
seems to work. For some hours all I got was Error 3141 when i run the query.
After clicking "OK" about 200 times in the box, I got the "coloumn" with the
names, but the "coloumn" named "Expr1" was empty. While composing a long
answer, all the time checking the code and trying to run the query, I at last
found the error. The missing link, or rather the missing space between <3>
and <"> in <TOP 3 ">. Great feeling when you have been sweating for hours,
and then finally finds the missing space or the comma in the wrong place, and
all works perfect.

All that is left now is to separate the 3 3-digit values into 3 textboxes
for the form and the report.

Thanks


Allen Browne skrev:

Use VBA to concatenate the related records into a string.

Details in:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

You will need to change the line:
strSql = "SELECT " & strField & " FROM " & strTable
to read:
strSql = "SELECT TOP 3 " & strField & " FROM " & strTable

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2007
I have two tables; Medlemmer (name, address, phone etc.) and Resultater
(holds information for (0 to n) competition results for each member), and
one
form with a 3 page tabctrl. (or MultiPage??). Record source is Medlemmer.
On tab (page) 1 I enter info for each member. Resultater is linked to
Medlemmer as a subdatasheet on tab (page) 2 where Medlemmer.[M-ID]
(Autonumber) = Resultater.ID.

So far all is perfect.

I want to have the 3 best results for each member.
(Not all members have results, and some have only one or two.)

The table Resultater can hold the results like this:

[ID] - [Competitionname] - [Res]
1 - (...) - 236
1 - (...) - 224
3 - (...) - 234
2 - (...) - 241
1 - (...) - 230
2 - (...) - 242
1 - (...) - 239
1 - (...) - 225
3 - (...) - 235
2 - (...) - 236
3 - (...) - 235
....
n - 212 - <empty> - <empty>

and I want to show the result in a report (and in textboxes on the form)
like:

[ID] - [Res1] - [Res2] - [Res3]
1 - 239 - 236 - 230
2 - 242 - 241 - 236
3 - 235 - 235 - 234
....
n - 212 - <empty> - <empty>

Any suggestions?
Thanks