Top n query using Allen Browne's "ConcatRelated"

[janf]

I am trying to get the top 3 values from a table using Allen Browne's
"ConcatRelated" function, but there seems to be something I don't get right.
The property of the field in the table is "text", but it holds (always)
three or (sometimes) two numbers (or it is empty (not 0)). (The same "error"
shows if the field property is set to "number".
The line of code (4) below is OK, if I can sort it descending. I can limit
the text field in the report to show what i want, but i need only the three
highest values.

Can someone help, please.
janf


Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = " | ") As Variant
On Error GoTo Err_Handler
'Purpose: Generate a concatenated string of related records.
'Return: String variant, or Null if no matches.
'Arguments: strField = name of field to get results from and concatenate.
' strTable = name of a table or query.
' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.
' strSeparator = characters to use between the concatenated
values.
'Notes: 1. Use square brackets around field/table names with spaces
or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later),
but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are
returned as ZLSs.
' 4. Returning more than 255 characters to a recordset
triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.

'Initialize to Null
ConcatRelated = Null

'Build SQL string, and get the records.
'1
strSql = "SELECT TOP 3 " & strField & " FROM " & strTable
' returns <empy> (nothing)

'2
' strSql = "SELECT TOP 3 " & val(strField) & " FROM " & strTable
' returns "0 | 0 | 0" (3 records)

'3
' strSql = "SELECT " & val(strField) & " FROM " & strTable
' returns "0 | 0 | 0 | 0 | 0 | 0" (all records)

'4
' strSql = "SELECT " & strField & " FROM " & strTable
' returns "223 | 231 | 229 | 240 | 215 | 238" (all
records)

' WANTED: "240 | 238 | 231" (Top 3 records)


If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If


In query:
Expr1: ConcatRelated("[25-sk]";"Resultater";"ID = " & [M-ID])

' strField = "[25-sk]" (Properties: text, 3 chrs, input mask = 009;0;_)
' strTable = "Resultater"
' strWhere = "ID = " & [M-ID]

 

[Allen Browne]

You have several issues to solve here:

a) Which are the TOP 3 depends on how you sort the records. Therefore it's
the ORDER BY clause you need to concentrate on.

b) If you have Text fields, they will sort as text (e.g. 11 before 9.)

c) You can us Val() to convert the text to numbers, but Val() can't cope
with Nulls. You will therefore need to une Nz() to convert the nulls to
something else.

d) Where there are multiple values tied for 3rd place, JET can't decide
which are the top 3, and so will return more than 3. This is going to happen
where there are lots of zeros (or whatever your Nz() returns.) To avoid
this, you need a unique field (such as the primary key) in the ORDER BY
clause as well.

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

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

I am trying to get the top 3 values from a table using Allen Browne's
"ConcatRelated" function, but there seems to be something I don't get
right.
The property of the field in the table is "text", but it holds (always)
three or (sometimes) two numbers (or it is empty (not 0)). (The same
"error"
shows if the field property is set to "number".
The line of code (4) below is OK, if I can sort it descending. I can limit
the text field in the report to show what i want, but i need only the
three
highest values.

Can someone help, please.
janf


Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = " | ") As Variant
On Error GoTo Err_Handler
'Purpose: Generate a concatenated string of related records.
'Return: String variant, or Null if no matches.
'Arguments: strField = name of field to get results from and
concatenate.
' strTable = name of a table or query.
' strWhere = WHERE clause to choose the right values.
' strOrderBy = ORDER BY clause, for sorting the values.
' strSeparator = characters to use between the concatenated
values.
'Notes: 1. Use square brackets around field/table names with spaces
or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later),
but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are
returned as ZLSs.
' 4. Returning more than 255 characters to a recordset
triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued
field.

'Initialize to Null
ConcatRelated = Null

'Build SQL string, and get the records.
'1
strSql = "SELECT TOP 3 " & strField & " FROM " & strTable
' returns <empy> (nothing)

'2
' strSql = "SELECT TOP 3 " & val(strField) & " FROM " & strTable
' returns "0 | 0 | 0" (3 records)

'3
' strSql = "SELECT " & val(strField) & " FROM " & strTable
' returns "0 | 0 | 0 | 0 | 0 | 0" (all records)

'4
' strSql = "SELECT " & strField & " FROM " & strTable
' returns "223 | 231 | 229 | 240 | 215 | 238" (all
records)

' WANTED: "240 | 238 | 231" (Top 3 records)


If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If


In query:
Expr1: ConcatRelated("[25-sk]";"Resultater";"ID = " & [M-ID])

' strField = "[25-sk]" (Properties: text, 3 chrs, input mask = 009;0;_)
' strTable = "Resultater"
' strWhere = "ID = " & [M-ID]

 

[janf]

Thank you for answer. After som guessing I got it working perfect.

'Build SQL string, and get the records.
strSql = "SELECT " & IIf(Nz(val(strField)) = vbNullString, "000",
strField) & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy & "DESC"
End If

The query returns all records, descending, and the size of the text box in
the report is set to show the wanted values.

But...
How can I now "catch" the three numbers to use in another query (update)?
Some function with "Left" or "mid", based on the result from the above query?
Grateful for any suggestion.

janf

You have several issues to solve here:

a) Which are the TOP 3 depends on how you sort the records. Therefore it's
the ORDER BY clause you need to concentrate on.

b) If you have Text fields, they will sort as text (e.g. 11 before 9.)

