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 |
#11
|
|||
|
|||
Contact Mgmt fields
This just came to mind: that is create a query that would search on those
fields--that is Primary Phone, Secondary Phone... Now I need to figure out how to do a form that all I have to do is type a phone number in and it will find it no matter which field it is store in. "Fred" wrote: We make and use these. I hate to say it, but IMHO separate colums in the main table is usually the better choice although searchability by phone number is one of the minuses, as is reduced flexibility to handle people who have too many phone numbers, or having to put seldom-used columns in to accomodate seldom used scenerios. (like secondary cell phone) The upside is that such would make a lot of other things simpler and easier. IMHO as long as you follow unique definitions of those phone columns, then such a structure is not un-normalized. (i.e. "Primary Phone" , " Secondary Phone" , "Primary Cell" "Secondary Cell" "Fax #" are 5 distinct attributes/definitions) |
#12
|
|||
|
|||
Contact Mgmt fields
Don't mistake me for an expert in this area, (those other guys who answered know Access 10 times better than me (they'd do better at answering) but here's a starting point from down on my lower level. Make a query which puts [EnterPhoneNumber] under each phone number field but on different lines on the grid. It will generate sql like SELECT People.PrimaryPhone, People.SecondaryPhone, People.Fax, People.Cell FROM People WHERE (((People.PrimaryPhone)=[EnterPhoneNumber])) OR (((People.SecondaryPhone)=[EnterPhoneNumber])) OR (((People.Fax)=[EnterPhoneNumber])) OR (((People.Cell)=[EnterPhoneNumber])); This will make [EnterPhoneNumber] a variable which it will ask the user for upon opening the query and will return all records where it matches any of the fields. You can code the form to do this but for me that takes 15 minutes of head scratching rather than being something I could just write here. |
#13
|
|||
|
|||
Contact Mgmt fields
Hey Duane,
I went back over your instruction and got it to work. Two problems: 1) I cannot change the PhoneType, and 2) I cannot tab from PhoneNumber to the next PhoneNumber, I have to use the mouse. And, I tried Fred's way "Make a query which puts [EnterPhoneNumber] under each phone number field but on different lines on the grid." This works too! Basically, I just want to be able to enter a phone number to find a record, and have the form look the way I want it to. I am still thinking about just having a continuous subform that allows me to select a phone type and enter the phone number. The only reason I am stuck on this way is normalization. I want to set the database up right. I tried a couple of off the shelf contact management programs like Act and they have separate fields for phone numbers. When I searched I had to search each phone type for a number. Hmmm! Will I damage the database if I do it with Phone 1, Phone 2, Phone 3...? "Duane Hookom" wrote: I think I have provided a solution in the past to either you or someone much like you with the same question. You could use four text boxes with the PhoneTypeID hard-coded in the control source. Then use four subforms that link to the Primary key of the main form and one of the text boxes. For instance is your PhoneTypeID is 1 for "Business", add a hidden text box Name: txtBusiness Control Source: =1 Then use a single view subform with link values: Link Master: CustomerID;txtBusiness Link Child: CustomerID; PhoneTypeID You can add a label with a caption of "Business" -- Duane Hookom Microsoft Access MVP "lmcc007" wrote: Thanks! I created a form with the three fields: PhoneNumID (Visible = No) PhoneTypeID (Combo box) PhoneNumber (Text box) Is there a way for the form to always display Business, Business Fax, Mobile, Home whether there is a phone number availble or not? I am trying to get the form to look like Phone numbers------------------------------------- Business 800-123-4567 Business Fax Mobile 888-888-1111 Home I like this standard look. "Tom van Stiphout" wrote: On Tue, 16 Jun 2009 17:23:01 -0700, lmcc007 wrote: You are doing it right. Database designers call this "normalization" which is the process of removing "repeating groups" by spinning off the data in its own table. The reason some are doing this is for convenience, but yours is the better approach from a db design perspective. For the user interface you need to think of a subform with two columns. The form is bound to the Phone table, with a dropdown for PhoneType (gets its rowsource from the PhoneTypes table) and a textbox for the PhoneNumber value. -Tom. Microsoft Access MVP What is the best or appropriate way of creating my contact mgmt database regarding phone numbers? Every example and template I look at all have separate fields for Business, Busines Fax, Mobile Phone, Home Phone... I'm trying it with a table (PhoneType and PhoneNumber) and only enter the phone numbers if availalbe. I'm wondering am I on the right track or should I do like all the examples and templates I see? And, another reason I created a PhoneNumbers table because I would like to be able to search for phone numbers. And, I do want my form to always display Business, Business Fax... whether there is a phone number available for the contact person or not. |
#14
|
|||
|
|||
Contact Mgmt fields
Thanks Fred,
Tried it and it works. Basically, all I want to do is enter a phone number to pull up a record. Is there a huge disadvantage doing it this way versus creating a continuous subform? I guess you can tell I am stuck on getting that particular look for my form (you know, Business, Business 2, Business Fax, Mobile). "Fred" wrote: Don't mistake me for an expert in this area, (those other guys who answered know Access 10 times better than me (they'd do better at answering) but here's a starting point from down on my lower level. Make a query which puts [EnterPhoneNumber] under each phone number field but on different lines on the grid. It will generate sql like SELECT People.PrimaryPhone, People.SecondaryPhone, People.Fax, People.Cell FROM People WHERE (((People.PrimaryPhone)=[EnterPhoneNumber])) OR (((People.SecondaryPhone)=[EnterPhoneNumber])) OR (((People.Fax)=[EnterPhoneNumber])) OR (((People.Cell)=[EnterPhoneNumber])); This will make [EnterPhoneNumber] a variable which it will ask the user for upon opening the query and will return all records where it matches any of the fields. You can code the form to do this but for me that takes 15 minutes of head scratching rather than being something I could just write here. |
#15
|
|||
|
|||
Contact Mgmt fields
The big disadvantage of having a separate column for each phone number is the
effort involved when you want to add another category of phone. You must change your forms, queries, and reports to account for the new field. With a separate table, you can have a contact with 1 or 10 phone numbers, and if you decide you want to add another PhoneType category, all you have to do is add it to your PhoneTypes table. It takes a while to get used to using subforms, but they really do make the whole process a lot simpler in the long run. Dale lmcc007 wrote: This just came to mind: that is create a query that would search on those fields--that is Primary Phone, Secondary Phone... Now I need to figure out how to do a form that all I have to do is type a phone number in and it will find it no matter which field it is store in. We make and use these. I hate to say it, but IMHO separate colums in the main table is usually the better choice although searchability by phone [quoted text clipped - 9 lines] -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#16
|
|||
|
|||
Contact Mgmt fields
To avoid confusion you really need to think/discuss this as a structure
question and deal with forms afterwards. There basically two structures discussedin the above thread: A. A separate phone table with a many-to-one relationship to your people table as discussed by the other guys B. Multiple phone columns for multiple phone types in your main people table as discussed by me. I listed certain conditions (basically that there are unique meanings & definitions for each phone number field) under which "B" is an OK choice. If they are not met, then in my opinion, "A" is the only OK choice. If we assume that "B" met the conditions for "OK", and it's still in the running, then it's a matter of choice, and my choice if we were doing it here would be "B", despite it's downsides, one of which would be to make your (unusual) described/desired searching job more complex. "B" is probably the only viable way of meeting your desire of providing the titles and blanks for phone numbers which have not been entered. Now you need to decide between "A" and "B" When you design the likely forms, "A" would have "People" in the main form, and a "phones" subform. "B" would have a "People" main form (with all of the phone number types and phone numbers) and no sub-form. Hope that helps a little. |
#17
|
|||
|
|||
Contact Mgmt fields
Actually a separate table would be best, I just want that form to be
displayed a certain way. I tried the following code to get it to display the way I want on the form, but I am not sure I should go this way: Private Sub PhoneTypeCommandButton_Click() DoCmd.GoToRecord , , acNewRec PhoneTypeName = "Business" DoCmd.GoToRecord , , acNewRec PhoneTypeName = "Business 2" DoCmd.GoToRecord , , acNewRec PhoneTypeName = "Business Fax" DoCmd.GoToRecord , , acNewRec PhoneTypeName = "Mobile" End Sub I pretty much want to know the correct way of setting up the db. I don't want to just put it together anyway "Dale_Fye via AccessMonster.com" wrote: The big disadvantage of having a separate column for each phone number is the effort involved when you want to add another category of phone. You must change your forms, queries, and reports to account for the new field. With a separate table, you can have a contact with 1 or 10 phone numbers, and if you decide you want to add another PhoneType category, all you have to do is add it to your PhoneTypes table. It takes a while to get used to using subforms, but they really do make the whole process a lot simpler in the long run. Dale lmcc007 wrote: This just came to mind: that is create a query that would search on those fields--that is Primary Phone, Secondary Phone... Now I need to figure out how to do a form that all I have to do is type a phone number in and it will find it no matter which field it is store in. We make and use these. I hate to say it, but IMHO separate colums in the main table is usually the better choice although searchability by phone [quoted text clipped - 9 lines] -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#18
|
|||
|
|||
Contact Mgmt fields
A. would be the choice I think I should choose but I am stuck on what I want
the form to display. "Fred" wrote: To avoid confusion you really need to think/discuss this as a structure question and deal with forms afterwards. There basically two structures discussedin the above thread: A. A separate phone table with a many-to-one relationship to your people table as discussed by the other guys B. Multiple phone columns for multiple phone types in your main people table as discussed by me. I listed certain conditions (basically that there are unique meanings & definitions for each phone number field) under which "B" is an OK choice. If they are not met, then in my opinion, "A" is the only OK choice. If we assume that "B" met the conditions for "OK", and it's still in the running, then it's a matter of choice, and my choice if we were doing it here would be "B", despite it's downsides, one of which would be to make your (unusual) described/desired searching job more complex. "B" is probably the only viable way of meeting your desire of providing the titles and blanks for phone numbers which have not been entered. Now you need to decide between "A" and "B" When you design the likely forms, "A" would have "People" in the main form, and a "phones" subform. "B" would have a "People" main form (with all of the phone number types and phone numbers) and no sub-form. Hope that helps a little. |
|
Thread Tools | |
Display Modes | |
|
|