sql union self-join syntax

[SillySally]

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);

 

[Ken Snell [MVP]]

I think you're making it more complicated than it needs to be... try an
outer join query:

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 LEFT JOIN contacts c2
WHERE C1.Newsletter=Yes AND c2.contactid IS NULL
ORDER BY c1.PostalCode;

 

[Ken Snell [MVP]]

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;

 

[John Spencer (MVP)]

OH MY!!!
-- Nz goes around the table and fieldname as one entity.
Nz(C1.Nickname,C1.FirstName)
-- Where criteria must be connected with "Or" or "And", not a comma
-- UNION queries automatically perform a DISTINCT unless you use UNION ALL, so
you could drop the Distinct
-- ORDER BY Clause goes after the last SELECT Query (and if you are using this
for a report, it really does not have validity to use it. Reports use their own
sorting and grouping which is set by using the Sorting and Grouping dialog
(under the view menu)
-- Unless the addresses and other information is exactly the same in the contact
id record and the signicant other record, you will still get multiple records returned.

All that said, if you just want to pick one row out of the matching rows, you
might try using an aggregate query (also known as grouping or aggregate query).
That query might look something like the following UNTESTED query


SELECT Nz(First([NickName]),First([FirstName])) & " " & First([LastName]) AS
[Member Name],
First([CompanyName]) As Company,
First(Nz(Nz([ContactNickName],[ContactFirstName]) & " " &
[ContactLastName]) AS [Contact Name],
First([MailingAddress]) & ", " + First([OptAddress]) AS Address,
First(City) as CityName ,
UCase$(First([StateOrProvince])) AS State,
First(PostalCode) as PostCode
FROM Contacts
WHERE Newsletter = True
GROUP BY SignificantOtherID

 

[Ken Snell [MVP]]

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>

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>

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);

 

[SillySally]

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>

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>

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);

.

 

[SillySally]

Oh my is right. Sql. Union. Self-join. All are scary to
me individually. Wrap them all up together... Thanks for
all the details.

Okay, I was able to fix my union query- since I'm a sql
light weight, I let Access do it for me! I'll post it
here with the question of how to combine married people in
one label: Sam Smith ContactID=12 married to Sally Jones
so Sam's SignificantOtherID=13; Sally Jones ContactID=13
married to Sam Smith so Sally's SignificantOtherID=12.
I want a mail label that says:
Sally Jones
Sam Smith
Address
City, State Zip

Your code returned both Sally and Sam's records, as well
as one "Company" record. 3, that's all. Maybe that's
what "first" does.

Here's my union query that seems to bring back what I need
without handling my mailing label issue: Thanks

SELECT Nz([c1.NickName],[c1.FirstName]) & " " &
[c1.LastName] AS [Member Name], nz([c1.CompanyName]," ")
AS [Company Name], c1.[MailingAddress] & ", "+
[c1.OptAddress] AS Address, c1.City,
UCase$([c1.StateOrProvince]) AS State, c1.PostalCode
FROM Contacts AS c1, contacts AS c2
WHERE (((((([c1].[Newsletter])=Yes)) And [c1].[contactid]=
[c2].[significantotherid])<>False))
UNION SELECT Nz([NickName],[FirstName]) & " " & [LastName]
AS [Member Name], nz([CompanyName]," ") AS [Company Name],
[MailingAddress] & ", "+[OptAddress] AS Address, City,
UCase$([StateOrProvince]) AS State, PostalCode
FROM Contacts
WHERE (([Newsletter])=Yes) And [significantotherid] IS
Null;

