Aggregating rows with a control

[ScottishFencer]

Here's the question.

I would like to aggregate a few entries together (from an access) query in a single entry on a control (or subform).

The results set looks like this:

ID, Item
101, 1
101, 2
101, 3
102, 1
103, 1
103, 2

etc

I would like to aggregate together to make the following entries:

ID, "Items"
101, 1 2 3
102, 1
103, 1 2

etc

I was looking at Dlookup but couldn't get it working properly. I have a control which has the calcualtion:

dlookup("[item]", "[QryList]", "ID = [ID from QryList])"

I realise that the suntax is not quite correct but I can't get my head around what is correct.

Any help is much appreciated.

 

[Remou]

This FAQ seems to be very similar to what you want:
How to concatenate multiple child records into a single value
faq701-4233

 

[MajP]

I did not look at the FAQ, but here is how I did it.

Make a function:

Public Function concItems(intID As Integer) As String
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select Item from tblItem where ID = " & intID
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rs.EOF
  concItems = concItems & " " & rs.Fields("Item")
  rs.MoveNext
Loop
End Function

Use this function in a query

SELECT DISTINCT tblItem.ID, concItems([ID]) AS StrItems
FROM tblItem;

I will look at the FAQ and see their approach, but this works well.

 

[MajP]

Yeah same logic. Theirs has a few more bells and whistles.

 

[CautionMP]

Which is about the same as this:

[blue]Public Function [/blue]AgrregateEntries(EntryID [blue]As String[/blue]) [blue]As String[/blue]
Dim conCurrent As ADODB.Connection
Dim rstQryList As New ADODB.Recordset
Dim sqlQryList As String, strOutput As String
Set conCurrent = CurrentProject.Connection
[green]'Make sure this statement matches your database[/green]
sqlQryList = "SELECT ID, Item FROM QryList;"
rstQryList.Open sqlQryList, conCurrent, adOpenForwardOnly, adLockReadOnly
Do
  If rstQryList.Fields("ID") = EntryID Then
    [green]'If item is not the first add punctuation[/green]
    If Len(strOutput) > 0 Then
      strOutput = strOutput & ", "
    End If
    strOutput = strOutput & rstQryList.Fields("Item")
  End If
  rstQryList.MoveNext
Loop Until rstQryList.EOF
CleanUp:
rstQryList.Close
Set rstQryList = Nothing
Set conCurrent = Nothing
AgrregateEntries = strOutput
[blue]End Function[/blue]

Note: Change the declaration of EntryID ([tt]EntryID [blue]As String[/blue][/tt]) to match your actual data type.

If you have a big recordset these types of routines will take a while to run.

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[ScottishFencer]

Thanks guys. I'll let you know if I have any problems. I must have missed that FAQ.

 

[ScottishFencer]

Hi guys,

I'm having problems. I deliberately put in different terms to make me understand what was happening - so that I never asked the same question again. I've altered the code:

Public Function AgrregateEntries(EntryID As String) As String
Dim conCurrent As ADODB.Connection
Dim rstQryList As New ADODB.Recordset
Dim sqlQryList As String, strOutput As String
Set conCurrent = CurrentProject.Connection
'Make sure this statement matches your database
sqlQryList = "SELECT SOPANo, cboStd FROM qryNCList2005;"
rstQryList.Open sqlQryList, conCurrent, adOpenForwardOnly, adLockReadOnly
Do
  If rstQryList.Fields("SOPANo") = EntryID Then
    'If item is not the first add punctuation
    If Len(strOutput) > 0 Then
      strOutput = strOutput & ", "
    End If
    strOutput = strOutput & rstQryList.Fields("cbostd")
  End If
  rstQryList.MoveNext
Loop Until rstQryList.EOF
CleanUp:
rstQryList.Close
Set rstQryList = Nothing
Set conCurrent = Nothing
AgrregateEntries = strOutput
End Function

ID = Sopano (the record identifier)
Item = cbostd (the item to be concatenated)
qrylist = qryNClookup (the base query)

The query itself looks like this:

SELECT qryNCLookup.SOPANo, AgrregateEntries([cboStd]) AS NC
FROM qryNCLookup;

When I run the query I get all of the SopaNo Ids but NC records are all empty strings. Am I missing something terribly obvious?

 

[MajP]

Not seeing write away, but debug with this

If Len(strOutput) > 0 Then
strOutput = strOutput & ", "
End If
strOutput = strOutput & rstQryList.Fields("cbostd")
msgbox EntryID & " " & rstQryList.Fields("cbostd")
End If
This will help to see if all field names are properly spelled and if it is looping through the records for each ID.

I should see an entry ID, and a field value. You will have to hit control esc to get out of this.

although unrelated this might be backwards
strOutput = strOutput & ", "
I think
strOutput = "," & strOutput

 

[ScottishFencer]

Hmmm ....

This is what I've been getting:

SOPANo NC
002
011
015

I've been playing with the code and got results - of sorts.



Here is the code that I used to create it:

Public Function AgrregateEntries(EntryID As String) As String
Dim conCurrent As ADODB.Connection
Dim rstQryList As New ADODB.Recordset
Dim sqlQryList As String, strOutput As String
Set conCurrent = CurrentProject.Connection
'Make sure this statement matches your database
sqlQryList = "SELECT SOPANo, cboStd FROM qryNCLookup;"
rstQryList.Open sqlQryList, conCurrent, adOpenForwardOnly, adLockReadOnly
Do
  If rstQryList.Fields("cbostd") = EntryID Then
    'If item is not the first add punctuation
    If Len(strOutput) > 0 Then
      strOutput = strOutput & ", "
      
    End If
    strOutput = strOutput & rstQryList.Fields("[b]cbostd[/b]")

If I put SOPANO back in this line then it returns blank fields again.

  End If
  rstQryList.MoveNext
Loop Until rstQryList.EOF
CleanUp:
rstQryList.Close
Set rstQryList = Nothing

Here is a sample of the output:

"SOPANo"	"NC"
"002"	"1.1.2, 1.1.2, 1.1.2"
"002"	"1.7.1, 1.7.1, 1.7.1, 1.7.1"
"002"	"2.2.2, 2.2.2, 2.2.2"

 

[ScottishFencer]

Oh, I forgot to mention that when I stuck the msgbox in it didn't return anything...

The sample output I listed above is not correct. each "NC" can only be listed once. I cannot get my head around where I am going wrong - but I feel I am close.

 

[CautionMP]

[navy]Public Function[/navy] AgrregateEntries(EntryID [navy]As [u]String[/u][/navy])

Change the [navy]String[/navy] to the correct data type. In the table what data type is [tt]SOPANo[/tt] (Integer, Long, Single, Double, Currency)?

If it's not a string then [tt]rstQryList.Fields("SOPANo") = EntryID[/tt] will return [navy]False[/navy].

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[MajP]

Obviously if the message box returns nothing, you are never entering the if statment. Therefore CautionMP is correct,

If it's not a string then rstQryList.Fields("SOPANo") = EntryID will return False

 

[PHV]

Why not something like this ?
sqlQryList = "SELECT SOPANo, cboStd FROM qryNCList2005 WHERE SOPANo='" & EntryID & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

However, now that I look at the way you did it, it is real really inefficient and confusing code.

 If rstQryList.Fields("SOPANo") = EntryID Then
    'If item is not the first add punctuation
    If Len(strOutput) > 0 Then
      strOutput = strOutput & ", "
    End If
    strOutput = strOutput & rstQryList.Fields("cbostd")
  End If
  rstQryList.MoveNext

Think about this. If you have 10,000 records you loop through this 10,000 X 10,000 times. Lets see that is a lot of zeros. That should be real quick

This is how you should do it, as I originally said.

code
strSql = "SELECT SOPANo, cboStd FROM qryNCLookup where SOPNo = " & entryID
code
Do while not rstQryList.eof
  code
   rstQryList.movenext
loop

Faster and a lot cleaner. Assume at most a field has 10 NC. Then you loop 10,000 * 10 not 100000000 times.

 

[MajP]

Damn, PHV beat me again. But I think we decided that EntryID should be a long not a string. Whichever one it is need to be consistent.

If SOPANO is a long then:
Public Function AgrregateEntries(EntryID As long) As String
and
"SELECT SOPANo, cboStd FROM qryNCLookup where SOPNo = " & entryID

or as PHV said if SOPANO is a string field then
Public Function AgrregateEntries(EntryID As string) As String
and
"SELECT SOPANo, cboStd FROM qryNCLookup where SOPNo = '" & entryID & "'

 

[CautionMP]

Yeah, not sure why I glossed over setting the filter in [tt]rstQryList[/tt], should make the function run considerably faster and greatly simplifies the whole function.

...and some times you loose some.
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[ScottishFencer]

Thanks for your help guys. I will definitely check all this out again tomorrow when I am back in the office. I felt sure I had checked for the type for EntryID (string) but I concede I may well be wrong. I will have a look at simplifying the filter as well.

 

[ScottishFencer]

Change the String to the correct data type. In the table what data type is SOPANo (Integer, Long, Single, Double, Currency)?

As I thought SOPANO is defintely a string. It is listed in the DB as NVARCHAR [text in Access] - which is handled as a string is it not? I even altered the underlying query to reformat SOPANO (as a test) to a string to be sure.

So I did some extra debugging:

I added a msgbox below sqlQryList [msgbox sqlqrylist] to see what EntryID was evaluating to. It's evaluating cbostd.... This means that it is obviously never going to kick into the loop.

I am attempting to concatenate the cbostd field so the query is:

SELECT DISTINCT qryNCLookup.SOPANo AS Mem, AgrregateEntries([cboStd]) AS NC
FROM qryNCLookup;

So obviously the variable that is being fed in here is cbostd and so SOPANo will never = EntryID ...

I admit that, at this point, I am having a little difficulty getting my head around the loop here. Do I have to add in another variable? Am I being really thick (I'm starting to think so)?

Then it dawns on me - the query is wrong...

The query should be this:

SELECT DISTINCT qryNCLookup.SOPANo, AgrregateEntries([SOPANo]) AS NC
FROM qryNCLookup;

Here is the final function:

Public Function AgrregateEntries(EntryID As String) As String
Dim conCurrent As ADODB.Connection
Dim rstQryList As New ADODB.Recordset
Dim sqlQryList As String, strOutput As String
Set conCurrent = CurrentProject.Connection
'Make sure this statement matches your database
sqlQryList = "SELECT cboStd, sopano FROM qryNClookup WHERE SOPANo = '" & EntryID & "'"
rstQryList.Open sqlQryList, conCurrent, adOpenForwardOnly, adLockReadOnly
Do While Not rstQryList.EOF
  If rstQryList.Fields("SOPANo") = EntryID Then
    'If item is not the first add punctuation
    If Len(strOutput) > 0 Then
      strOutput = strOutput & ", "
    End If
    strOutput = strOutput & rstQryList.Fields("cbostd")
  End If
  rstQryList.MoveNext
 Loop
 rstQryList.Close
 Set rstQryList = Nothing
 Set conCurrent = Nothing
AgrregateEntries = strOutput
End Function

any advice about improving performance would be appreciated!

Thanks again you have all been brilliant.

 

[PHV]

any advice about improving performance
What is the SQL code of qryNClookup ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScottishFencer]

I could possible do away with qryNClookup (something that I wrote when I started looking at this). It's a very simple query that joins 2 tables together.

SELECT tblAssessmentLog.SOPANo, tblNCList.cboStd
FROM tblAssessmentLog INNER JOIN tblNCList ON tblAssessmentLog.txtAssID = tblNCList.txtAssID
WHERE (((tblNCList.cboStd) Is Not Null))
ORDER BY tblAssessmentLog.SOPANo, tblNCList.cboStd;