Re: Marking addresses as preferred

Allen's suggestion may be the right one for you or it may not be. It
depends on what you need.

Instead of priority, you might want to use AddressType. Where addressType
is Mailing, Billing, Shipping, Living, Secondary, Business, etc. Or you
might want to store an effective date so you can track the address history.
It depends on the purpose of the data. The simple Yes/No field may be the
answer to what you want.

Or you might need a combination of the above ideas.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Matthew" <mappleNOSPAMPLEASE@xxxxxxxx> wrote in message
news:Os284IjTIHA.1184@xxxxxxxxxxxxxxxxxxxxxxx
Hi Johns,

Thanks for this.

This query shows that there are 13,000+ clients who have at least one
address in tblClientAddresses, yet none marked as preferred.

(tblClients has 17,000+ records in it. tblAddresses has 10,000+.
tblClientAddresses has 14,000+. This data is freshly imported from
another database which did not store addresses in a many-to-many
relationship to clients like this one does.)

Now what do I do about it...

I really like Allen's suggestion of using Priority instead of Preferred,
however, my main Clients form is designed with a subform to show the
preferred address, concatenated, with a button to edit addresses. I
thought it was sensible to limit each client to a single Preferred
address, which makes it easy for our users to quickly view where a client
is.

I believe that form should follow function - but I am unsure how to
proceed. Any advice is greatly appreciated, you all have been a huge help
to me in my database education!

Thanks!

Matthew

"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:epGIF7iTIHA.4696@xxxxxxxxxxxxxxxxxxxxxxx
John made one small error. Since you are looking for those persons who
do not have a preferred address the query should have read.

SELECT tblClients.ClientID
FROM tblClients
WHERE NOT EXISTS
(SELECT ClientID
FROM tblClientAddresses
WHERE tblClientAddresses.ClientID =
tblClients.ClientID AND Preferred = TRUE);

Note the change from False to True in the criteria of the sub-query
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"Matthew" <mappleNOSPAMPLEASE@xxxxxxxx> wrote in message
news:uDMzNOiTIHA.3916@xxxxxxxxxxxxxxxxxxxxxxx
Hi John,

Thanks for your response.

When I try this SQL, I get prompted to Enter Parameter Value for
tblAddresses.ClientID.

I modified your code to the following:


SELECT tblClients.ClientID
FROM tblClients
WHERE NOT EXISTS
(SELECT ClientID FROM tblClientAddresses WHERE
tblClientAddresses.ClientID =
tblClients.ClientID AND Preferred = False);

And I get 7000 + records, which encouraged me. But scanning through
these client IDs, I see clients with no addresses along with clients
with one address that is already marked preferred. So this query
doesn't seem to tell me who, of the clients that have an address in
tblClientAddresses, has no preferred address.

Thanks!

Matthew


"John W. Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1sson3t0a8tnvi6e527q45vfmdcd28ehba@xxxxxxxxxx
On Wed, 2 Jan 2008 23:33:40 -0500, "Matthew"
<mappleNOSPAMPLEASE@xxxxxxxx>
wrote:

But what about clients who have one or more addresses, but none are
marked
as preferred? How can I make sure that each unique client in
tblClientAddresses has at least one (and preferably only one) of their
addresses marked as Preferred?

SELECT tblClients.ClientID
FROM tblClients
WHERE NOT EXISTS
(SELECT ClientID FROM tblAddresses WHERE tblAddresses.ClientID =
tblClients.ClientID AND Preferred = False);

John W. Vinson [MVP]








.