Best way for Multiple Phone Number for 1 Record

[bignose2]

Access 2007
Hi,

What is the best way to have multiple phone numbers etc for one records

For years stuck with my format
Home1
Home2
Contact
Mobile1
Mobile2

But increasingly people have 3 or 4 mobiles etc. I also do the same with emails e.g. email1, email2 so problem getting worse.

It would be nice to be able to have a few or as many numbers for each phone or email category.
e.g.
Home
012345 111222
012355 333444
Contact
Mobile
07770 123456
07770 987654
07770 555444

I am trying to keep simple without big reprogamming and wondered about Multivalued fields. I know not good programming but hoped might be simple.

It might be easier that I think to have separate linked tables but just seems excessive to have 4 or so completely separate tables holding just numbers. But again searching or having subforms on a form messy, I know could prob populate a listbox or something but must be an easier way.

I do search for phone numbers & emails & do quite a lot of linking tables with especially emails.

Very broadly at the moment I use if PhoneNo = Home1 then do X or if PhoneNo = Home2 then do X.

Tested a little with MVFields but think from results not really what desiened for what I want.
All records seem to what to have the same data. If I enter 01234 56788 in records 1, all the subsequent fields have the same. I can manually go through and "edit list", delete or change but think this must not be right.

I guess this MVF is more designed for every records to have the SAME 4 or 5 fields and you select from those but I am sure I saw a DB using it in a different manner.

I the MVF is a simple possibility and you have some advise on the basics I was also wondering about..
I would prefer to have 2 columns
07770 555666 : Mr Smith
07770 777888 : Mrs Smith
I have tried for hours to have 2 columns when making this lookup field but any time more than 1 col, I cannot edit list, either in the table or in a control on a form, simply no option there, right click, NO edit list, just search.

Also if MVF is a option, advise on query to copy/move Mobile1 (Normal text field) to Mobile (MVF). Somehow go through and add to first record of the MVF & then run again for mobile2 to the 2nd record etc. I suspect will need some VBA but just in case easier that I think.

Any ideas please

Thanks I/A

 

[llkhoutx]

Use a linked table, possibly a list box, combo box or subform for multiple telephone numbers. It's not rocket science.

 

[Ranman256]

It would be MUCH easier to make a child table and have infinite phones for 1 record.
And easy to query. That's what SQL is for.

 

[Galaxiom]

I am trying to keep simple without big reprogamming and wondered about Multivalued fields.

Multivalue fields are not suited. They are meant for a limited number of values that are used on multiple records.

It might be easier that I think to have separate linked tables but just seems excessive to have 4 or so completely separate tables holding just numbers.

You only need one extra table. The Home, Mobile, Work etc is indicated by another field value.

 

[bignose2]

Thanks for all your help,

I had looked at this a few times before and using another table but just did not seem to fall in to place, Don't know why I am generally pretty good at most of this stuff but do tend to dip in & out of modifying the program but now seems blindingly obvious.
After 1 hour the basics are done & running well & better than before.

Using child table & subform, might use a list box but can minimalize the subform enough to be tidy on the main form.

Thanks again.