Marking addresses as preferred

[Matthew]

Hello,

Here's my structure:

tblClients
*ClientID
....

tblAddresses
*AddressID
....

tblClientAddresses
*ClientAddressID
ClientID
AddressID
Preferred (y/n)

I want to make sure that every client has no more than one preferred
address. I've identified those with more than one preferred, and it's a
small enough number that I can clean that up manually.

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?

Many thanks in advance!!

Matthew

 

[John W. Vinson]

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]

 

[Allen Browne]

Matthew, an alternate approach might be to use a Priority field (Number)
instead of the Preferred field (Yes/no.)

That way, the user can specify the not only the #1 address for the client,
but other preferred addresses as well. If they have 2 that are both #1, your
software assumes that either one is of equal priority, and so the user will
be happy with either address returned.

This is very simple to implement, more flexible and informative, and moves
responsiblity for sorting out the data from the developer to the user.

 

[John Spencer]

The following should identify all the records with no preferred address

SELECT tblClients.ClientID
FROM tblClients LEFT JOIN tblClientAddresses
ON tblClients.ClientID = tblClientAddresses.ClientID
WHERE tblClientAddresses.Preferred = True
GROUP BY tblClients.ClientID
HAVING Count(tblClientAddresses.ClientID) = 0


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

 

[Matthew]

Hi John,

Thanks for your reply.

This results in no records. What are we missing??

Thanks again!

Matthew

 

[Matthew]

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

 

[Matthew]

I love this idea, except for one thing:

On my main Client form, I have a subform that shows that client's preferred
address, concatenated. The user can click a button to edit the client's
address list. If there are more than one preferred address, which one shows
up in that quickly-viewable subform as the main address?

Many thanks!

Matthew

 

[John Spencer]

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

 

[John Spencer]

MY Error. You might try the following

SELECT tblClients.ClientID
FROM tblClients INNER JOIN tblClientAddresses
ON tblClients.ClientID = tblClientAddresses.ClientID
GROUP BY tblClients.ClientID
HAVING Sum(tblClientAddresses.Preferred) = 0

OR for an updatable query try

SELECT tblClients.*
FROM tblClients
WHERE ClientID IN
(SELECT tblClients.ClientID
FROM tblClients INNER JOIN tblClientAddresses
ON tblClients.ClientID = tblClientAddresses.ClientID
GROUP BY tblClients.ClientID
HAVING Sum(tblClientAddresses.Preferred) = 0)



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

 

[Matthew]

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

 

[Matthew]

Hmm, I wonder why these queries yield 8,002 records, whereas this:


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


yields 13,240 records?

Aha: The earlier one includes clients that have no address, their clientID
does not exist in tblClientAddresses!

Thanks!!

Matthew

 

[John Spencer]

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

 

[Allen Browne]

I use a subquery to retrieve the primary key of the client's preferred
address. In practice, it chooses the lower autonumber value if a client has
multiple addresses of equal priority (so typically the earlier one entered),
but the software documentation states that the selection is undefined (not
predicatable) for this case.

So, you create a query on your tblClient, and type the subquery into the
Field row in query design: It looks like this:
PreferredAddress: (SELECT TOP 1 tblClientAddress.ClientAddressId
FROM tblClientAddress
WHERE ((tblClientAddress.ClientId = tblClient.ClientID)
AND (tblClientAddress.AddressStartDate <= Date())
AND ((tblClientAddress.AddressCeaseDate) Is Null
Or (tblClientAddress.AddressCeaseDate > Date())))
ORDER BY tblClientAddress.AddressPriority,
tblClientAddress.ClientAddressId)

If subqueries are a new idea, see:
http://allenbrowne.com/subquery-01.html

 

[Matthew]

One thing that remains a mystery to me is why the second query allows me to
update the data, but the first does not?

Thanks!

Matthew

 

[John Spencer]

The first query is combining rows (that is what a group by does). When you
have combined rows together the database engine has no specific reference to
any row(s) in the database. Without the ability to identify the specific
row, it cannot update a row. Plus if you were updating an aggregate bit of
data - such as a total, it would have to either set every row involved to
the total, split the new sum among the rows involved - equally,
proportionately.

THe second query returns specific rows that have been identified by the
where clause. In this case the database engine does know which specific
rows are involved.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Matthew]

Thank you, this is helpful!!

Matthew

The first query is combining rows (that is what a group by does). When
you have combined rows together the database engine has no specific
reference to any row(s) in the database. Without the ability to identify
the specific row, it cannot update a row. Plus if you were updating an
aggregate bit of data - such as a total, it would have to either set every
row involved to the total, split the new sum among the rows involved -
equally, proportionately.

THe second query returns specific rows that have been identified by the
where clause. In this case the database engine does know which specific
rows are involved.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.