What is the best way please

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

We have a table of contacts with fields FirstName and Surname with usually
between 2 and 4 contacts per company.

What we would like to achieve is to bring the first contacts FirstName and
Surname into a memo field, followed by the same information for contact 2
etc.

What is the best way to do this please - using Access 2002

TIA
Tom
 
I'm not understanding exactly what you want, but you can use a query to
concatenate values from different fields into a single value. Put a
calculated field in the query the produces your desired result:

MyContacts: [FirstName] & " " & [Surname]
 
Ken


If I can re-explain - in a contacts table there is a FirstName and Surname
for each contact. The table contains between 2 and 4 contacts per company.

What we want to achieve is to take the FirstName and Surname from the 1st
record & enter it into a memo field. If there is a 2nd record to enter the
same information into the memo field after the entry for the 1st record & to
repeat this process for 3rd & 4th records

Tom
Ken Snell said:
I'm not understanding exactly what you want, but you can use a query to
concatenate values from different fields into a single value. Put a
calculated field in the query the produces your desired result:

MyContacts: [FirstName] & " " & [Surname]

--

Ken Snell
<MS ACCESS MVP>

Tom said:
We have a table of contacts with fields FirstName and Surname with
usually between 2 and 4 contacts per company.

What we would like to achieve is to bring the first contacts FirstName
and Surname into a memo field, followed by the same information for
contact 2 etc.

What is the best way to do this please - using Access 2002

TIA
Tom
Click to expand...
Click to expand...
 
We have a table of contacts with fields FirstName and Surname with usually
between 2 and 4 contacts per company.

What we would like to achieve is to bring the first contacts FirstName and
Surname into a memo field, followed by the same information for contact 2
etc.
Click to expand...

And then when the third contact in the list gets married and changes
her name, and the second contact leaves the company, and a new first
contact joins the company but needs to be first...? This redundant
memo field will be a nightmare to keep up to date!

Instead, I'd suggest that you just dynamically concatenate the names
as needed. See

http://www.mvps.org/access/modules/mdl0004.htm

for some sample code which can be adapted to do this.

John W. Vinson[MVP]
 
You want to put all the contacts for a single company into one memo field?
For what purpose? As John Vinson points out elsethread, this storing of
redundant data is only going to lead to errors in the future.

What will you be doing with the memo field after you put all the information
into it? Use it for display on a form or report? Export it to a file for
another system to use? Use it as a second storage location of data?

What you seek to do can be done via a VBA function that Duane Hookom, ACCESS
MVP, has written, if you are bound and determined to do this.
http://www.rogersaccesslibrary.com/...Generic+Function+To+Concatenate+Child+Records'

There also is another VBA function to do this at The ACCESS Web:
http://www.mvps.org/access/modules/mdl0004.htm

--

Ken Snell
<MS ACCESS MVP>


Tom said:
Ken


If I can re-explain - in a contacts table there is a FirstName and Surname
for each contact. The table contains between 2 and 4 contacts per company.

What we want to achieve is to take the FirstName and Surname from the 1st
record & enter it into a memo field. If there is a 2nd record to enter the
same information into the memo field after the entry for the 1st record &
to repeat this process for 3rd & 4th records

Tom
Ken Snell said:
I'm not understanding exactly what you want, but you can use a query to
concatenate values from different fields into a single value. Put a
calculated field in the query the produces your desired result:

MyContacts: [FirstName] & " " & [Surname]

--

Ken Snell
<MS ACCESS MVP>

Tom said:
We have a table of contacts with fields FirstName and Surname with
usually between 2 and 4 contacts per company.

What we would like to achieve is to bring the first contacts FirstName
and Surname into a memo field, followed by the same information for
contact 2 etc.

What is the best way to do this please - using Access 2002

TIA
Tom
Click to expand...
Click to expand...
Click to expand...
 
Back
Top