How do I combine 3 records into one in Access?

[greyhawke]

I have extracted information from a database via ODBC. My part number ends up having 3 lines because each line of the description is in a separate record. How can I combine the description into one field instead of three?

 

[Dreamboat]

Is it always in 3 lines? Or do some have 2, some have 5, etc... Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[oakleaf8]

Try this:

Create query in Design View
Close the Show Table Box
Right Click and select SQL View
Paste the following:

SELECT [fldPartNumber1] & " " & [fldPartNumber2] & " " & [fldPartNumber3] AS [Part Number]
FROM tblPartNumber;

Change the three field names to your field names
Change tblPartNumber to your current table name

Right click and go into Query Design. Add all other fields you want in your final table.

Right click, select Query Type, select Make-Table Query. Choose new table name. Run the query and you have a new table with one part number field instead of three.

Hope this helps

 

[greyhawke]

Thanks for the replies.

In response to the first reply from Anne, yes, the number of desc lines will vary.

Maybe an example would help.

Part No. Desc
1x1090 Ball Bearing 1-1/4"
1x1090 diameter

I want one part number field and one desc field. I am using ODBC tables, a query to pull the info together, and a report to prompt for criteria. I would like to be able to accomplish this on the report without having to create a make-table. I already have all my fields on the report except the desc field. I would really appreciate everyone's help.

 

[Dreamboat]

What's the max number of desc lines?

You should be able to do this part in your query, or a query based on your existing query, and put something like this:

=Trim([DescLine1]&" "&[DescLine2]&" "&[DescLine3])

I THINK that TRIM is available in Access, but cannot swear. The trim is to get rid of any spaces that might be left over after they've been concatenated. oalkleaf will probably know this better than me.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[greyhawke]

I feel like I'm being a little thick-headed today, but I'm going to ask anyway....

If all of my desc lines are in the same column (or field-name) what tells Access which is DescLine1 vs DescLine2, etc.?

 

[Dreamboat]

D'oh!

Talk to oakleaf on that. Sorry...I didn't catch it. You may also want to ask in the Access forum instead of here... Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[greyhawke]

Thanks for the input. I'll pose this question to the Access forum.

Have a great weekend!

 

[oakleaf8]

Hmmm... So you already have one part number field and one desc field. What you do have is several records with same Part No. but differing Desc. that need to be combined into one record with Desc. combined? Is this correct?

 

[fneily]

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. Besure in your VBA window, click on Tools , the References and make sure the DAO library is referenced first.

neil

 

[greyhawke]

Oakleaf - yes, that is correct. Same part number, different desc - which is actually coming from a multi-value database so it is making each line of desc from database a separate record with same part number.

Neil - Thanks so much for the detailed example. I will try this and let you know how it goes. It may take a while to get back to you - I wear many hats here and sometimes one has to take priority over the other.

Thank you all for your input.

Caron.