How do I combine 3 rows of same field name into one field in Access?

[greyhawke]

If I have:

Part No. Desc
1x9090 Ball bearing
1x9090 1-1/4" diameter

how can I combine the two lines of desc (number of lines will vary) into one field OR

how do I show the multi-lines of desc on a report and only show the part number once.

Thanks.

Caron.

 

[jbehrne]

Hi,

You can use a "Make Table" query or "Update Table" query. Both should combine the values into a recordset of your creation. Hope this helps,

jbehrne

 

[MichaelRed]

there is a property in report controls re hiding duplicates. set to some positive sounding value (like yes | true). Of course this doesn't necessarily order the remaining fields to (read) in any particular order, so:

Part No.   Desc
1x9090     Ball bearing
           1-1/4" diameter

and

Part No.   Desc
1x9090     1-1/4" diameter
           Ball bearing

are (more or less) equally likely


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[fneily]

I also posted this in the other forum Microsoft Office for you.

I made a table with two fields - partno, desc. Desc can span more than one record, eg. partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field.

create a query that looks like this:

SELECT [partno], fConcatFld("parttable","partno","desc","string",[partno]) AS descs
FROM parttable
GROUP BY [partno];

Do ALT+F11, insert a new module and put the following code for the fConcatFld function:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'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 DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb


loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = '" & vForFldVal & "' ;" 'place this line on one line in the VBA ' window.

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

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


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

Run the query, which calls the function. In your VBA window, click on Tools , then References and make sure the DAO library is referenced first.

neil





In