How to summarize the fields of the table this way?

[jamal numan]

Review this Allen Browne tutorial http://allenbrowne.com/func-concat.html

Thank you June7 for the help.

Unfortunately, i don't have enough skills to perform this kind of work. I'm looking for having an integrated code that possibly does this sort of thing such that i can stick it in my database and proceed...

May you please have a look on the attached file. the data i have are stored in table14 will the output table that i expect is stuck in table18....is there a simple way to derive this output from the input data?

best



Jamal

[June7]

I just realized your requirement has a different twist that makes it even trickier. You don't want the values repeated. Allen Browne's function will not work without some modification.

You need to develop some programming skills. Start with this

http://office.microsoft.com/en-us/ac...010341717.aspx

And to give you a leg up here is your project and the modified function. Because you have lookup values, had to use a query as the source data for the function.

EDIT: Purpose served, file removed.

[jamal numan]

I just realized your requirement has a different twist that makes it even trickier. You don't want the values repeated. Allen Browne's function will not work without some modification.

You need to develop some programming skills. Start with this

http://office.microsoft.com/en-us/ac...010341717.aspx

And to give you a leg up here is your project and the modified function. Because you have lookup values, had to use a query as the source data for the function.

Thank you June for the colossal efforts. This is amazing...

I tried to apply the code that you have developed on a sample of the actual data but unfortunately it didn’t work…


I got the message “data type mismatch in criteria expression”

The actual data (saved in T5) has the same structure as the data provided in the file M4

What might be the problem?



how to use the code properly with my actual data?


Best

Jamal

[June7]

Ref_B is a number field so don't use apostrophe delimiters.
Elec: ConcatRelated("Electricity","Query1","Ref_B=" & [Ref_B],"Electricity",", ")

Apostrophes are for text, # for dates, nothing for numbers.

In the sample db the corresponding field was text.

[jamal numan]

Ref_B is a number field so don't use apostrophe delimiters.
Elec: ConcatRelated("Electricity","Query1","Ref_B=" & [Ref_B],"Electricity",", ")

Apostrophes are for text, # for dates, nothing for numbers.

In the sample db the corresponding field was text.

Perfect...this is what i have been looking for since ages! you have solved one of the most complicated issues that I'm confronting.

thank you so much June...thank you for your time and efforts. very much appreciated... this is a great work with a brilliant output...

best

Jamal

[jamal numan]

Perfect...this is what i have been looking for since ages! you have solved one of the most complicated issues that I'm confronting.

thank you so much June...thank you for your time and efforts. very much appreciated... this is a great work with a brilliant output...

best

Jamal

Hi June,

I’m again in trouble with the concatenation grouping.

I tried to apply the model that you have previously developed to concatenate the values of a field according to another but sounds not to work this time!

Please, have a look on the error message that I got. If you still remember, you have generously spent a lot of time to concatenate many fields at the same time.

Isn’t this model generic?

I’m attaching screenshots and the mdb file.

Thank you for the help,

Best

Jamal

----------------------------
Option Compare Database

Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant

Dim strData As String

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.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)

'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
If strData <> rsMV(0) Then strOut = strOut & rsMV(0) & strSeparator
If Not rsMV.EOF Then strData = rsMV(0)
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
If strData <> rs(0) Then strOut = strOut & rs(0) & strSeparator
If Not rs.EOF Then strData = rs(0)
End If
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 rsMV = Nothing
Set rs = Nothing
Exit Function

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

---------------------------------------------------------

[June7]

Unfortunately, Allen neglects this one little instruction in his tutorial:

Set a VBA reference to: Microsoft DAO 3.x Object Library

From the VBA editor > Tools > References > find and check the library

The db I gave you would have already had this selected.

[jamal numan]

Unfortunately, Allen neglects this one little instruction in his tutorial:

Set a VBA reference to: Microsoft DAO 3.x Object Library

From the VBA editor > Tools > References > find and check the library

The db I gave you would have already had this selected.

Hi June,

Superb. It works now very well. I do appreciate your generous help.

Cheers

Jamal

[jamal numan]

Unfortunately, Allen neglects this one little instruction in his tutorial:

Set a VBA reference to: Microsoft DAO 3.x Object Library

From the VBA editor > Tools > References > find and check the library

The db I gave you would have already had this selected.

Hi again June,

I tried to apply it to another table but I got the error message:

“Error 3061: too few parameters. Expected 1”

What might be the reason?

The mdb file and screenshots are attached.

Best

Jamal

[June7]

Field F11 is text so the WHERE string needs apostrophe delimiters.

G3: ConcatRelated("F12","T3","F11='" & [F11] & "'","F12",", ")

A date/time field would use # character.

[jamal numan]

Field F11 is text so the WHERE string needs apostrophe delimiters.

G3: ConcatRelated("F12","T3","F11='" & [F11] & "'","F12",", ")

A date/time field would use # character.

Many thanks June for the prompt answer June. You are a star.

Cheers

Jamal