Trouble combining records

[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

 

[Tom Ellison]

Dear Jack:

Why do you feel this is necessary?

What if Customer 5 had made a thousand responses? How is it you expect that
a single row in a table can contain an unlimited amount of information?

The rules of database design are very specific about this. Done properly,
it will work very well, indeed.

This does not mean the information cannot be reported in the fashion you
describe.

Tom Ellison

 

[strive4peace]

Hello Jack,

I am assuming you want to do this for reporting purposes and
not storage reasons...

put this code in a general module so it can be used anywhere

'~~~~~~~~~~~~
Function LoopThroughTableCombineRecords( _
pTablename as string, _
pIDFieldname as string, _
pTextFieldname as string, _
pSomeID as long _
) as string

'crystal
'strive4peace2007 at yahoo dot com

'NEEDS REFERENCE
.Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, s as string
mAllValues = vbNull

s = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pSomeID & ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(s,dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
if not isnull(r(pTextFieldname)) then
mAllValues = (mAllValues + " ") _
& r(pTextFieldname)
end if
r.MoveNext
Loop

Proc_Exit:
'close the recordset
r.close
'release the recordset variable
Set r = Nothing

LoopThroughTableCombineRecords = nz(mAllValues,"")
Exit function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopThroughTable"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End function
'~~~~~~~~~~~~~~

in a query, you can do this:

field --> AllResponses:
LoopThroughTableCombineRecords
(
"RelatedTablename",
"Customer ID",
"Response",
[Customers].[Customer ID]
)

I put the line breaks in to make this easier to read -- do
not use them in your actual equation


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*