Table clean up 2

[jtfrier]

I have a table that has two main fields one called part# and the other des for decription my table has mutipal part# with dec that that are defrent I need to have one part # and the decription field des need to be combinded
Ex what I want it to look like
part# des
1234 pincle, .09, red, black

what it looks like
part# des
1234 pincle
1234 .09
1234 red
1234 black

 

[PHV]

Have a look here: faq701-4233


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jtfrier]

that looks right but looks like its in sql im not experinced enough to convert it any tips its been long time since i have writen one line of code

 

[jtfrier]

im working in MS Access

 

[lespaul]

did you follow the instructions?

[tt]To use any function like this, open a new module. Copy the code from "Function Con..." to "End Function" into the new module. Select Debug|Compile to make sure there are no compile errors. Then save the module as "modConcatenate". You can then use the Concatenate() function as an expression in a query or control source or other places.[/tt]

then you call it in a query like:

SELECT Part#, Concatenate("SELECT Des FROM YourTableName WHERE Part# =" & [Part#]) as DescriptionsWithCommas FROM YourTableName

That's it....two steps, copy and call.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jtfrier]

ok thanks that help

 

[jtfrier]

im geting error when runing this here is the coad im using for the link above

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") As String

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)<--------here is were the error shows up
       
    Dim strConcat As String
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

 

[PHV]

Which error ?
How does the query call this function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jtfrier]

the error i get says Data type mismatch in criteria expression

 

[jtfrier]

SELECT Partno, Concatenate("SELECT Desc FROM Edmanmanruby1 WHERE Partno =" & [Partno]) AS Descriptions
FROM Edmanmanruby1;

 

[PHV]

If Partno is not defined as numeric:

SELECT Partno, Concatenate("SELECT Desc FROM Edmanmanruby1 WHERE Partno=[!]'[/!]" & [Partno][!] & "'"[/!]) AS Descriptions
FROM Edmanmanruby1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jtfrier]

it runs with out errors but it still keeps duplicat part numbers any ideas

 

[jtfrier]

thank you sooooo much it worked great!!!!!!!!!!!!!