Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Attempting to combine duplicate entries into one

Discussion in 'Information Technology' started by Jack, Jul 28, 2006.

  1. Jack

    Jack
    Expand Collapse
    Guest

    Hello,

    I have records that contain "Customer ID" and "Response". There is a record
    for everytime a Customer made a response. So, Customer 5 will have three
    records if they answered X, Y, and Z. How would I combine these records in
    to one record that would only have "Customer ID" 5 and "Response" of X Y Z?

    Thanks in advance!
    Jack
     
  2. Loading...

    Similar Threads Forum Date
    SciTech New Quantum Dot Technique Combines Best of Optical and Electron Microscopy Breaking News Jun 17, 2013
    SAD-Sant Samaj Combine Sweeps SGPC Elections Sikh Sikhi Sikhism Sep 19, 2011
    SciTech 3,000 images combine for Milky Way portrait Breaking News Nov 2, 2009
    Sikh News SAD-BJP combine sweeps civic polls in Punjab (Outlook India) Breaking News Aug 8, 2007

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Jack:

    Do this in a query, don't combine the Response values into one field in a
    row in table. The table would not be in First Normal Form. The present
    table structure is the right one.

    To do it in a query you first need to add a function to a standard module in
    the database, e.g. assuming the table is called Responses:

    Public Function ListResponses(lngCustomerID As Long) As String

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strResponses As String

    strSQL = "SELECT Response FROM Responses " & _
    "WHERE [Customer ID] = " & lngCustomerID

    Set rst = New ADODB.Recordset

    With rst
    .ActiveConnection = CurrentProject.Connection
    .Open _
    Source:=strSQL, _
    CursorType:=adOpenKeyset, _
    Options:=adCmdText

    Do While Not .EOF
    strResponses = strResponses & " " & .Fields("Response")
    .MoveNext
    Loop
    .Close
    ' remove leading space
    strResponses = Mid$(strResponses, 2)
    End With

    Set rst = Nothing
    ListResponses = strResponses

    End Function

    You then call the function in a query which returns DISTINCT values of
    Customer ID, passing the value of each Customer ID into the function:

    SELECT DISTINCT [Customer ID],
    ListResponses([Customer ID]) AS ResponseList
    FROM Responses;

    You can include other tables in the query of course, joined to the Responses
    table, e.g. a Customers table joined on CustomerID.

    Ken Sheridan
    Stafford, England

    "Jack" wrote:

    > Hello,
    >
    > I have records that contain "Customer ID" and "Response". There is a record
    > for everytime a Customer made a response. So, Customer 5 will have three
    > records if they answered X, Y, and Z. How would I combine these records in
    > to one record that would only have "Customer ID" 5 and "Response" of X Y Z?
    >
    > Thanks in advance!
    > Jack
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You did not give an example of your data.
    Are the response records like this--
    Customer ID Response X
    5 Now is the time ..

    Customer ID Response Y
    5 How now brown cow...

    Customer ID Response Z
    5 The quick brown fox...


    Or is it like this --
    Customer ID Response X Response Y Response Z
    5 Now is the time ..
    5 How now brown cow...
    5 The quick brown
    fox...

    If it is the first type then you need to run 3 append queries.
    If it is the latter then use a totals query.

    "Jack" wrote:

    > Hello,
    >
    > I have records that contain "Customer ID" and "Response". There is a record
    > for everytime a Customer made a response. So, Customer 5 will have three
    > records if they answered X, Y, and Z. How would I combine these records in
    > to one record that would only have "Customer ID" 5 and "Response" of X Y Z?
    >
    > Thanks in advance!
    > Jack
     
  5. Jack

    Jack
    Expand Collapse
    Guest

    "KARL DEWEY" wrote:

    > You did not give an example of your data.
    > Are the response records like this--
    > Customer ID Response X
    > 5 Now is the time ..
    >
    > Customer ID Response Y
    > 5 How now brown cow...
    >
    > Customer ID Response Z
    > 5 The quick brown fox...
    >
    >
    > Or is it like this --
    > Customer ID Response X Response Y Response Z
    > 5 Now is the time ..
    > 5 How now brown cow...
    > 5 The quick brown
    > fox...
    >
    > If it is the first type then you need to run 3 append queries.
    > If it is the latter then use a totals query.
    >
    > "Jack" wrote:
    >
    > > Hello,
    > >
    > > I have records that contain "Customer ID" and "Response". There is a record
    > > for everytime a Customer made a response. So, Customer 5 will have three
    > > records if they answered X, Y, and Z. How would I combine these records in
    > > to one record that would only have "Customer ID" 5 and "Response" of X Y Z?
    > >
    > > Thanks in advance!
    > > Jack


    My Table has the Header:

    Customer ID | Response

    The data beneath is:

    5 | X
    5 | Y
    5 | Z

    I would like to create a new Table with the same header:

    Customer ID | Response

    But have the data as such:

    5 | X Y Z

    Thanks again, sorry for not being clear originally.
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    There is probably a much simpler way but I did what I think you want in 3
    queries.

    Table name Cust-Resp

    Query name CUST-RESPONSE
    SELECT [Cust-Resp].[Customer ID], IIf([Response]="X",1,0) AS X,
    IIf([Response]="Y",1,0) AS Y, IIf([Response]="Z",1,0) AS Z
    FROM [Cust-Resp];

    Query name CUST-RESPONSE-1
    SELECT [CUST-RESPONSE].[Customer ID], Sum([CUST-RESPONSE].X) AS SumOfX,
    Sum([CUST-RESPONSE].Y) AS SumOfY, Sum([CUST-RESPONSE].Z) AS SumOfZ
    FROM [CUST-RESPONSE]
    GROUP BY [CUST-RESPONSE].[Customer ID];


    SELECT [CUST-RESPONSE-1].[Customer ID], Trim(IIf([SumOfX]>0,"X ",Null) &
    IIf([SumOfY]>0,"Y ",Null) & IIf([SumOfZ]>0,"Z",Null)) AS Response
    FROM [CUST-RESPONSE-1];


    "Jack" wrote:

    >
    >
    > "KARL DEWEY" wrote:
    >
    > > You did not give an example of your data.
    > > Are the response records like this--
    > > Customer ID Response X
    > > 5 Now is the time ..
    > >
    > > Customer ID Response Y
    > > 5 How now brown cow...
    > >
    > > Customer ID Response Z
    > > 5 The quick brown fox...
    > >
    > >
    > > Or is it like this --
    > > Customer ID Response X Response Y Response Z
    > > 5 Now is the time ..
    > > 5 How now brown cow...
    > > 5 The quick brown
    > > fox...
    > >
    > > If it is the first type then you need to run 3 append queries.
    > > If it is the latter then use a totals query.
    > >
    > > "Jack" wrote:
    > >
    > > > Hello,
    > > >
    > > > I have records that contain "Customer ID" and "Response". There is a record
    > > > for everytime a Customer made a response. So, Customer 5 will have three
    > > > records if they answered X, Y, and Z. How would I combine these records in
    > > > to one record that would only have "Customer ID" 5 and "Response" of X Y Z?
    > > >
    > > > Thanks in advance!
    > > > Jack

    >
    > My Table has the Header:
    >
    > Customer ID | Response
    >
    > The data beneath is:
    >
    > 5 | X
    > 5 | Y
    > 5 | Z
    >
    > I would like to create a new Table with the same header:
    >
    > Customer ID | Response
    >
    > But have the data as such:
    >
    > 5 | X Y Z
    >
    > Thanks again, sorry for not being clear originally.
     

Share This Page