Designating one record as the primary one

[BruceM]

I'm not sure in which group I should post this, and I had a hard time coming
up with a descriptive subject line. I have asked about this topic before,
and thought I had come up with a solution, but it seems not.

Here is the situation. I have a Contacts table and a related PhoneNumber
table (Contact ID is the PK/FK). The phone numbers are displayed on a form
in a list box (lstPhone), which gets its data during the form's Current
event:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " & Me.ContactID

Phone is a text field, and ContactID a number (autonumber).

I need to designate one number for each contact as the primary number. I
know I can do this with a yes/no field (I will call the field
PrimaryNumber), but there are two considerations. When there is a single
phone number I want it to be the primary one automatically. The other
consideration is that there be only one primary phone number for a contact.
I need a way of preventing two numbers for one contact from both being
designated as the primary one. I could use the Default Value for the
PrimaryNumber (yes/no) field to designate the first record entered as the
primary one. If it is the only record, fine; if not, the user is able to
designate another number as the primary one. The trouble with default value
for the PrimaryNumber field is how to turn it off if the user is entering a
second number (since both can't be primary). Another issue is that if
another number is designated as primary, the previous primary number needs
to have that designation removed (i.e. the PrimaryNumber field needs to be
set to No). The user can be informed by way of a message box.

The logic when entering a phone number (through a pop-up form) is something
like this:
If this is the first number entered for this contact, designate it at the
primary one, since it may be the only number. If there are other phone
numbers for this contact, one of which is designated as primary, clear the
designation from that number and assign it to this one instead, and notify
the user of the change.

The reason for wanting to do this, in case it matters, is that I generate a
report that includes the phone number. The record source for that report is
a query that combines the Contacts table and the PhoneNumber table. If
there are two phone numbers for a contact then there are two records in the
query, and the contact appears twice on the report. I want the contact to
appear just once; to accomplish this I intend to specify True as the
criteria for PrimaryNumber in the report's record source query.

 

[Tom Lake]

I'm not sure in which group I should post this, and I had a hard time

coming up with a descriptive subject line. I have asked about this topic
before, and thought I had come up with a solution, but it seems not.

Here is the situation. I have a Contacts table and a related PhoneNumber
table (Contact ID is the PK/FK). The phone numbers are displayed on a
form in a list box (lstPhone), which gets its data during the form's
Current event:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " & Me.ContactID

Phone is a text field, and ContactID a number (autonumber).

Have TWO phone fields in your PhoneNumber table. One [PrimaryPhone]
and one [AlternatePhone] Only fill in the Alternate if it exists. Then, on
your report
do this for the phone number field:

=IIf(IsNull([AlternatePhone]), [PrimaryPhone], [AlternatePhone])

Tom Lake

 

[Klatuu]

Here is one way to do it. Continue to have the child table that has phone
numbers in it, but add a PrimaryPhone field to the contact record. Put the
first one in that field and in the phone table as well. If the user wants to
add an additional phone, just add it to the table. You would also need to
have something on your form that will allow the user to change the primary
phone, when that happens, update the field with the selected phone number.

 

[BruceM]

Thanks for the reply. I'm working on this with a phone number because it's
a relatively simple situation, but uses principles I may be able to apply to
future projects when there are a larger number of related records in the
child table. What I'm saying here is that there may be more than two phone
numbers. If it was never to be more than two I would not have bothered with
the phone number table at all. Rather than have two phone number fields
there I would have just had Phone1 and Phone2 records in the Contacts table.
I may decide to go back to that. Three or more numbers is relatively rare.

I'm not sure in which group I should post this, and I had a hard time
coming up with a descriptive subject line. I have asked about this topic
before, and thought I had come up with a solution, but it seems not.

Here is the situation. I have a Contacts table and a related PhoneNumber
table (Contact ID is the PK/FK). The phone numbers are displayed on a
form in a list box (lstPhone), which gets its data during the form's
Current event:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " & Me.ContactID

Phone is a text field, and ContactID a number (autonumber).

Have TWO phone fields in your PhoneNumber table. One [PrimaryPhone]
and one [AlternatePhone] Only fill in the Alternate if it exists. Then,
on your report
do this for the phone number field:

=IIf(IsNull([AlternatePhone]), [PrimaryPhone], [AlternatePhone])

Tom Lake

 

[Klatuu]

"Three or more numbers is relatively rare."
Really?
count your own.

Mine:
Home
Home Fax
Cell
Work
Work Fax

Thanks for the reply. I'm working on this with a phone number because it's
a relatively simple situation, but uses principles I may be able to apply to
future projects when there are a larger number of related records in the
child table. What I'm saying here is that there may be more than two phone
numbers. If it was never to be more than two I would not have bothered with
the phone number table at all. Rather than have two phone number fields
there I would have just had Phone1 and Phone2 records in the Contacts table.
I may decide to go back to that. Three or more numbers is relatively rare.

I'm not sure in which group I should post this, and I had a hard time
coming up with a descriptive subject line. I have asked about this topic
before, and thought I had come up with a solution, but it seems not.

Here is the situation. I have a Contacts table and a related PhoneNumber
table (Contact ID is the PK/FK). The phone numbers are displayed on a
form in a list box (lstPhone), which gets its data during the form's
Current event:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " & Me.ContactID

Phone is a text field, and ContactID a number (autonumber).

Have TWO phone fields in your PhoneNumber table. One [PrimaryPhone]
and one [AlternatePhone] Only fill in the Alternate if it exists. Then,
on your report
do this for the phone number field:

=IIf(IsNull([AlternatePhone]), [PrimaryPhone], [AlternatePhone])

Tom Lake

 

[BruceM]

Thanks for the reply. Are you saying I should store the number in two
places? That doesn't seem right, somehow. I must say that the theoretical
principles of database design and the logistics of producing a neat and
readable interface are far apart in some situations. I have a number of
situations in which I would like to designate one record as the main one,
but I don't want to allow several records to be so designated.
Unfortunately I will need to solve this problem for other projects, even if
I end up bailing on this one and going back to multiple phone number fields
in the Contacts table.

 

[BruceM]

I replied to your original reply, but when I sent it I noticed this message.
Thank you for your continued interest. I was speaking out of frustration
more than anything else. As I said in the other reply to you, I will need
to solve this eventually, but I need to get this project up and running. I
had imagined that designating one (and only one) record with a check box
would be simple enough, but I'm starting to think that is not the case.

"Three or more numbers is relatively rare."
Really?
count your own.

Mine:
Home
Home Fax
Cell
Work
Work Fax

Thanks for the reply. I'm working on this with a phone number because
it's
a relatively simple situation, but uses principles I may be able to apply
to
future projects when there are a larger number of related records in the
child table. What I'm saying here is that there may be more than two
phone
numbers. If it was never to be more than two I would not have bothered
with
the phone number table at all. Rather than have two phone number fields
there I would have just had Phone1 and Phone2 records in the Contacts
table.
I may decide to go back to that. Three or more numbers is relatively
rare.

I'm not sure in which group I should post this, and I had a hard time
coming up with a descriptive subject line. I have asked about this
topic
before, and thought I had come up with a solution, but it seems not.

Here is the situation. I have a Contacts table and a related
PhoneNumber
table (Contact ID is the PK/FK). The phone numbers are displayed on a
form in a list box (lstPhone), which gets its data during the form's
Current event:
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE ContactID = " & Me.ContactID

Phone is a text field, and ContactID a number (autonumber).

Have TWO phone fields in your PhoneNumber table. One [PrimaryPhone]
and one [AlternatePhone] Only fill in the Alternate if it exists.
Then,
on your report
do this for the phone number field:

=IIf(IsNull([AlternatePhone]), [PrimaryPhone], [AlternatePhone])

Tom Lake

 

[Klatuu]

There are times when denormalization makes sense. The other solution would
require a boolean field in each record in the phone table to designate the
primary phone. The issue here is that for each report or query where you
want to show the primary number only, you would require an additional join
with a WHERE.

I actually don't see a problem with this design. you would be carrying an
additional field for each record in the phone table that is of limited value.

 

[BruceM]

A boolean field is exactly what I would like to use, with a wrinkle.
Suppose the following in the Phone table:

tblPhoneNumber
PhoneID (autonumber PK)
ContactID (FK)
PhoneNumber
Primary (boolean)

If there are two or more records with the same ContactID, I want to assure
that only one has the boolean field marked as True. Further, I want to
assure that at least one record is marked as Primary for each Contact.
Again, I ask you to consider that this is one version of something that I
would like to implement on another project with data other than phone
numbers. There could be 20 or more records, so while having a few phone
number fields in the Contacts table is not necessarily a big problem, it
would not work at all for the other project with a larger and variable
number of records.

I'm trying to imagine something like an update query if the record count is
more than one for a given ContactID. If somebody tries to check the Primary
box the check boxes would all be cleared for that ContactID, and the current
record would then be checked. I don't know if that's a good choice, and I
don't know how to do it even if it is possible, but I'm done for the day,
and will resume thinking about this tomorrow.

Again, I thank you for your interest in and attention to my questions.

 

[Klatuu]

I don't disagree with your concept at all. I was only offering an
alternative for consideration.
The man who taught me to program would give me the specs and require that I
come back with at least 3 ways to do it, pick the one I thought was best, and
be able to defend my choice. (just a little aside)

My first thought as to how to keep track of which is the current primary,
would be to use a combo box. When a user wants to change the primary
selection, you can use a combo to see what the current selection is, and
change it if desired.

Without writing the code, the concept is.
Make the combo a 2 column combo, one column for the data, and one for the
boolean field. Use the data column as the bound column and hide the boolean
column.
When you first present the combo, create your rowsource on the phone table
filtered on the primary key of the master record. You can make the record
that is currently the primary record the selected item in the combo. Then,
when the user makes a selection, update the two records involved, that is,
the old primary and the new primary.

Now, being a minimalist (read lazy), I would design it so there is only one
modal form with one combo and call it with paramters to tell it what to use
for a row source, etc.

 

[CoryPerkins]

I've run into the same problem. One solution I came up with was to run and
update statement on the other records and set their primary flag to false
once one was updated to true. On the first record you should be able to
check that there aren't any other records and set it's primary to true.

After doing this for some things, I think it's probably a better solution to
follow what Klatuu has described. Having a field on the parent table for the
primary and updating it accordingly.

Anyway, just my thoughts.

 

[BruceM]

Sorry I didn't get back to you sooner, but the day suddenly got crazy. I
see what you're saying, up to a point. One way I could put the primary
record at the top of the combo box list is by using ORDER BY the MainPhone
field in the row source. The part that I can't figure out is how to update
the other record. In other words, how do I clear the MainPhone field (my
new name for it after I checked and found that Primary is a reserved word)
for the record that was formerly designated as the main phone number?
Perhaps I'm over-complicating this, or maybe I'm just not seeing the point,
but it's been a long week.