A semi-unique index?

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

You probably need to put the preferred addresses in a separate table and make
the key (personid) unique.
I don't think there is any way you can do it with the y/n flag.

-Dorian

"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

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

Forgot to mention - you don't need to store the preferred address in the
preferred table you just need the ID of the address. Hope that makes sense
e.g.

tblAddress:
PersonAddressID
PersonID - many relationship
Address

tblPreferred Address:
PersonAddressID (foreign key)
personID (unique) - one relationship

-Dorian

"mscertified" wrote:

You probably need to put the preferred addresses in a separate table and make
the key (personid) unique.
I don't think there is any way you can do it with the y/n flag.

-Dorian

"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

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

On Dec 5, 6:25 pm, mscertified wrote:
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)

You probably need to put the preferred addresses in a separate table and make
the key (personid) unique.

I agree: preferred address is a relationship and thus warrants its own
table.

I don't think there is any way you can do it with the y/n flag.

It's doable but I wouldn't recommend it. You could add a column
AddressSequence of type INTEGER (Long), put a UNIQUE CONSTRAINT (or
unique index) on the compound of (PersonID, AddressSequence) -- there
should also be one on (AddressID, PersonID) -- then put a row-level
Validation Rule to the effect of

((AddressSequence = 1 AND PreferredAddress = TRUE) OR (AddressSequence
1 AND PreferredAddress = FALSE))

As icing on the cake you could create a helper SQL PROCEDURE which
uses a Sequence table of integers to INSERT a new row with the lowest
unused positive integer for the (PersonID, AddressID, AddressSequence)
key: [assuming a person can have up to five addresses]:

CREATE PROCEDURE AddAddress
(
arg_PersonID INTEGER,
arg_AddressID INTEGER
)
AS
INSERT INTO tblAddress (PersonID, AddressID, AddressSequence,
PreferredAddress)
SELECT DISTINCT arg_PersonID, arg_AddressID, MIN(S1.Seq), IIF(EXISTS(
SELECT *
FROM tblAddress AS T2
WHERE T2.PersonID = arg_PersonID
AND T2.PreferredAddress = TRUE
), FALSE, TRUE)
FROM Sequence AS S1
WHERE S1.Seq BETWEEN 1 AND 5
AND NOT EXISTS (
SELECT *
FROM tblAddress AS T2
WHERE T2.PersonID = arg_PersonID
AND S1.Seq = T2.AddressSequence);

The above will make a person's first address the preferred address and
all subsequent address non-preferred. Another stored proc could change
the preferred address:

CREATE PROCEDURE MakeAddressPreferred
(
arg_PersonID INTEGER,
arg_AddressID INTEGER
)
AS
UPDATE tblAddress
SET PreferredAddress = IIF(AddressID = arg_AddressID, TRUE, FALSE)
WHERE PersonID = arg_PersonID
AND EXISTS (
SELECT *
FROM tblAddress AS T2
WHERE T2.PersonID = arg_PersonID
AND T2.AddressID = arg_AddressID);

Another approach is a table-level CHECK CONSTRAINT e.g.

CHECK (NOT EXISTS (
SELECT T1.PersonID, T1.PreferredAddress
FROM tblAddress AS T1
GROUP BY T1.PersonID, T1.PreferredAddress
HAVING COUNT(*) 1));

While all the above approaches satisfy the business rule that no more
than one address may be designated as the preferred address for a
person, what they all lack is a way of imposing the business rule that
one address must be flagged as a person's preferred address. FWIW it
should be possible with the CHECK constraint approach but the Jet
engine has a bug which makes it unworkable in practice.

You could, however, revoke permission from the base table and grant
permissions only to your CRUD procs, thus *controlling* the ways in
which the data can be changed, which is the next best thing to table
constraints IMO. As usual with Jet there's a catch: for some reason
the Access team omitted security from the ACE (Access 2007) engine...

Jamie.

--

Hi Matthew,

I didn't know at what level to respond so I come to the top.

I didn't see anywhere the solution of implementing a reverse relationship.
Jamie et al are right in that Preferred Address is a relationship; you can't
solve/implement it with an index.

However include in your person table a column to hold an address id as a
foreign key. Name this column preferred address or some such. Now you have
a one-to-many relationship (addresses) from person to address but also a
one-to-many relationship (preferred addresses) from address to person, the
latter caters for the improbable but possible situation of the same address
being preferred by more than one person.

Keep an eye on how to maintain the integrity of these relationships.

Regards,

Rod

On Dec 6, 11:43 am, Rod Plastow
wrote:
include in your person table a column to hold an address id as a
foreign key. Name this column preferred address or some such. Now you have
a one-to-many relationship (addresses) from person to address but also a
one-to-many relationship (preferred addresses) from address to person

I've seen the natural consequence of this approach i.e. an entity
table with 100+ nullable columns and it ain't pretty.

the improbable but possible situation of the same address
being preferred by more than one person.

Funny. all members of my immediate family have the same preferred
address (or so they tell me g).

Jamie.

--

Oh Jamie, 'The slings and arrows ..'

Yes of course, delete the word improbable and substitute incestuous.

I would agree that 100 nullable columns is undesirable but like all good
things in life, they are OK in moderation. :-)

I was actually expecting someone to say that the dbms would screw up trying
to maintain a two-way reverse relationship (I don't know, I've never tried
it) in which case I would respond that my gut feel is to maintain the
preferred address relationship programatically.

BTW Just added a post to the religious pk debate.

Rod

On Dec 6, 1:00 pm, Rod Plastow
wrote:
I was actually expecting someone to say that the dbms would screw up trying
to maintain a two-way reverse relationship (I don't know, I've never tried
it) in which case I would respond that my gut feel is to maintain the
preferred address relationship programatically.

I see what you mean. It won't work in Jet because when checking
FOREIGN KEY constraints Jet considers NULL to be a data value. FWIW it
would work in SQL Server if the PerferredAddress (or whatever) column
was nullable (you'd have to retro-fit the FK after the tables have
been created), hence the reason for my comment about out of control
nullable columns in entity tables used to model relationships.

Jamie.

--