-----Original Message-----
OH MY!!!
-- Nz goes around the table and fieldname as one entity.
Nz(C1.Nickname,C1.FirstName)
-- Where criteria must be connected with "Or" or "And", not a comma
-- UNION queries automatically perform a DISTINCT unless you use UNION ALL, so
you could drop the Distinct
-- ORDER BY Clause goes after the last SELECT Query (and if you are using this
for a report, it really does not have validity to use it. Reports use their own
sorting and grouping which is set by using the Sorting and Grouping dialog
(under the view menu)
-- Unless the addresses and other information is exactly the same in the contact
id record and the signicant other record, you will still get multiple records returned.

All that said, if you just want to pick one row out of the matching rows, you
might try using an aggregate query (also known as grouping or aggregate query).
That query might look something like the following UNTESTED query


SELECT Nz(First([NickName]),First([FirstName])) & " " & First([LastName]) AS
[Member Name],
First([CompanyName]) As Company,
First(Nz(Nz([ContactNickName],[ContactFirstName]) & " " &
[ContactLastName]) AS [Contact Name],
First([MailingAddress]) & ", " + First ([OptAddress]) AS Address,
First(City) as CityName ,
UCase$(First([StateOrProvince])) AS State,
First(PostalCode) as PostCode
FROM Contacts
WHERE Newsletter = True
GROUP BY SignificantOtherID

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);

.

 

[SillySally]

Ken,
I have a concatenation function that I've used elsewhere.
I'm wondering if that might work here, but of course it's
not quite right. I used it to concatenate a list of
animals that one ContactID volunteers with so:
Annie (Black Lab); Bucky (Newfoundland).

I'll post it here in case it might be an option, but I
can't figure out how to work ContactID and
SignificantOtherID into it. Thanks.

Public Function FldConcat(Source As String, ConcField As
String, _
ParamArray GroupBy())

' Returns the values separated by a semicolon
' from the ConcField field in the Source table or query.
' Records are selected by the fields in the GroupBy list.
' GroupBy list: Field1 Name, Field1 Value, Field2 Name,
Field2 Value, ...
' The ConcField and GroupBy fields may be of any type.

' Usage:
' ?FldConcat("Order Details", "Quantity", "OrderID", 10255)
' ?FldConcat("Products","UnitPrice", _
' "SupplierID",[SupplierID],"CategoryID",
[CategoryID])
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One
Side Table
' 10255 = Value on which return concatenated
Quantity


Dim db As Database, TQ As Object, rst As Recordset
Dim FldName As String, FldValue, ConcValue
Dim GroupByNum1 As Integer, I As Integer, dlm As String,
sql As Variant

On Error GoTo ExitFunc

GroupByNum1 = UBound(GroupBy)
If GroupByNum1 Mod 2 = 0 Then Exit Function

Set db = CurrentDb
On Error Resume Next
Set TQ = db.TableDefs(Source)
If Err <> 0 Then Set TQ = db.QueryDefs(Source)
On Error GoTo ExitFunc

sql = Null
For I = 0 To GroupByNum1 Step 2
FldName = GroupBy(I)
FldValue = GroupBy(I + 1)
dlm = ""
Select Case TQ.Fields(FldName).Type
Case dbText: dlm = "'"
Case dbDate: dlm = "#"
End Select
sql = sql + " AND " & "[" & FldName & "]" _
& IIf(IsNull(FldValue), " Is Null", "=" & dlm &
FldValue & dlm)
Next
sql = "SELECT [" & ConcField & "] FROM [" & Source & "]
WHERE " _
& sql & " ORDER BY [" & ConcField & "]"

Set rst = db.OpenRecordset(sql, dbOpenSnapshot)
FldConcat = Null
With rst
Do Until .EOF
ConcValue = .Fields(ConcField)
If Not IsNull(ConcValue) Then _
FldConcat = FldConcat + "; " & ConcValue
.MoveNext
Loop
.Close
End With

ExitFunc:
Set rs = Nothing: Set db = Nothing
End Function

 

[Ken Snell [MVP]]

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>

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>

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>




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);

.

 

[Ken Snell [MVP]]

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>

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>

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>


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>




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);




.

 

[Ken Snell [MVP]]

