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.
--