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 duplicates 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? |
#2
|
|||
|
|||
Table design question re duplicates fields
On Thu, 7 May 2009 10:37:01 -0700, 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? You're using a relational database: use it relationally! One Company has multiple Phones: a one to many relationship. Create a *second table*, Phones, with fields PhoneID: Autonumber primary key CompanyID: foreign key to your current table's primary key PhoneType: Text, "Business", "Home", etc. etc., probably from a very small one field PhoneTypes table Phone: Text, the actual phone number You can create a unique two-field Index on CompanyID and Phone to prevent duplicates. I question whether you might need multiple Contacts (people with whom to communicate) at each Company though, and have the phone table linked to Contacts rather than to Companies. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Table design question re duplicates fields
Yes, I did just that. Company and PhoneNumber are my primary keys.
The problem is I want my form to always display Business, Business 2, Business Fax, and Mobile with all the values being stored in the PhoneNumber field. Contacts -- I am not sure about that because the people I contact are inside the company--like, human resource manager, secretary, supervisor... And, their addresses are usually the same except with very big companies like Exxon, Shell... "John W. Vinson" wrote: On Thu, 7 May 2009 10:37:01 -0700, 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? You're using a relational database: use it relationally! One Company has multiple Phones: a one to many relationship. Create a *second table*, Phones, with fields PhoneID: Autonumber primary key CompanyID: foreign key to your current table's primary key PhoneType: Text, "Business", "Home", etc. etc., probably from a very small one field PhoneTypes table Phone: Text, the actual phone number You can create a unique two-field Index on CompanyID and Phone to prevent duplicates. I question whether you might need multiple Contacts (people with whom to communicate) at each Company though, and have the phone table linked to Contacts rather than to Companies. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Table design question re duplicates fields
On Thu, 7 May 2009 16:09:01 -0700, lmcc007
wrote: Yes, I did just that. Company and PhoneNumber are my primary keys. The problem is I want my form to always display Business, Business 2, Business Fax, and Mobile with all the values being stored in the PhoneNumber field. I'm not sure I understand. You'ld use a Subform based on Phones on the form, displaying the phone number and the phone type fields. What do you mean by "all the values being stored in the PhoneNumber field"? There should only be one. Contacts -- I am not sure about that because the people I contact are inside the company--like, human resource manager, secretary, supervisor... And, their addresses are usually the same except with very big companies like Exxon, Shell... So? They might all have the same ADDRESS but surely they each have their own phone. I have a set of tables CONtblPeople ContactID LastName FirstName etc CONtblAddresses AddressID autonumber PK ContactID Address1 Direction Street Suffix ' e.g. St., Ave., Blvd. City State ' also used for Province, etc. PostCode CONtblPhones PhoneID autonumber PK ContactID PhoneType Phone -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Table design question re duplicates fields
The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber field. I'm not sure I understand. You'ld use a Subform based on Phones on the form, displaying the phone number and the phone type fields. What do you mean by "all the values being stored in the PhoneNumber field"? There should only be one. I want my form to look like Outlook Contact Manager form. You know: Phone Numbers Business 800-833-1212 Business 2 Business Fax 800-833-1213 Mobile Using the subform it will only display if there is data and if no data it will not display Business, Business 2... I am reading Access Basics for Programming by Crystal I got off of Allen Browne's website. I haven't finished yet but maybe it will be address here. Thanks! "John W. Vinson" wrote: On Thu, 7 May 2009 16:09:01 -0700, lmcc007 wrote: Yes, I did just that. Company and PhoneNumber are my primary keys. The problem is I want my form to always display Business, Business 2, Business Fax, and Mobile with all the values being stored in the PhoneNumber field. I'm not sure I understand. You'ld use a Subform based on Phones on the form, displaying the phone number and the phone type fields. What do you mean by "all the values being stored in the PhoneNumber field"? There should only be one. Contacts -- I am not sure about that because the people I contact are inside the company--like, human resource manager, secretary, supervisor... And, their addresses are usually the same except with very big companies like Exxon, Shell... So? They might all have the same ADDRESS but surely they each have their own phone. I have a set of tables CONtblPeople ContactID LastName FirstName etc CONtblAddresses AddressID autonumber PK ContactID Address1 Direction Street Suffix ' e.g. St., Ave., Blvd. City State ' also used for Province, etc. PostCode CONtblPhones PhoneID autonumber PK ContactID PhoneType Phone -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Table design question re duplicates fields
On Thu, 7 May 2009 18:02:06 -0700, lmcc007
wrote: I'm not sure I understand. You'ld use a Subform based on Phones on the form, displaying the phone number and the phone type fields. What do you mean by "all the values being stored in the PhoneNumber field"? There should only be one. I want my form to look like Outlook Contact Manager form. You know: Phone Numbers Business 800-833-1212 Business 2 Business Fax 800-833-1213 Mobile Using the subform it will only display if there is data and if no data it will not display Business, Business 2... Ah, ok. Base the subform not on Phones but on a Query left joining PhoneTypes to Phones: SELECT PhoneTypes.PhoneType, Phones.PhoneType, Phones.Phones FROM PhoneTypes LEFT JOIN Phones ON PhoneTypes.PhoneType = Phones.PhoneType ORDER BY PhoneTypes.PhoneType; This will display all types whether or not there's a phone to match. You do need to include the PhoneType field from both tables in order to have the query updateable, but you needn't display it in the subform. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Table design question re duplicates fields
I could not get the query to work. And, I sort of didn’t understand it. I
need it to be very basic. Anyway, after trying it so many times I messed up the database, so I started over with a new database and created two tables this time. tblCompanies CompanyID AutoNumber CompanyName Text tblPhoneNumbers PhoneNumberID Autonumber Business Text Business2 Text BusinessFax Text Mobile Text CompanyID Number If I did it this way, will I be able to sort, filter, and find the phone numbers without having to search each field? I know this way I will not be able to add additional phone numbers unless I add more fields, which was what I was trying not to do. I wish there was a way to upload the db or post a picture of it. "John W. Vinson" wrote: On Thu, 7 May 2009 18:02:06 -0700, lmcc007 wrote: I'm not sure I understand. You'ld use a Subform based on Phones on the form, displaying the phone number and the phone type fields. What do you mean by "all the values being stored in the PhoneNumber field"? There should only be one. I want my form to look like Outlook Contact Manager form. You know: Phone Numbers Business 800-833-1212 Business 2 Business Fax 800-833-1213 Mobile Using the subform it will only display if there is data and if no data it will not display Business, Business 2... Ah, ok. Base the subform not on Phones but on a Query left joining PhoneTypes to Phones: SELECT PhoneTypes.PhoneType, Phones.PhoneType, Phones.Phones FROM PhoneTypes LEFT JOIN Phones ON PhoneTypes.PhoneType = Phones.PhoneType ORDER BY PhoneTypes.PhoneType; This will display all types whether or not there's a phone to match. You do need to include the PhoneType field from both tables in order to have the query updateable, but you needn't display it in the subform. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Table design question re duplicates fields
On Thu, 7 May 2009 19:41:05 -0700, lmcc007
wrote: I could not get the query to work. And, I sort of didn’t understand it. I need it to be very basic. Anyway, after trying it so many times I messed up the database, so I started over with a new database and created two tables this time. tblCompanies CompanyID AutoNumber CompanyName Text tblPhoneNumbers PhoneNumberID Autonumber Business Text Business2 Text BusinessFax Text Mobile Text CompanyID Number If I did it this way, will I be able to sort, filter, and find the phone numbers without having to search each field? I know this way I will not be able to add additional phone numbers unless I add more fields, which was what I was trying not to do. You're still making the same mistake. Fields are expensive... records are cheap. Your tblPhoneNumbers should not have one field for each phone number; it should have ONE RECORD for each phone number. Try two tables: tblPhoneTypes PhoneType (Text, Primary Key) Seq (Number) Fill it with the various phone types you want, with Seq containing a number which will define the sort order. tblPhoneNumbers PhoneNumberID Autonumber Primary Key CompanyID Number (joined to Companies in the relationship window) PhoneType Text (joined to tblPhoneTypes in the relationship window) Phone Create a unique two field index on CompanyID and Phone. If someone has three phones, they would have three records in tblPhoneNumbers. If they have only one phone they would have only one record. Create a new Query. Don't pick any tables; instead copy and paste the following SQL into the SQL window: SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType, tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType ORDER BY tblPhoneTypes.Seq; Save this query, and use it as the recordsource for a Subform on your company form, using CompanyID as the master/child link field. Make it a continuous form; put textboxes for tblPhoneTypes.PhoneType but set it to Enabled = No, Locked = Yes so users can't change your master phone type table. Put another textbox for Phone. You'll see as many rows as there are phone types; if there's a Mobile phone for the company, you'll see that phone number in the phone field. If you type in a new phone number next to the Business 1 phone type, Access will copy the PhoneType value from tblPhoneTypes to that record in tblPhoneNumbers, maintaining the link. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Table design question re duplicates fields
Hi John,
I followed your instructions but the form doesn’t display the Business, Business 2, Business Fax, and Mobile. If there is no phone number for one of these fields, it leaves the phone type blank. I tried it again and track each step. They are as follows: 1. Created main form with one field: CompanyName 2. Saved as frmCompanies 3. Created subform by using the Control Wizard. 4. Used existing Tables and Queries. 5. Next 6. Query: Query1 7. Available fields choices: tblPhoneTypes.PhoneType Phone CompanyID 8. Next 9. Choose from a list: Show Query1 for each record in tblCompanies using CompanyID 10. Next 11. What name would you like for your subform? Query1 subform 12. Finish 13. Open Property Sheet to look at Query1 subform 14. Under Data tab Source Object: Query1 subform Link MasterField CompanyID Link Child Field CompanyID 15. Close Form 16. Opened Query1 subform 17. Property Sheet Default view = Continuous Forms 18. First Text Box = tblPhoneTypes.PhoneType 19. Went to Data tab Control Source = tblPhoneTypes.PhoneType Enable = No Locked = Yes 20. Second Text Box = Phone 21. Close 22. Opened frmCompanies 23. Query1 subform displays a text box, which is blank because I haven’t entered a phone number for this company. 24. Went to second record where there is a phone number for company, so here two text boxes are displayed. One with the number and the other one is empty. For example: Business 832-888-1919 ____________ 25. The PhoneType text boxes do not display Business, Business 2, Business Fax, Mobile. Where did I go wrong? I am glad you understand what I am trying to do, but maybe I missed a step or something. What do you think? Thanks a bunch!!! "John W. Vinson" wrote: On Thu, 7 May 2009 19:41:05 -0700, lmcc007 wrote: I could not get the query to work. And, I sort of didn’t understand it. I need it to be very basic. Anyway, after trying it so many times I messed up the database, so I started over with a new database and created two tables this time. tblCompanies CompanyID AutoNumber CompanyName Text tblPhoneNumbers PhoneNumberID Autonumber Business Text Business2 Text BusinessFax Text Mobile Text CompanyID Number If I did it this way, will I be able to sort, filter, and find the phone numbers without having to search each field? I know this way I will not be able to add additional phone numbers unless I add more fields, which was what I was trying not to do. You're still making the same mistake. Fields are expensive... records are cheap. Your tblPhoneNumbers should not have one field for each phone number; it should have ONE RECORD for each phone number. Try two tables: tblPhoneTypes PhoneType (Text, Primary Key) Seq (Number) Fill it with the various phone types you want, with Seq containing a number which will define the sort order. tblPhoneNumbers PhoneNumberID Autonumber Primary Key CompanyID Number (joined to Companies in the relationship window) PhoneType Text (joined to tblPhoneTypes in the relationship window) Phone Create a unique two field index on CompanyID and Phone. If someone has three phones, they would have three records in tblPhoneNumbers. If they have only one phone they would have only one record. Create a new Query. Don't pick any tables; instead copy and paste the following SQL into the SQL window: SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType, tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType ORDER BY tblPhoneTypes.Seq; Save this query, and use it as the recordsource for a Subform on your company form, using CompanyID as the master/child link field. Make it a continuous form; put textboxes for tblPhoneTypes.PhoneType but set it to Enabled = No, Locked = Yes so users can't change your master phone type table. Put another textbox for Phone. You'll see as many rows as there are phone types; if there's a Mobile phone for the company, you'll see that phone number in the phone field. If you type in a new phone number next to the Business 1 phone type, Access will copy the PhoneType value from tblPhoneTypes to that record in tblPhoneNumbers, maintaining the link. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Table design question re duplicates fields
If you haven't entered additional phone numbers (with their phone types) for
that company, nothing should be displayed. It occurred to me that you might be expecting to see those other ones as "blanks" with the phone type listed and a blank place for the phone number (like you did in your previous structure) |
Thread Tools | |
Display Modes | |
|
|