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.

 

[PHV]

You may try this SQL code in your function:
sqlQryList = "SELECT N.cboStd, A.SOPANo" _
& " FROM tblAssessmentLog AS A INNER JOIN tblNCList AS N ON A.txtAssID = N.txtAssID" _
& " WHERE N.cboStd Is Not Null AND A.SOPANo = '" & EntryID & "'"

Create an index on tblAssessmentLog.SOPANo
Create an index on tblNCList.txtAssID

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

 

[MajP]

1. This will help only a little, but get rid of the outer If check. How could

rstQryList.Fields("SOPANo") <> EntryID ?

If

sqlQryList = "SELECT cboStd, sopano FROM qryNClookup WHERE SOPANo = '" & EntryID & "'"

or if using PHVs suggestion

sqlQryList = "SELECT N.cboStd, A.SOPANo" _
 & " FROM tblAssessmentLog AS A INNER JOIN tblNCList AS N ON A.txtAssID = N.txtAssID" _
 & " WHERE N.cboStd Is Not Null AND A.SOPANo = '" & EntryID & "'"

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

 

[CautionMP]

Ok, here is my attempt at redemption.

I did this type of work for a client that used a really slow database engine using an ODBC link. The solution to the speed problem was to work with snapshot recordsets to reduce the number of 'hits' on the external data source.

The creation of the recordset ([tt]rstQryList[/tt]) was moved outside the function that returned the value and passed the sanpshot recordset to the function as an argument. This is probably why I did not think about including the recorset filter ([tt]...WHERE SOPANo = '" & EntryID & "'"[/tt]) in the original code.

The drawback to doing this is that now running a query becomes a process because you have to build a mechanism to create, manage, and update the snapshot recordset before you run the query(s) that use this function.

It can be pain, but as MajP pointed out, if you have 10,000 records the function has to create, run, and destroy 10,000 recordsets. This overhead equates to a speed reduction, and only you can determine how big this reduction will be in your environment.

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