Concatenate Scenario 1

[air1jcw]

I have a table I am working with structed like so:

All are text fields.

FieldA FieldB FieldC FieldD FieldE etc...
112 X X X
113 X
114 X X
115 X X X

I would like for this to be concatenated into 1 field, wether FieldB, FieldC, FieldD or FieldE are null or not. I can get this to semi-sorta work with expr: [FieldB]& "," & [FieldC]& "," & [FieldD] & "," in a select query, but all I get is X,,,X, or X,X,X,, or ,,X,X or what have you. I sure hope this makes sense!! I would like for it to work off of a horizontal "isnotnull" thing AND not show the comma if one or more of the fields is null.

Can do this is a select query or module and use the query to call the code in the module?? Oh yeah - I will need some examples as well!!!

Thank you in advanced!!

 

[dhookom]

Oh yeah, we need some examples as well! Based on your records displayed in your message, what would you want the result to look like? If you remove the commas, how would you know which field contained the "X"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[air1jcw]

The end result would could look like:

FldA FldB FldC FldD FldE New field

112 A300 757 767 A300,757,767
114 727 727
115 757 767 757,767
116 A300 727 757 767 A300,727,757,767

Does this make better sense? Please advise if not!!

Air

 

[dhookom]

Looks like you have a seriously un-normalized database. However, you can try:
NewField: [FldB] & (IIf(Len(FldB & "&quot=0,"",",&quot+[FldC]) & (IIf(Len(FldB & FldC & "&quot=0,"",",&quot+[FldD]) & (IIf(Len(FldB & FldC & FldD & "&quot=0,"",",&quot+[FldE])

I think I got this correct but you may need to play with it some.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

The Table

FldA	FldB	FldC	FldD	FldE	NewFld
112	A300		757	767	
114		727			
115			757	767	
116	A300	727	757	767

The Function

Public Function basHorzConCat(ParamArray MySet() As Variant) As String

    Dim Idx As Long
    Dim MyStr As String

    While Idx <= UBound(MySet)
        If (Not IsNull(MySet(Idx))) Then
            MyStr = MyStr & MySet(Idx) & &quot;,&quot;
        End If
        Idx = Idx + 1
    Wend

    basHorzConCat = Left(MyStr, Len(MyStr) - 1)

End Function

The Query(SQL)
SELECT tblTstFlds.FldA, tblTstFlds.FldB, tblTstFlds.FldC, tblTstFlds.FldD, tblTstFlds.FldE, basHorzConCat([FldA],[FldB],[FldC],[FldD],[FldE]) AS MyNewFld
FROM tblTstFlds
WITH OWNERACCESS OPTION;


The Resultx

FldA	FldB	FldC	FldD	FldE	MyNewFld
112	A300		757	767	112,A300,757,767
114		727			114,727
115			757	767	115,757,767
116	A300	727	757	767	116,A300,727,757,767

Although it (your structure) MAY be denormalized, there is no reason you cannot generate the "string" you asked for.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[air1jcw]

MichaelRed,

Yeah - I don't know what all that "denormalized" is all about??

Anyhow - Thank you very much!!! What you provide worked great!!
How do I get spaces inbetween??

From 727,747,757 to 727, 747, 757

Air

 

[dhookom]

Air,
A denormalized table:
tblStudentClasses
======================
StudentID
Period1
Period2
Period3
Period4
Period5
...
Where each "Period" field would contain a subject or class code.
A normalized table would look more like:
tblStudentClasses
==================
StudentID
PeriodNumber
ClassID
If a student was enrolled in six classes, there would be six records in a normalized table rather than one record in an un-normalized table.

Since I am not aware of your exact table and usage, I can only guess. Your tables may not be normalized. However fields with values like you suggest, might be un-normalized.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

I'm not sure what you mean by " ... spaces inbetween ... ".





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CosmoKramer]

Michael,

I think he is looking for a comma and " " in your code:

While Idx <= UBound(MySet)
        If (Not IsNull(MySet(Idx))) Then
            MyStr = MyStr & MySet(Idx) &

", "

        End If
        Idx = Idx + 1
    Wend

Hoc nomen meum verum non est.

 

[MichaelRed]

If so, then the function assignment statement also needs to be modified.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[air1jcw]

MichaelRed & CosmoKramer -

Sorry for the confusion and I appreciate the extra effort. I solved the space issue. I was wanting a space after each comma: 727,757,767, to 727, 757, 767, etc...

Perhaps you can help me with another issue of concatenating.
I am using a code, maybe you have seen it before.

I keep getting an error during the running of the select query. A box pops up saying Error #13 Type mismatch.
This locks up the whole d/base.

Any fixes??

Here is the SQL & the code:

SQL - SELECT IID, fConcatFld("ALLMPNs","IID","MPN","string",[IID]) AS PNs
FROM ALLMPNs
GROUP BY IID;

Code -

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb

loSQL = "SELECT [" & stFldToConcat & "] FROM ["
loSQL = loSQL & stTable & "] WHERE "

Select Case stForFldType
Case "String":
loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
Case Else
GoTo Err_fConcatFld
End Select

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot;; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.DESCRIPTION
Resume Exit_fConcatFld
End Function

 

[dhookom]

Air,
I prefer the concatenate function found at faq701-4233. There is a sample database at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

These function will concatenate normalized data similar to my previous example data of students and classes.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[air1jcw]

dhookom,

Is there a way to do this without a primary key and with out numeric data type? I have a good &quot;linking&quot; field to use between 2 tables - it is a text field. And that field resides in both tables. If this don't work...

If there is an other way - The data I am wanting to concatenate is all in the same table. If I have to I will use 2.

Any suggestions??

Thanks in advance!!

air.

 

[MichaelRed]

Somewhat ambigious, but:

The value of [IID] can evaluate to Null. If this occurs, the SQL string will have a Where clause with no 'argument' Attempting to execute it will generate an error.

A more probable issue depends on the Usage of ADO vs. DAO recordset(s). If you have both references, you need to qualify which you indend in the declarations. If you are using ADO only, the code is incorrect, as the OpenRecorset does not apply (this can also occur if both are referenced -but DAO is not included in the declarations).





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dhookom]

air,
The sample that I provided can be used with a linking text field. The SQL String you provide would use something like:
&quot;... WHERE [TextField]=&quot;&quot;&quot; & [TextField] & &quot;&quot;&quot;&quot;

You don't need two tables. If you create a simple totals query, it doubles as a &quot;parent&quot; table. Base your concatenate query on the parent query and reference the details table within the SQL string.

If you need further help, come on back with your table structure, a few sample records, and desired output.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[air1jcw]

dhookom,

I tried the &quot;... WHERE [TextField]=&quot;&quot;&quot; & [TextField] & &quot;&quot;&quot;&quot;
and the concatenate field returns null. I am not getting any errors messages. Just a null field.

Heres the SQL I am working with:
SELECT EffectivityMAIN.IID, Concatenate(&quot;SELECT MPN FROM mpns WHERE [IID] =&quot;&quot; & [IID] & &quot;&quot;&quot AS MPNs
FROM EffectivityMAIN;

I am not understanding what you are sayin' about creating a simple totals query??

Thanks for your help - I am getting closer!!
air.

 

[air1jcw]

dhookom,

I should give this info to ya - it might help you to help me!!

Table structure Query results
The way I would like it
fldIID fldMPN fldIID fldMPNs
112 AA 112 AA, AB
112 AB 113 BB
113 BB 114 CC, CD, CE
114 CC
114 CD
114 CE

Once again IID is a text field and no primary keys

Air.

 

[dhookom]

Assuming your table name is EffectivityMAIN, create a totals query named &quot;qgrpIID&quot;:
SELECT IID
FROM EffectivityMAIN
GROUP BY IID;

Then create another query:
SELECT IID,
Concatenate(&quot;SELECT MPN FROM EffectivityMAIN WHERE IID = &quot;&quot;&quot; & IID & &quot;&quot;&quot; ORDER BY MPN&quot as MPNs
FROM qgrpIID;


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[air1jcw]

dhookhom,

Thank you very much for your help!! Your code examples and d/base example is awesome and is exactly what I need.

Thanks!!

Air.