How can I make a report using multiple names from one field?

[Sherman6789]

I have a database where one of the fields lists Point of Contact (POC) names. There can be from one to four names in the POC field and they are separated commas. I have already made several types of reports. The only one which is giving me trouble is the report that is in order by "Points of Contact" names from the same field. How can I make this report which groups all of the records that list contacts in order. Duplicates are acceptable.
EXAMPLE:

SAMPLE DATABASE WITH THREE FIELDS
Rec. ITEM Contact Name(s)
1. A John
2. D John, Beverly
3. T David, Patricia, Beverly
4. M Beverly
5. P David, Patricia, Beverly, John


SAMPLE EXPECTED REPORT (by Contact Names)

****************
BEVERLY
2 D
3 T
4 M
5 P
****************
DAVID
3 T
5 P
****************
JOHN
1 A
2 D
5 P
****************
PATRICIA
3 T
5 P
****************

Partial Solutions?
1. Make a table of all Contacts and somehow use it to search for records containing the names anywhere in the POC field.

2. Divide the contact list into four fields to make it easier to search somehow.

I will appreciate any help. thanks.

WRSherman

 

[dhookom]

That's the penalty for placing multiple values in a single field. I would start by creating a table of all unique contacts. Then create a query with the contacts table and your "SAMPLE DATABASE WITH THREE FIELDS". Type the criteria under the nasty multiple value field to:
Like "*" & [NewContactField] & "*"
This will create the records you need.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You really should fix your tables, this is clearly a poor non normalized design. However adding additional fields is even a poorer design.

Each name should be its own record and relate back to another table.

If you can not fix the table structure you can do this in a query. This is just a work around to show that it is possible, but not recommended. Normalize the data is the preferred method.

Build a function to determine if a name is in a list of names. Build a table of each name.

Public Function nameInList(theName, theList) As Boolean
  Dim I As Integer
  Dim splitNames() As String
  If Not IsNull(theName) And Not IsNull(theList) Then
    splitNames = Split(theList, ",")
    For I = LBound(splitNames) To UBound(splitNames)
      If Trim(splitNames(I)) = Trim(theName) Then nameInList = True
    Next I
  End If
End Function

Then you can use it in a query
SELECT
tblNames.personName,
tblContacts.item,
tblContacts.contacts,
nameInList([personName],[contacts]) AS InList
FROM tblNames, tblContacts
WHERE
nameInList([personName],[contacts]))=True
ORDER BY tblNames.personName;

this gives me output like
personName item contacts
Beverly B Paticia, Beverly
Beverly A John, Beverly
David E David, John
David D David
John E David, John
John C John
John A John, Beverly

 

[dhookom]

I'm not sure why you need a function when you can set up the criteria to use a simple:

SELECT 
 tblNames.personName, 
 tblContacts.item, 
 tblContacts.Rec,
 tblContacts.contacts, 
FROM tblNames, tblContacts
WHERE 
 Contacts Like "*" & [PersonName] & "*"
ORDER BY tblNames.personName;

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I'm not sure why you need a function

Just to be safe.

I do not know all the names, but I assumed you could get in trouble with derivatives. If I had the 4 different people in my contact list the simple query fails.

John,John-Patrick,David,Davidson

 

[Sherman6789]

Thanks to both dhookom and MajP.

I agree that it is best to not have multiple names in the same field. There are far more names and the database has many more fields than on this sample. The names are first and last name. This sample database is only to show how it is set up.

That particular field is only to let the reader know the names of all persons who may be contacted about a particular record. That is why they are in one field. I was told to also make one printout which is sorted by contacts so that they can see all of the records each contact person is associated with no matter where they fall on the list in the contact field. It would be a problem even if the names were in alphabetical order. Like you said, this can cause a problem and I agree with the other problem caused by derivatives. If I create a list of contacts in a table for comparisons, it requires that each name be typed at lease twice. The search must be able to distinguish between names using commas as separators. Thanks for the help everyone. -WRS