Need to count/sum number of times a record is returned, then combine results in one cell.

[FoolzRailer]

Hello



I'm trying to make a query, that counts the number of times a given record is present. I'm having a though time explaining it, so added a picture a picture for clarification.

I would like for there to be only one instance of 301, in Udtryk 1, it should in one cell something like this: "FS:2, OB: 2, RB: 1".



So far I've got this code:

SELECT Rørenes_tilstand.LedningsID, Rørenes_tilstand.OpstroemKnudeID, Rørenes_tilstand.OpstrømsKnudeNavn, Rørenes_tilstand.Opstrømsdybde, Rørenes_tilstand.NedstroemKnudeID, Rørenes_tilstand.Nedstrømsdybde, Rørenes_tilstand.NedstrømsKnudeNavn, Rørenes_tilstand.Laengde, Rørenes_tilstand.Handelsmaal, Rørenes_tilstand.Materiale, Rørenes_tilstand.TVObsKode, Count([TVObs].[TVObsKode]) AS Udtryk1
FROM Rørenes_tilstand
GROUP BY Rørenes_tilstand.LedningsID, Rørenes_tilstand.OpstroemKnudeID, Rørenes_tilstand.OpstrømsKnudeNavn, Rørenes_tilstand.Opstrømsdybde, Rørenes_tilstand.NedstroemKnudeID, Rørenes_tilstand.Nedstrømsdybde, Rørenes_tilstand.NedstrømsKnudeNavn, Rørenes_tilstand.Laengde, Rørenes_tilstand.Handelsmaal, Rørenes_tilstand.Materiale, Rørenes_tilstand.TVObsKode;

Is this possible?

[FoolzRailer]

So i managed to Count "RB" with this Count(IIf(([TVObs].[TVObsKode])="RB",True,Null)) AS Udtryk1.

Now I just need to count the rest in one cell.

[orange]

Need to know more about table
Rørenes_tilstand

Why are there duplicates/replicates?

[FoolzRailer]

Rørenes_tilstand is Union query, that gathers together 5 separate queries.

Rørenes_tilstand:

SELECT  *
FROM RB_2_4
UNION  ALL
SELECT  *
FROM OB_2_4
UNION  ALL
SELECT  *
FROM PF_2_4
UNION  ALL
SELECT  *
FROM DE_3_4
UNION  ALL
SELECT  *
FROM FS_2_4
UNION ALL SELECT  *
FROM IS_2_4;

RB_2-4:

SELECT DISTINCTROW Ledning.ID AS LedningsID, Ledning.OpstroemKnudeID, Knude.Knudenavn AS OpstrømsKnudeNavn, Opstrømsdybde.Opstrømsdybde, Ledning.NedstroemKnudeID, Nedstrømsdybde.Nedstrømsdybde, Knude_1.Knudenavn AS NedstrømsKnudeNavn, Delledning.Laengde, Delledning.Handelsmaal, K_LedningMateriale.Beskrivelse AS Materiale, LednHaendelse.MaaltStationStart, TVObs.TVObsKode, TVObs.TVObsKlasse, TVObs.Type1, TVObs.Type2
FROM Nedstrømsdybde INNER JOIN (Opstrømsdybde INNER JOIN ((((((TVObs INNER JOIN (TVInspektion INNER JOIN LednHaendelse ON TVInspektion.RapportID = LednHaendelse.RapportID) ON TVObs.LednHaendelseID = LednHaendelse.ID) INNER JOIN Knude ON TVInspektion.Startpunktnr = Knude.Knudenavn) INNER JOIN Ledning ON Knude.ID = Ledning.OpstroemKnudeID) INNER JOIN Delledning ON Ledning.ID = Delledning.LedningID) INNER JOIN K_LedningMateriale ON Delledning.MaterialeKode = K_LedningMateriale.Kode) INNER JOIN Knude AS Knude_1 ON (Ledning.NedstroemKnudeID = Knude_1.ID) AND (TVInspektion.Slutpunktnr = Knude_1.Knudenavn)) ON Opstrømsdybde.ID = Knude.ID) ON Nedstrømsdybde.ID = Knude_1.ID
WHERE (((TVObs.TVObsKode)="RB") AND ((TVObs.TVObsKlasse)>=2));

There is 4 other similar queries, where instead of RB and >=2 they may have different criteria.

Is this what you needed, or do you need more information?

I haven't been able to solve this yet, and have been reading up on different forums, as I see it I may have to concatenate, but this doesn't seem possible in Access. So might need to do some VB scripting, but I'm not familiar with it.

[FoolzRailer]

As a follow-on from what @krish-km mentioned, here's a function I've adapted from Allen Browne's ConcatRelated function (note that I've removed a lot of the original's general purpose utility, so should only be used for your specific scenario).

Put this in a VBA module...

  Public Function ConcatRelated(strField1 As String, _
                strField2 As String, _
                strRelField As String, _
                lngRelFieldVal As Long, _
                strOrderBy As String, _
                strTable As String, _
                Optional strSeparator = ", ") As Variant

On Error GoTo Err_Handler

    Dim db As DAO.Database          ' Database
    Dim rs As DAO.Recordset         '
    Dim strSql As String            ' SQL statement
    Dim strOut As String            ' Output string to concatenate to.
    Dim lngLen As Long              ' Length of string.

    ' Initialize to Null
    ConcatRelated = Null

    ' Find related records limited by related field
    strSql = "SELECT " & strRelField & ", " & strField1 & ", " & strField2 _
           & " FROM " & strTable & " WHERE " & strRelField & " = " & lngRelFieldVal _
           & " ORDER BY " & strOrderBy

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql)

    ' Loop through related fields to build comma separated list
    Do While Not rs.EOF
        strOut = strOut & rs.Fields(strField1) & ": " & rs.Fields(strField2) & strSeparator
        rs.MoveNext        
    Loop

    rs.Close

    ' Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If _
        lngLen > 0 _
    Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler

End Function

You'll then be able to use this function in an SQL statement.
The first and second arguments are your 2 fields that you want to concatenate in to the comma-separated list; these are passed as strings so use double-quotes.
The third and forth argument is the the field whose value is the same across the records you're trying to summarise (in your case it's PipeID). Note that argument 3 needs to be in double-quotes "PipeID" and argument 4 is the sql-reference field, so mustn't be in quotes tblTvObservations.PipeID.

The fifth argument is the field you've specified in either strField or strField2 that you want the comma separated list to be ordered by.
The sixth and final argument is the table/query name where this data comes from.
Here's an example of it used in an sql query...

SELECT tblTvObservations.PipeID, ConcatRelated("TVObservation","NumberOf","PipeID",tblTvObservations.PipeID,"TVObservation","tblTvObservations") AS NumberOf
FROM tblTvObservations;

...to get the following result:

Matt Hall

Got this down thanks to Matt Hall on Stackoverflow, posting answer here in case people are interested.