Curious Key Question...

[sawilliams]

I have to alter a table that holds phone numbers and add a checkbox to indicate that a phone number is "preferred". Only ONE phone number can be preferred. So if I make the key be the customer_no and the preferred checkbox, of course Access rebels because a customer could have three phones, one preferred and two not so I can't make that combination the Key. So, how to I create some sort of toggle that allows a user to check the preferred box on only one phone number? And if the preferred number changes from home to business, checking the business phone preferred checkbox unchecks the home phone checkbox. I realize I've kind of drifted from tables to forms so I'm not sure where I start to solve the problem...

 

[rjoubert]

I'm assuming the user is presented with a form with which they can select the preferred phone number. Instead of using checkboxes on the form, use a radio button group. Radio buttons only allow for one selection per grouping.

 

[sawilliams]

I thought of that but here's the thing. I have a tabbed input form with a subform that displays the phone numbers and associated info. The user clicks a command button on the subform to open another form to add (or edit) phone info. We store lots of info about phones that does not need to display in the tabbed input form (like phone-note, assistant's name, day or evening, &c.) So, when adding or editing phone info, they are only looking at one record at a time. Further, there can be an unlimited number of phones so I'm not sure how to set up an option group for that.

 

[rjoubert]

When the user attempts to save a new number for an existing customer, do a DLookup to find out if a preferred number already exists for that customer. If so and the user attempts to save the new number as a preferred one, pop up an error message stating that a preferred number already exists for this customer.

 

[WildHare]

This is a tricky situation, that somewhat depends on the structure of your DB. You say that you can have an unlimited number of phone numbers, which leads me to believe that the numbers need to be in their own separate table, FK'ed back to the 'owner' table:

MainTable
*Keyfield
OtherInfo
...
etc etc etc

PhoneNumberTable
*PhoneNumber
*MainTable.KeyField
etc etc etc


So you have a decision - do you store the "preferred" chunk of data at the MainTable level, or the PhoneNumberTable level?

If you store it at the MainTable Level, then you need to either store that phone number up there (duplicated, not a good idea), or store some pointer value that will point to the number in the PhoneNumberTable, which is getting dangerously close to the "dog-chasing-his-tail" syndrome..

So I think it's best to store the tag for the Preferred number at the PhoneNumberTable level.

So now our PhoneNumberTable look like:

PhoneNumberTable
*PhoneNumber
*MainTable.KeyField
Preferred? (Yes/No Boolean)

The problem here is obviously, how do we keep > 1 Phone number from being preferred, and does one number in the set of MY phone numbers HAVE to be set preferred? Two rules there then, that need to be handled.

On a form, an option group of Radio Buttons would seem to be the only direct way of handling this, but you'd need some code behind the change event of the group on your form, and that would only work for data maintenance done with this form.

What you have is an example that illustrates the lack of table-level 'triggers' or other validation rules in Jet. Given the above structure, the slickest code on a form won't help if you open the PhoneNumberTable directly and check every phone number as Preferred=YES...

I'm almost positive that we can come up with a solution to this problem - I'm going to see if I can work something up in the next few hours, and then probably PHV or someone will chime in just as I finsh, with a one-line solution that solves the whold dam' thing..










--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[BobRodes]

Well, a rather simple solution would be to have a "preferrednumber" table, sort of like in rjoubert's second post. The phone number field would be the primary key. You might want to have the customerid field, with a secondary index as well.

Then, if a user clicks a preferred number radiobutton, you look up the old selection in this table, delete it, and add the new one. If there isn't an old selection, you just add the new one. (You might want to check first if there's a record in the table with the current customerid, which is why you might want to keep the customerid field. But it seems to me you might be able to get away with not doing so.)

How's that sound?

Bob

 

[sawilliams]

All these are excellent ideas, but I may have designed myself into a box. The phone numbers are displayed in a subform (a continuous form) on a tabbed input form. Since the number of rows displayed vary with the number of phone numbers, I'm not sure how to go about adding a "radio button group". For now, I've just done some conditional formatting to highlight any "preferred" phone and have a message box pop up when the "preferred" checkbox is updated to remind the user to limit the "preferred" to just one phone.

 

[BobRodes]

Ok. Not an Access expert, so I can't speak to tabbed input forms very well. However, any list which is capable of multiple selections will allow you to disable or alter those selections through code. So, use the customerid, and when a user alters a selection, check to see if another of the customerid's phones has been selected, and if so deselect it. Otherwise, just allow the new selection to stand as is.

 

[pbrodsky]

You could have a descriptor with each phone number (home, office, mobile, etc.) and use that descriptor as a fk in the customer table.

Since there are an indefinite number of phone numbers, a list box is probably better for selection a primary than a radio button group.

 

[BobRodes]

Why do you need a descriptor as a fk, given that the number itself is by definition unique?

 

[jsteph]

If you're willing to put some extra work into it, this is a possible case for the 'associative' table. This is a 3 table system:

Customer
CustiD -- Key
blah, blah

Phone
PhoneID --Key
PhoneNum

Now the important table:
PhoneCust
Custid -- Key
PhoneID -- Key
PhoneType (cell, fax, main, etc)
...And the important field:
Pref
Data type: text l-char.
Validiation rule "X" OR IS NULL
Index: Composite INDEX on Custid,Pref UNIQUE--IGNORE NULLS

Here, only one customer can have a PREF value of X, all the rest of their entries can be NULL. The Unique/ignorenulls allows this.

This allows form customers to have many phone numbers, and the same phone number to be used by many customers.
Example:
We have, say Midas Muffler shops as our customers--hundreds of them. In each record we want a 'Corporate' number. Why type the same number into 1000 customer records, then when that changes, do the same? In the Phone table there is 1 number with an ID, and that ID is paired with 1000 Midas shops' customerID's in the associative table.
--Jim

 

[BobRodes]

Using a constriction/rule like this looks like a good idea, jsteph, and probably performs faster than maintaining a separate table of which numbers are preferred.