If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design question re duplicate fields
I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six different phone numbers. In all the examples I have looked at, they have all separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields: PhoneType1 = Business Phone1 PhoneType2 = Business 2 Phone2 PhoneType3 = Business Fax Phone3 PhoneType4 = Mobile Phone4 As you can see above that Phone type is repeated over and over again. There should be no duplicates phone numbers for a company. The phone numbers needs to be in one column in order to sort, filter, and find. Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it. Any suggestions? Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...blesdbde sign |
#2
|
|||
|
|||
Table design question re duplicate fields
Dear friend,
Since some of the contacts may have 1 tel. no, some others 2 and so on I would suggest that you must create a separate table for storing those tel. nos. This should be related One to Many to your contacts table. You may have in this table the following fields: ContactID (which is the primary key of your contact table) Tel.No TypeOfTelNo - might be a drop down menu. I would also advice you that you set as primary keys the first 2 fields in case your contact have only one tel. no for each tel. category, or in contrary all the three fields. Hope this helps, GeorgeCY Ο χρήστης "lmcc007" *γγραψε: I am using Access 2007. I am trying to create a job searching database. The problem I am having is the phone number field. Sometimes there may be six different phone numbers. In all the examples I have looked at, they have all separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields: PhoneType1 = Business Phone1 PhoneType2 = Business 2 Phone2 PhoneType3 = Business Fax Phone3 PhoneType4 = Mobile Phone4 As you can see above that Phone type is repeated over and over again. There should be no duplicates phone numbers for a company. The phone numbers needs to be in one column in order to sort, filter, and find. Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it. Any suggestions? Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...blesdbde sign |
#3
|
|||
|
|||
Table design question re duplicate fields
Hi,
simply create 2 extra tables, a lookup table similar to: tlkpPhonetypes ------------------- fields: ptID (autonumber) = PK; ptDescription (text) = Business, private, mobile, ..... and a link table to link the persons to their phone numbers like: tblPersonsPhoneNr -------------------- fields: ppnID (autonumber) = PK; ppnPerson (long integer or same filed type as the PK of your persons table) = FK to your table persons; ppnPhoneType (long integer) = FK to tlkpPhonetypes.ptID; ppnPhoneNr (text): the field containing the actual phone nr if usefull you can add a remarks field where you can note things like: "only use this number from 9 to 5 " Hope this helps a bit -- Kind regards Noëlla "lmcc007" wrote: I am using Access 2007. I am trying to create a job searching database. The problem I am having is the phone number field. Sometimes there may be six different phone numbers. In all the examples I have looked at, they have all separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields: PhoneType1 = Business Phone1 PhoneType2 = Business 2 Phone2 PhoneType3 = Business Fax Phone3 PhoneType4 = Mobile Phone4 As you can see above that Phone type is repeated over and over again. There should be no duplicates phone numbers for a company. The phone numbers needs to be in one column in order to sort, filter, and find. Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it. Any suggestions? Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...blesdbde sign |
#4
|
|||
|
|||
Table design question re duplicate fields
Thank you George! Yes, I did this. I'm sorry I posted this twice. I tried
to delete it but I do not see how to delete this extra post. "George" wrote: Dear friend, Since some of the contacts may have 1 tel. no, some others 2 and so on I would suggest that you must create a separate table for storing those tel. nos. This should be related One to Many to your contacts table. You may have in this table the following fields: ContactID (which is the primary key of your contact table) Tel.No TypeOfTelNo - might be a drop down menu. I would also advice you that you set as primary keys the first 2 fields in case your contact have only one tel. no for each tel. category, or in contrary all the three fields. Hope this helps, GeorgeCY Ο χρήστης "lmcc007" *γγραψε: I am using Access 2007. I am trying to create a job searching database. The problem I am having is the phone number field. Sometimes there may be six different phone numbers. In all the examples I have looked at, they have all separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields: PhoneType1 = Business Phone1 PhoneType2 = Business 2 Phone2 PhoneType3 = Business Fax Phone3 PhoneType4 = Mobile Phone4 As you can see above that Phone type is repeated over and over again. There should be no duplicates phone numbers for a company. The phone numbers needs to be in one column in order to sort, filter, and find. Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it. Any suggestions? Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...blesdbde sign |
#5
|
|||
|
|||
Table design question re duplicate fields
Thank you! I am created lookup tables. Oh, I am sorry I posted this twice.
I was not able to figure out how to delete this extra post. For some reason, my computer is very slow when accessing this discussion group. "Noëlla Gabriël" wrote: Hi, simply create 2 extra tables, a lookup table similar to: tlkpPhonetypes ------------------- fields: ptID (autonumber) = PK; ptDescription (text) = Business, private, mobile, ..... and a link table to link the persons to their phone numbers like: tblPersonsPhoneNr -------------------- fields: ppnID (autonumber) = PK; ppnPerson (long integer or same filed type as the PK of your persons table) = FK to your table persons; ppnPhoneType (long integer) = FK to tlkpPhonetypes.ptID; ppnPhoneNr (text): the field containing the actual phone nr if usefull you can add a remarks field where you can note things like: "only use this number from 9 to 5 " Hope this helps a bit -- Kind regards Noëlla "lmcc007" wrote: I am using Access 2007. I am trying to create a job searching database. The problem I am having is the phone number field. Sometimes there may be six different phone numbers. In all the examples I have looked at, they have all separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields: PhoneType1 = Business Phone1 PhoneType2 = Business 2 Phone2 PhoneType3 = Business Fax Phone3 PhoneType4 = Mobile Phone4 As you can see above that Phone type is repeated over and over again. There should be no duplicates phone numbers for a company. The phone numbers needs to be in one column in order to sort, filter, and find. Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it. Any suggestions? Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...blesdbde sign |
Thread Tools | |
Display Modes | |
|
|