Grouping by a multi-value field

[Jon]

I have a contact database with a multi-value field for signers of
correspondence (multiple people can sign a single letter). I would like to
group contact lists by the multi-value field without getting a line for each
signer. For example, if the value of the multi-value field was "Smith,
Jones" I'd like the contact to be shown on only one line of the list with
"Smith, Jones" as the group. Can this be done.

Any help would be greatly appreciated

 

[Jeff Boyce]

Jon

It is possible (not a good idea but possible) to store more than one fact in
a field. When you say "a multi-value field", what comes to mind is a series
of values, separated by, oh, I don't know, commas?<g>

Which version of Access are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

It's 2007.

Jon

It is possible (not a good idea but possible) to store more than one fact in
a field. When you say "a multi-value field", what comes to mind is a series
of values, separated by, oh, I don't know, commas?<g>

Which version of Access are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Dale

(that was my assumption, too, but just in case...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

Dale,
You've described what I have done. The problem I'm having is using the field
for grouping. If the contents of the multi-value field is "Smith, Jones" for
example, grouping on that field yeilds a report with two lines for a single
record.

 

[Dale Fye]

Jon,

When you group on a multi-value field, you won't get a "Smith, Jones" group,
you will get "Smith", and "Jones", since these are actually individual values.

Multi-value fields are a bad idea. They violate the first rule of
relational database design (store no more than one piece of information in a
field in a record), and make it easy for stuff like this to happen.

What you need (and what Access has provided without your knowledge) is
another table, in which you would store data for your one-to-many
relationship. Access has basically done this for you, but has hidden the
additional table.

Instead of using a multi-value field, you would need to use a subform to
enter data in that field.

Yes, it is a little more complicated, but it makes sure you understand your
data structure. Multi-valued field just tend to confuse the uninitiated.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.