merging fields - how do i do it?

[Neen]

Hi

I am a telemarketer and need help with how to merge fields into one - bear with me i am trying to explain this as fully as i can!

I have essentially 3 main tables
Company (with Company AutoID)
Contacts (with Company ID to link to company and Contact AutoID) and
Calls (with Contact ID to link to contacts and Call AutoID)

Every time i make a call to a particular contact at a company i record a call which adds a record to the calls table (consists of fields: CallID, ContactID, Date, Notes, Action) e.g. Fred Bloggs works for CompanyABC. When I call him, a new call is created in the calls table for each date and call that I make.

So at the moment each call is logged separately. I need to return the data to a client. Instead of them having each call shown separately I somehow need to merge all of the call dates and notes fields into a new Master Calls field.

FOR EXAMPLE At the moment the data returned would look a little like this.

CallID 1, Company ABC, Fred Bloggs 1/1/8 he told me to call in October
CallID 57, Company ABC, Fred Bloggs 10/7/8 Appointment made

I need to return the data so it looks like this
Company ABC. Fred Bloggs, 1/1/8: He told me ot call in October 10/7/8 Appointment Made.

Essentially I need the master notes field to show me the date and the notes of each call (in date order)

CAN ANYONE HELP ME?

x

 

[Uncle Gizmo]

This Thread here may provide some insperation

 

[Neen]

Hi Uncle Gizmo

Right...thanks for your response. i have had a look at that but I dont get it?! I dont do a lot with the SQL stuff, all through queries etc as am self taught. Can you advise at all for me?

Someone told me to do this:
create a name/field for your new notes and create an update query to update from existing fields to this one. E.g. NewNotesField with update to [oldNotes1]&” “&[oldnotes2] &” “& [oldnotes3]

But I dont have the notes fields as oldnotes1, oldnotes2, they are just new records in the call table. When i did this query all i got was the date and notes repeated over and over and over, I need it to move to the next set of notes for that contact. The constant is the contactID