concatenate

[Guest]

We have a database that keeps track of equipment. The equipment number is a
constant, and we have a field where they can enter notes. So some pieces of
equipment may have 2 records because when they enter notes, they create a new
record. Some may have 10. Is there a way to concatenate the notes field so
we can have one record (instead of 2 or 10) for each piece of equipment, and
show all the notes for that equipment number?

 

[Joseph Meehan]

We have a database that keeps track of equipment. The equipment
number is a constant, and we have a field where they can enter notes.
So some pieces of equipment may have 2 records because when they
enter notes, they create a new record. Some may have 10. Is there a
way to concatenate the notes field so we can have one record (instead
of 2 or 10) for each piece of equipment, and show all the notes for
that equipment number?

The problem is the basic database design.

You need one table for the equipment and what ever details you want to
record about them, like serial number, location color HP or whatever. You
also will want a comments table that will link to the equipment table via a
unique Equipment ID number. Each note will be a new record in the comments
table. Any given piece of equipment may have from zero to over a million
notes.

You can display the notes in a subform so when viewing the equipment
information you can see all the notes about it at the same time.

 

[Dirk Goldgar]

We have a database that keeps track of equipment. The equipment
number is a constant, and we have a field where they can enter notes.
So some pieces of equipment may have 2 records because when they
enter notes, they create a new record. Some may have 10. Is there a
way to concatenate the notes field so we can have one record (instead
of 2 or 10) for each piece of equipment, and show all the notes for
that equipment number?

Normally you would have one table foe equipment, with one record for
each equipment number (probably EquipmentNumber would be the primary
key), and another table for "EquipmentNotes", with one record per note,
and with a EquipmentNumber as what is called a "foreign key". Then
you'd use a main form/subform arrangement to present the data: main
form based on the Equipemt table, with a subform in continuous or
datasheet view, based on the EquipmentNotes table. So the subform
always shows you the notes realated to the piece of equipment that is
current on the main form. An arrangement like this lets you manipulate
the notes separately, but at the same time see all the notes related to
a particular piece of equipment.

If you really don't want to do it that way, then you *could* have a memo
field in the equipment record, and add each note as a new line in that
one field. Making the conversion would probably take a little VBA code,
or else you could export all the notes to a text file, open the text
file in Notepad, and copy and paste groups of lines into a text box
bound to the memo field. I still think the main form/subform apprach is
better for most purposes.

 

[Guest]

Thanks for the responses. Actually, the database is set up this way, but
what we are trying to do is export the information into an Excel spreadsheet.
When we create a query with the two tables, we end up with multiple rows for
each piece of equipment because of the multiple note "records". We would
like the equipment number to only appear once, with the notes all in one
field. Does this make sense? Thanks for your help!

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/modules/mdl0008.htm (or
http://www.mvps.org/access/modules/mdl0004.htm) at "The Access Web" for a
function that will let you do that.

 

[Dirk Goldgar]

Thanks for the responses. Actually, the database is set up this way,
but what we are trying to do is export the information into an Excel
spreadsheet. When we create a query with the two tables, we end up
with multiple rows for each piece of equipment because of the
multiple note "records". We would like the equipment number to only
appear once, with the notes all in one field. Does this make sense?

Sure, it's not uncommon to be forced to denormalize your database for
export to another application. I suggest you follow the following link:

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

Copy the fConcatChild() function on that web page, and paste it into a
standard module -- a new one, or one that you already have in your
database for such utilities. That function will, as it says, "return a
concatenated list of sub-record values". You can use it in a query
based on the Equipment table alone, to return a concatenated list of all
the related records from the EquipmentNotes table. Just export that
query to your Excel spreadsheet.

You may want to modify the function so as to use the vbCrLf character
combination (or maybe just vbCr; I'm not sure what Excel may require)
as the delimiter, instead of the semicolon that it is set up to use.