Attempting to combine duplicate entries into one

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
KARL DEWEY said:
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.
Click to expand...

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.
 
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];
 
Back
Top