By the way, this query is designed to bring back only the records where the
person has no "significant other" or only one person from the "significant
other" partnership. After you confirm that this query does exactly this,
then we will work on getting the name info for the "significant other" for
your setup. I don't think we'll need to use the concatenate function to do
this....


--

Ken Snell
<MS ACCESS MVP>

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>

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>

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);




.

 

[SillySally]

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>

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>

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);




.

.

 

[Ken Snell [MVP]]

The difference between the records' contents may be that you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the significant other's name
as well (as the field named SO Name). In this query, we'll use a subquery to
get the significant other's name, where I am assuming that you want to use
the NickName, FirstName, and LastName fields for this information. Note that
the subquery uses an artificial value of -9999 for the ContactID when there
is no significant other for this person, and that will return a Null for the
SO Name in these cases. This will let you use the Null to test for whether
there is a second name for your label or not, in case you want to push the
address and city/state/zip up one line on the label and leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS [SO 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>

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>

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>


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);




.

.

 

[SillySally]

VERY cool! Once I spelled "significant" correctly. On my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate" the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the significant other's name
as well (as the field named SO Name). In this query, we'll use a subquery to
get the significant other's name, where I am assuming that you want to use
the NickName, FirstName, and LastName fields for this information. Note that
the subquery uses an artificial value of -9999 for the ContactID when there
is no significant other for this person, and that will return a Null for the
SO Name in these cases. This will let you use the Null to test for whether
there is a second name for your label or not, in case you want to push the
address and city/state/zip up one line on the label and leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS [SO 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>

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>


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>


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);




.





.

.

 

[Ken Snell [MVP]]

Sorry about the typo.

Before I suggest how to handle the with and without SO records, show me how
you want the output to look when there is an SO, and when there is not an
SO.

--

Ken Snell
<MS ACCESS MVP>

VERY cool! Once I spelled "significant" correctly. On my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate" the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the significant other's name
as well (as the field named SO Name). In this query, we'll use a subquery to
get the significant other's name, where I am assuming that you want to use
the NickName, FirstName, and LastName fields for this information. Note that
the subquery uses an artificial value of -9999 for the ContactID when there
is no significant other for this person, and that will return a Null for the
SO Name in these cases. This will let you use the Null to test for whether
there is a second name for your label or not, in case you want to push the
address and city/state/zip up one line on the label and leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS [SO 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>

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);




.





.

.

 

[SillySally]

My bad, I really must learn to use words that are easier
to spell. My nemesis: organization.

I don't have strong inclinations of either:
Jane Doe and John Deer
Or
Jane Doe
John Deer

I think the limitation may be a regular mailing label. If
we have:
Cassandra Williamson-Bartholomew and Michael Bartholomew
that's not likely to fit on one lable line. So maybe best
to have two separate lines.

I'm can't wait to hear how that will integrate with
Company and Company Contact mailing information- before I
believe I had Member Name or Company, second line if
Comapany, then appicable Company Contact. Thanks!

-----Original Message-----
Sorry about the typo.

Before I suggest how to handle the with and without SO records, show me how
you want the output to look when there is an SO, and when there is not an
SO.

--

Ken Snell
<MS ACCESS MVP>

VERY cool! Once I spelled "significant" correctly. On my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate" the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the significant other's name
as well (as the field named SO Name). In this query, we'll use a subquery to
get the significant other's name, where I am assuming that you want to use
the NickName, FirstName, and LastName fields for this information. Note that
the subquery uses an artificial value of -9999 for the ContactID when there
is no significant other for this person, and that will return a Null for the
SO Name in these cases. This will let you use the Null

to
test for whether

there is a second name for your label or not, in case

you
want to push the

address and city/state/zip up one line on the label and leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS [SO 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>


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]"

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);




.





.



.

.

 

[Ken Snell [MVP]]

