Many data in one Column

[Alexandre]

Hi,
Example:
In a form, I have Names and Phone number. A name can have
10 Phone numbers and for a phone number, it can have a
many names.
I want that when I search a Name, all his phone number
appears. I can make an entry for each phone number in my
table.
This is correct but if I want to search the name from a
phone number there is a problem. If I search the phone
number "555-5555" , I have to enter it in every 10 entries
and then filter.

I can make an other table like : alex 111-111
alex 222-222
george 111-1111

But what doesn't work with that is that if I search for
alex, it will give me two response instead of one with two
phone numbers.

Maybe for a name having many Phone Number in the table but
I don't think it is possible to do that. Even with the
choice list, I can only select one.

What can I do?
Thanks a lot

 

[Tim Ferguson]

In a form, I have Names and Phone number. A name can have
10 Phone numbers and for a phone number, it can have a
many names.
I want that when I search a Name, all his phone number
appears. I can make an entry for each phone number in my
table.

Normal m-to-m relationship; needs three tables[1]:

People(*PersonID, FullName, etc...)

Numbers(*PhoneID, SwitchboardCode, DialNumber, IsAFax, etc...)

BelongsTo(*PersonID, *PhoneID)



Two queries: this one gives you the numbers for a person:

SELECT SwitchboardCode, DialNumber
FROM Numbers INNER JOIN BelongsTo
ON Numbers.PhoneID = BelongsTo.PhoneID
WHERE BelongsTo.PersonID = 10334

and this one gives you the people at a given number:

SELECT FullName
FROM People INNER JOIN BelongsTo
ON People.PersonID = BelongsTo.PersonID
WHERE BelongsTo.PhoneID = 2330

and so on.

Hope that helps


Tim F