c) You can us Val() to convert the text to numbers, but Val() can't cope
with Nulls. You will therefore need to une Nz() to convert the nulls to
something else.

d) Where there are multiple values tied for 3rd place, JET can't decide
which are the top 3, and so will return more than 3. This is going to happen
where there are lots of zeros (or whatever your Nz() returns.) To avoid
this, you need a unique field (such as the primary key) in the ORDER BY
clause as well.

I am trying to get the top 3 values from a table using Allen Browne's
"ConcatRelated" function, but there seems to be something I don't get

[quoted text clipped - 79 lines]

' strTable = "Resultater"
' strWhere = "ID = " & [M-ID]

 

[Allen Browne]

Not exactly sure where you are going with this, but a subquery is probably
the path to take:
http://allenbrowne.com/subquery-01.html

 

[janf via AccessMonster.com]

I am a member of a rifle shooters club in Norway (maybe that is why I am
aiming so high).

A lot of guesswork have got me this far. My DB keeps track of all the members
in the club, including their results from various shooting competitions. The
shooters are placed in "groups", based on their top 3 results the previous
year, so that shooters of the same skill level are in the same group.

E.g.
One shooter has the 3 best results this year: 238 - 232 - 232 (25 shots at
200 meters). This means he (she) is qualified to shoot in "group 4" the next
year.
Results between 240 and 230 qualifies to group 4. Less than 230, but over 219
qualifies to group 3, and 240 up to max 250 points qualifies to group 5 (the
Elite). (Actually, it is the third best result that is important, as 245 -
244- 237 qualifies to group 4.)

This system is made so the competition is not "too" unfair. One who can shoot
243 - 248 points time after time is always superior to one that struggles to
even reach 230 points now and then. Also, one in a lower group that shoots
high results, is forced up to a higher group next year.

The top 3 results are used in reports to the organisation (http://www.dfs.no)
the club belongs to, to document in witch group the shooters will be the
following year. In addition to showing the three best results in the report,
I would like to use the (third best) result to update the field that
indicates the shooters group next year (preferably automatic).

The report looks like this:
[From table: Medlemmer (Members) ] - [Tbl:Resultater] - [Tbl: Medlemmer]

[Name] - [Birthyear] - [Group this year] - [Top 3 results] - [Group next year]


[Group this year] is now set manually from a drop down list.

[Top 3 results] is OK now.

[Group next year] must (now) be set manually from a drop down list.
If possible I would like this to be set automatic, based on the third best
result, so that the field is uppdated according to the value of the result
entered.
(I.e. a third best result of 225 sets the [Group next year] field to 3, and
when a third best result of between 240 and 230 is entered the [Group next
year] field changes to 4, or between 250 and 240 changes to 5.)

At the end of the year, at the push of a button, I would like the [Group this
year] field updated with the values from the [Group next year] field, and
[Group next year] field cleared of all values.

Pushing the next button then clears all records from the table "Resultater",
and all is set for a new year of competitions. (This is OK.)


Hopefully this clarifies a little

janf

 

[Allen Browne]

You'll have to work out the specifics, but see the TOP N per group example
here:
http://allenbrowne.com/subquery-01.html#TopN

Once you have that, you can create another query to:
SELECT ShooterID, Min(Score) AS ThirdScore
FROM Query1
GROUP BY ShooterID;

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

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

I am a member of a rifle shooters club in Norway (maybe that is why I am
aiming so high).

A lot of guesswork have got me this far. My DB keeps track of all the
members
in the club, including their results from various shooting competitions.
The
shooters are placed in "groups", based on their top 3 results the previous
year, so that shooters of the same skill level are in the same group.

E.g.
One shooter has the 3 best results this year: 238 - 232 - 232 (25 shots at
200 meters). This means he (she) is qualified to shoot in "group 4" the
next
year.
Results between 240 and 230 qualifies to group 4. Less than 230, but over
219
qualifies to group 3, and 240 up to max 250 points qualifies to group 5
(the
Elite). (Actually, it is the third best result that is important, as 245 -
244- 237 qualifies to group 4.)

This system is made so the competition is not "too" unfair. One who can
shoot
243 - 248 points time after time is always superior to one that struggles
to
even reach 230 points now and then. Also, one in a lower group that shoots
high results, is forced up to a higher group next year.

The top 3 results are used in reports to the organisation
(http://www.dfs.no)
the club belongs to, to document in witch group the shooters will be the
following year. In addition to showing the three best results in the
report,
I would like to use the (third best) result to update the field that
indicates the shooters group next year (preferably automatic).

The report looks like this:
[From table: Medlemmer (Members) ] - [Tbl:Resultater] - [Tbl: Medlemmer]

[Name] - [Birthyear] - [Group this year] - [Top 3 results] - [Group next
year]


[Group this year] is now set manually from a drop down list.

[Top 3 results] is OK now.

[Group next year] must (now) be set manually from a drop down list.
If possible I would like this to be set automatic, based on the third best
result, so that the field is uppdated according to the value of the result
entered.
(I.e. a third best result of 225 sets the [Group next year] field to 3,
and
when a third best result of between 240 and 230 is entered the [Group next
year] field changes to 4, or between 250 and 240 changes to 5.)

At the end of the year, at the push of a button, I would like the [Group
this
year] field updated with the values from the [Group next year] field, and
[Group next year] field cleared of all values.

Pushing the next button then clears all records from the table
"Resultater",
and all is set for a new year of competitions. (This is OK.)


Hopefully this clarifies a little

janf