OK - let me show you how to create a single, calculated field for a query
that will produce all four lines of the address (IF there is an SO: contact
on first line, SO on second line, street address on third line, and
city/state/zip on fourth line --- if there is no SO: contact on first
line, street address on second line, city/state/zip on third line, and blank
fourth line). I think you can modify as needed from this example:

IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents


There are other ways to "skin this cat", but this will give you one
approach. You can modify this for additional lines, etc.
--

Ken Snell
<MS ACCESS MVP>

My bad, I really must learn to use words that are easier
to spell. My nemesis: organization.

I don't have strong inclinations of either:
Jane Doe and John Deer
Or
Jane Doe
John Deer

I think the limitation may be a regular mailing label. If
we have:
Cassandra Williamson-Bartholomew and Michael Bartholomew
that's not likely to fit on one lable line. So maybe best
to have two separate lines.

I'm can't wait to hear how that will integrate with
Company and Company Contact mailing information- before I
believe I had Member Name or Company, second line if
Comapany, then appicable Company Contact. Thanks!

-----Original Message-----
Sorry about the typo.

Before I suggest how to handle the with and without SO records, show me how
you want the output to look when there is an SO, and when there is not an
SO.

--

Ken Snell
<MS ACCESS MVP>

VERY cool! Once I spelled "significant" correctly. On my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate" the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that
you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the
significant other's name
as well (as the field named SO Name). In this query,
we'll use a subquery to
get the significant other's name, where I am assuming
that you want to use
the NickName, FirstName, and LastName fields for this
information. Note that
the subquery uses an artificial value of -9999 for the
ContactID when there
is no significant other for this person, and that will
return a Null for the
SO Name in these cases. This will let you use the Null to
test for whether
there is a second name for your label or not, in case you
want to push the
address and city/state/zip up one line on the label and
leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS
[SO 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>


message
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]"

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>




"SillySally" <[email protected]>
wrote
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);




.





.



.

.

 

[SillySally]

That looks interesting! Embarrassingly, I don't know what
to do with it. I have created queries and then set up a
label report using the report wizard and the query I
created.
You said this is a calculated field. Do I put this on a
form? Sorry- no idea. Details appreciated.
Will this take into consideration that the labels are
being created for Companies as well? Thanks.

-----Original Message-----
OK - let me show you how to create a single, calculated field for a query
that will produce all four lines of the address (IF there is an SO: contact
on first line, SO on second line, street address on third line, and
city/state/zip on fourth line --- if there is no SO: contact on first
line, street address on second line, city/state/zip on third line, and blank
fourth line). I think you can modify as needed from this example:

IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents


There are other ways to "skin this cat", but this will give you one
approach. You can modify this for additional lines, etc.
--

Ken Snell
<MS ACCESS MVP>

My bad, I really must learn to use words that are easier
to spell. My nemesis: organization.

I don't have strong inclinations of either:
Jane Doe and John Deer
Or
Jane Doe
John Deer

I think the limitation may be a regular mailing label. If
we have:
Cassandra Williamson-Bartholomew and Michael Bartholomew
that's not likely to fit on one lable line. So maybe best
to have two separate lines.

I'm can't wait to hear how that will integrate with
Company and Company Contact mailing information- before I
believe I had Member Name or Company, second line if
Comapany, then appicable Company Contact. Thanks!

-----Original Message-----
Sorry about the typo.

Before I suggest how to handle the with and without SO records, show me how
you want the output to look when there is an SO, and

when
there is not an

SO.

--

Ken Snell
<MS ACCESS MVP>


"SillySally" <[email protected]> wrote

in
message

VERY cool! Once I spelled "significant" correctly.

On
my

labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I

first "concatenate"
the

Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that
you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the
significant other's name
as well (as the field named SO Name). In this query,
we'll use a subquery to
get the significant other's name, where I am assuming
that you want to use
the NickName, FirstName, and LastName fields for this
information. Note that
the subquery uses an artificial value of -9999 for the
ContactID when there
is no significant other for this person, and that will
return a Null for the
SO Name in these cases. This will let you use the

