A semi-unique index?

This is the approach I would take as well. But rather than "spin through the
users existing records", I think I would just write an update query that sets
all of the other Preferred values for that user to false.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Matthew" wrote:

Ah, so it'll be done with code in the front end, not at the table level.

Thanks for your help!!!

Matthew

"Dennis" wrote in message
...
Using something like Address as a primary key is not doable. Simply add a
Yes/No field to the table called "PREFERRED" (or whatever you want). If
they
check it, it's a preferred address. Then, when they go to add another
address, if they check Preferred, in the BEFORE UPDATE event, spin though
the
user's existing records looking for the checkbox set in any other one (You
can use DLookup for that). If a YES is found for any other address, popup
a
MSGBOX with the error and CANCEL the Update/add.

"Matthew" wrote:

I feel like there's a simple answer which eludes me.

I'm trying to make it so that a person can only have one preferred
address.

tblPeople
*PersonID
etc.

tblAddresses
*AddressID
PersonID
StreetAddress..
PreferredAddress (y/n)

I've tried creating an index in tblAddresses using the two fields
PersonID
and PreferredAddress, and setting it to Unique and Ignore Nulls.

That makes it so that no person can have two addresses marked as
preferred,
but they also cannot have two addresses NOT marked as preferred.

I want a person to be able to have as many not-preferred addresses as
they
want, but only one of them can be designated as preferred.

How can I do this?

Many thanks in advance!!

Matthew

Except that it would be nice to get a popup box that says, "There is already
a preferred addess defind; would you like to use the new one as preferred?"
(YES/NO)

That lets the user decide what to do. When the computer does "stuff"
automatically, you never really know what's going on under the sheets.

JMHO - YMMV

"Dale Fye" wrote:

This is the approach I would take as well. But rather than "spin through the
users existing records", I think I would just write an update query that sets
all of the other Preferred values for that user to false.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Matthew" wrote:

Ah, so it'll be done with code in the front end, not at the table level.

Thanks for your help!!!

Matthew

"Dennis" wrote in message
...
Using something like Address as a primary key is not doable. Simply add a
Yes/No field to the table called "PREFERRED" (or whatever you want). If
they
check it, it's a preferred address. Then, when they go to add another
address, if they check Preferred, in the BEFORE UPDATE event, spin though
the
user's existing records looking for the checkbox set in any other one (You
can use DLookup for that). If a YES is found for any other address, popup
a
MSGBOX with the error and CANCEL the Update/add.

"Matthew" wrote:

I feel like there's a simple answer which eludes me.

I'm trying to make it so that a person can only have one preferred
address.

tblPeople
*PersonID
etc.

tblAddresses
*AddressID
PersonID
StreetAddress..
PreferredAddress (y/n)

I've tried creating an index in tblAddresses using the two fields
PersonID
and PreferredAddress, and setting it to Unique and Ignore Nulls.

That makes it so that no person can have two addresses marked as
preferred,
but they also cannot have two addresses NOT marked as preferred.

I want a person to be able to have as many not-preferred addresses as
they
want, but only one of them can be designated as preferred.

How can I do this?

Many thanks in advance!!

Matthew

Yeah, I thought about it as well, but then I figured, if the user selects
"make this my preferred addres", he probably knows what he's talking about.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Dennis" wrote:

Except that it would be nice to get a popup box that says, "There is already
a preferred addess defind; would you like to use the new one as preferred?"
(YES/NO)

That lets the user decide what to do. When the computer does "stuff"
automatically, you never really know what's going on under the sheets.

JMHO - YMMV

"Dale Fye" wrote:

This is the approach I would take as well. But rather than "spin through the
users existing records", I think I would just write an update query that sets
all of the other Preferred values for that user to false.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Matthew" wrote:

Ah, so it'll be done with code in the front end, not at the table level.

Thanks for your help!!!

Matthew

"Dennis" wrote in message
...
Using something like Address as a primary key is not doable. Simply add a
Yes/No field to the table called "PREFERRED" (or whatever you want). If
they
check it, it's a preferred address. Then, when they go to add another
address, if they check Preferred, in the BEFORE UPDATE event, spin though
the
user's existing records looking for the checkbox set in any other one (You
can use DLookup for that). If a YES is found for any other address, popup
a
MSGBOX with the error and CANCEL the Update/add.

"Matthew" wrote:

I feel like there's a simple answer which eludes me.

I'm trying to make it so that a person can only have one preferred
address.

tblPeople
*PersonID
etc.

tblAddresses
*AddressID
PersonID
StreetAddress..
PreferredAddress (y/n)

I've tried creating an index in tblAddresses using the two fields
PersonID
and PreferredAddress, and setting it to Unique and Ignore Nulls.

That makes it so that no person can have two addresses marked as
preferred,
but they also cannot have two addresses NOT marked as preferred.

I want a person to be able to have as many not-preferred addresses as
they
want, but only one of them can be designated as preferred.

How can I do this?

Many thanks in advance!!

Matthew