Okay, that works just right bringing back only one of the
married couple entries. Just out of curiousity, the union
query that I wrote only brings back records that have
everything (address, city, state and zip) completed, where
your query brings back all of the records (handy I think
to pinpoint where we are missing info when we run
labels). But I can't see why the difference...
Ready for the name of the SO!
By the way, I think this query is going to get even more
complicated, but not beyond the likes of you! I need to
add a feature for those lucky folks that have second homes
that they live at part of the year. I was thinking of
having two addresses with an "in use" flag to show which
one should be used (not quite sure how to set that up).
It all depends on if I'll be able to get this feature to
work with this type of query. But as I've learned from
you, look forward but do one step at a time. Thanks
-----Original Message-----
sigh... and then I left in an extraneous parenthesis....
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name], Nz(c1.[CompanyName]," ") AS
[Company Name], Nz(c1.[ContactNickName],c1.
[ContactFirstName]) & " " & c1.[ContactLastName] AS
ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode, c1.SignificantOtherID,
c1.SignificantOtherID
FROM Contacts AS c1
WHERE c1.SignificantOtherID > c1.ContactID Or
c1.SignificantOtherID Is Null
ORDER BY c1.PostalCode;
--
Ken Snell
<MS ACCESS MVP>
"Ken Snell [MVP]" <
[email protected]>
wrote in message
You changed the WHERE statement that I'd suggested you
use. Try this:
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name], Nz(c1.[CompanyName]," ") AS
[Company Name], Nz(c1.[ContactNickName],c1.
[ContactFirstName]) & " " & c1.[ContactLastName] AS
ContactName, c1.[MailingAddress] & ", "+c1. [OptAddress]
AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode, c1.SignificantOtherID,
c1.SignificantOtherID
FROM Contacts AS c1
WHERE c1.SignificantOtherID > c1.ContactID Or
c1.SignificantOtherID) Is Null
ORDER BY c1.PostalCode;
Let's get the records correct here, and then we can
turn to the
Concatenate function that you suggest using in a later
post, ok?
--
Ken Snell
<MS ACCESS MVP>
in message
Ken, you came back for more! Thanks for the posts-
using
your code with a slight modification, I was able to
bring
back all of the records I'm looking for. Sort of- I
brought back "duplicate" records, but don't know what
to
do with them. John Doe ContactID=7 is married to Jane
Deer ContactID=8. That means that in John Doe's
record,
SignificantOtherID=8 (linking him to Jane); for Jane
Deer,
SignificantOtherID=7 (linking her to John). For their
singular mailing label,I want:
Jane Doe
John Deer
Address
City, State Zip
How can I handle this? Thanks.
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name], Nz(c1.[CompanyName]," ")
AS
[Company Name], Nz(c1.[ContactNickName],c1.
[ContactFirstName]) & " " & c1.[ContactLastName] AS
ContactName, c1.[MailingAddress] & ", "+c1.
[OptAddress] AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode, c1.SignificantOtherID,
c1.SignificantOtherID
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID) Is Not Null Or
(c1.SignificantOtherID) Is Null))
ORDER BY c1.PostalCode;
-----Original Message-----
And, upon further reading, the DISTINCT modifier should
be unnecessary, and
I find a few other typos:
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " &
c1.[LastName] AS [Member Name], Nz(c1.
[CompanyName]," ")
AS
[Company Name], Nz(c1.[ContactNickName],
c1.[ContactFirstName]) & " " & c1.[ContactLastName]) AS
[Contact
Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode
FROM Contacts c1
WHERE c1.significantotherid IS NULL OR
c1.significantotherid > c1.contactid
ORDER BY c1.PostalCode;
--
Ken Snell
<MS ACCESS MVP>
"Ken Snell [MVP]" <
[email protected]>
wrote in message
I am not sure if my first reply will be all the
records
that you're
seeking. I take it that the Contacts table contains a
field
(significantohterid) that is filled in if the person
in
that record has a
partner? If so, how do you plan to "weed out" that
partner when the partner
also is a record in the table -- is the assumption
that
the two records
will have some other commonality that would allow a
union query to get rid
of duplicates? That can't be done when you're
returning
names in the union
query's results (I assume that the names of the two
partners are not
exactly the same < g >).
If it doesn't matter which 'significant other' person
gets the letter,
then perhaps this query will do what you seek. It
selects records with no
value in the significantotherid field and records
where
the "id" value in
the significantotherid field is greater than the "id"
value in the
record's contactid field (this is an arbitrary way to
select just one of
the two records for the two related people):
SELECT DISTINCT c1.Nz([NickName],[FirstName]) & " " &
[LastName] AS [Member Name], c1.nz
([CompanyName]," ") AS
[Company Name], c1.nz(Nz([ContactNickName],
[ContactFirstName]) & " " & [ContactLastName]) AS
[Contact
Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, c1.UCase$([StateOrProvince]) AS
State,
c1.PostalCode
FROM Contacts c1
WHERE c1.significantotherid IS NULL OR
c1.significantotherid > c1.contactid
ORDER BY c1.PostalCode;
--
Ken Snell
<MS ACCESS MVP>
in message
Hi. I can't seem to get my syntax correct. I hope
the
first part of the union query will give me everyone
with a
SignificantOtherID (avoiding duplicates) and the
second
part will give me everyone without a Significant
Other.
I'm trying to make mailing labels without sending
duplicates to the married/SO people. Thanks, Sal
SELECT DISTINCT c1.Nz([NickName],[FirstName]) & " "
&
[LastName] AS [Member Name], c1.nz
([CompanyName]," ")
AS
[Company Name], c1.nz(Nz([ContactNickName],
[ContactFirstName]) & " " & [ContactLastName]) AS
[Contact
Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, c1.UCase$([StateOrProvince]) AS
State,
c1.PostalCode
FROM Contacts c1, contacts c2
WHERE ((((Contacts.Newsletter)=Yes)), c1.contactid =
c2.significantotherid)
ORDER BY Contacts.PostalCode
union
SELECT Nz([NickName],[FirstName]) & " " &
[LastName] AS
[Member Name], nz([CompanyName]," ") AS [Company
Name], nz
(Nz([ContactNickName],[ContactFirstName]) & " " &
[ContactLastName]) AS [Contact Name],
[MailingAddress]
& ", "+[OptAddress] AS Address, City,
UCase$([StateOrProvince]) AS State, PostalCode
FROM Contacts
WHERE ((SignificantOtherId) Is Null);
.
.