Null
to

test for whether
there is a second name for your label or not, in case you
want to push the
address and city/state/zip up one line on the label and
leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS
[SO 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>


message
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]"

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]"

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>




"SillySally"

wrote
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);




.





.



.



.

.

 

[Ken Snell [MVP]]

You would need to use this "calculated" field in the query that your label
report uses as its recordsource (the one you specified to the wizard when
you created the report). I assume that in your current query you have a
field that provides you with the label info? This calculated field is
designed to produce one entire label; if your current query is using four
fields to make the four lines of the label, then you'd replace all four
fields with this one calculated field.

This field doesn't take into account specifically the company item...I don't
know enough about your setup to be able to state with certainty how to do
that, but this example should give you a start towards how to set that up.
If you can provide details, perhaps I can suggest some ways to do this. (For
example, how do you know if a the label is for a company or for a person?)
--

Ken Snell
<MS ACCESS MVP>

That looks interesting! Embarrassingly, I don't know what
to do with it. I have created queries and then set up a
label report using the report wizard and the query I
created.
You said this is a calculated field. Do I put this on a
form? Sorry- no idea. Details appreciated.
Will this take into consideration that the labels are
being created for Companies as well? Thanks.

-----Original Message-----
OK - let me show you how to create a single, calculated field for a query
that will produce all four lines of the address (IF there is an SO: contact
on first line, SO on second line, street address on third line, and
city/state/zip on fourth line --- if there is no SO: contact on first
line, street address on second line, city/state/zip on third line, and blank
fourth line). I think you can modify as needed from this example:

IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents


There are other ways to "skin this cat", but this will give you one
approach. You can modify this for additional lines, etc.
--

Ken Snell
<MS ACCESS MVP>

My bad, I really must learn to use words that are easier
to spell. My nemesis: organization.

I don't have strong inclinations of either:
Jane Doe and John Deer
Or
Jane Doe
John Deer

I think the limitation may be a regular mailing label. If
we have:
Cassandra Williamson-Bartholomew and Michael Bartholomew
that's not likely to fit on one lable line. So maybe best
to have two separate lines.

I'm can't wait to hear how that will integrate with
Company and Company Contact mailing information- before I
believe I had Member Name or Company, second line if
Comapany, then appicable Company Contact. Thanks!

-----Original Message-----
Sorry about the typo.

Before I suggest how to handle the with and without SO
records, show me how
you want the output to look when there is an SO, and when
there is not an
SO.

--

Ken Snell
<MS ACCESS MVP>


message
VERY cool! Once I spelled "significant" correctly. On
my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate"
the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but
messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that
you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the
significant other's name
as well (as the field named SO Name). In this query,
we'll use a subquery to
get the significant other's name, where I am assuming
that you want to use
the NickName, FirstName, and LastName fields for this
information. Note that
the subquery uses an artificial value of -9999 for the
ContactID when there
is no significant other for this person, and that will
return a Null for the
SO Name in these cases. This will let you use the Null
to
test for whether
there is a second name for your label or not, in case
you
want to push the
address and city/state/zip up one line on the label and
leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS
[SO 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>


message
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]"

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>


"SillySally" <[email protected]>
wrote
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>




"SillySally"

wrote
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);




.





.



.



.

.

 

[SillySally]

Even after your great explanation, alas, I don't get it.
So here's my step by step. My query is "Labels by Ken":
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name], (SELECT Nz(c2.[NickName],c2.
[FirstName]) & " " & c2.[LastName] AS [SONm] FROM Contacts
c2....

I used the report wizard using "Labels by Ken" query as
the record source. I set up the label like this on 4
lines:
=Trim([Member Name])
=Trim([SO Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])
(probably not my best label report since I'm still trying
to figure out SO. It seemed to work (except for Company-
more later), and printed the married couple as I wanted.

You said: You would need to use this "calculated" field in
the query that your label report uses as its
recordsource. So I tried to add a column to my query-
Label: IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents

But I received an error message: I may have entered an
invalid comman or quotation marks. Am I missing some
syntax or did I put it in the wrong place?

I thought in my label report, I could replace all of the
lines with =Label.

What am I doing wrong? Thanks. Sorry.

-----Original Message-----
You would need to use this "calculated" field in the query that your label
report uses as its recordsource (the one you specified to the wizard when
you created the report). I assume that in your current query you have a
field that provides you with the label info? This calculated field is
designed to produce one entire label; if your current query is using four
fields to make the four lines of the label, then you'd replace all four
fields with this one calculated field.

This field doesn't take into account specifically the company item...I don't
know enough about your setup to be able to state with certainty how to do
that, but this example should give you a start towards how to set that up.
If you can provide details, perhaps I can suggest some ways to do this. (For
example, how do you know if a the label is for a company or for a person?)
--

Ken Snell
<MS ACCESS MVP>

That looks interesting! Embarrassingly, I don't know what
to do with it. I have created queries and then set up a
label report using the report wizard and the query I
created.
You said this is a calculated field. Do I put this on a
form? Sorry- no idea. Details appreciated.
Will this take into consideration that the labels are
being created for Companies as well? Thanks.

-----Original Message-----
OK - let me show you how to create a single, calculated field for a query
that will produce all four lines of the address (IF there is an SO: contact
on first line, SO on second line, street address on third line, and
city/state/zip on fourth line --- if there is no SO: contact on first
line, street address on second line, city/state/zip on third line, and blank
fourth line). I think you can modify as needed from this example:

IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents


There are other ways to "skin this cat", but this will give you one
approach. You can modify this for additional lines, etc.
--

Ken Snell
<MS ACCESS MVP>


My bad, I really must learn to use words that are easier
to spell. My nemesis: organization.

I don't have strong inclinations of either:
Jane Doe and John Deer
Or
Jane Doe
John Deer

I think the limitation may be a regular mailing label. If
we have:
Cassandra Williamson-Bartholomew and Michael Bartholomew
that's not likely to fit on one lable line. So maybe best
to have two separate lines.

I'm can't wait to hear how that will integrate with
Company and Company Contact mailing information- before I
believe I had Member Name or Company, second line if
Comapany, then appicable Company Contact. Thanks!

-----Original Message-----
Sorry about the typo.

Before I suggest how to handle the with and without SO
records, show me how
you want the output to look when there is an SO, and when
there is not an
SO.

--

Ken Snell
<MS ACCESS MVP>


message
VERY cool! Once I spelled "significant" correctly. On
my
labels I have the control source of the first line
(mailing name) as
=Trim([Member Name] & [Contact Name])

Do I fix the label report, or do I first "concatenate"
the
Member Name and SO Name? I tired to string the names
together and it worked for the married couple, but
messed
up all of the other records. So I stopped playing. I'm
back to your VERY cool sql statement... Thanks.

-----Original Message-----
The difference between the records' contents may be that
you were using the
DISTINCT modifier in your query; I did not.

Now let's modify the query so that it'll return the
significant other's name
as well (as the field named SO Name). In this query,
we'll use a subquery to
get the significant other's name, where I am assuming
that you want to use
the NickName, FirstName, and LastName fields for this
information. Note that
the subquery uses an artificial value of -9999 for the
ContactID when there
is no significant other for this person, and that will
return a Null for the
SO Name in these cases. This will let you use the Null
to
test for whether
there is a second name for your label or not, in case
you
want to push the
address and city/state/zip up one line on the label and
leave the fourth
line blank in these cases.

SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignficantOtherID,-9999)) AS
[SO 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>


message
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]"

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>


"SillySally" <[email protected]>
wrote
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>




"SillySally"

wrote
in message
[email protected]...
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);




.





.



.



.



.

.