sql union self-join syntax

[Ken Snell [MVP]]

OK - I take it that this is the complete query that you call "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
WHERE c2.ContactID = Nz(c1.SignificantOtherID,-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;


What we'll do is create a second query that uses this query. Let's call this
second query "Labels_Report":

SELECT (IIf(Len(LK.[SO Name] & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode])
& Chr(13) & Chr(10),
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[SO Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode]))
AS MyLabelContents
FROM Labels by Ken AS LK;

Then in your report, change the Record Source to the Labels_Report query.
Delete three of the textboxes that you currently use. Set the control source
of the one remaining textbox to MyLabelContents field.

--

Ken Snell
<MS ACCESS MVP>

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

 

[SillySally]

Thank you for the tutelage. You are very patient.

OK, I think I have it working in that my married couple's
address labels are great! The query for "Labels by Ken"
was just right. I'm going to repost the second
query "Labels_Report". I had some syntax trauma and hope
I did this correctly:
SELECT (IIf(Len(LK.[SO Name] & "")=0,Trim(LK.[Member
Name])) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10)), Trim(LK.[Member
Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) & Chr(13)
& Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim
(LK.City & ", " & LK.State & " " & LK.PostalCode) AS
MyLabelContents
FROM [Labels by Ken] AS LK;

OK, onto the Company issue. Before SO reared its head, I
had labels that printed either the Member Name and
address (3 lines total), or the Company Name on line 1
with the Contact Name on line 2 and then 2 address lines
(4 lines total). That query looked like this:

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, Contacts.City,
UCase$([StateOrProvince]) AS State, Contacts.PostalCode
FROM Contacts
WHERE (((Contacts.Hydrant)=Yes))
ORDER BY Contacts.PostalCode;

and the labels were formatted like this:
=Trim([Member Name] & [Contact Name])
=Trim([Company Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])

So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. And of course, Company and Contact
showing up blank since I haven't accounted for them (and
then the 2 address lines). So maybe Company is messing
up SO? Thanks for hanging in there ;-)

 

[Ken Snell [MVP]]

Do you need a single query and report to work for all three situations: (1)
member with significant other (4 lines); (2) member without significant
other (3 lines plus one blank line); and (3) company and member together (4
lines)?

If yes, how do you determine which scenario applies for (3)? Is it the
presence/absence of a value in the company field?

I'm a bit confused by your statement:
"So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. "

Are you saying that this is occurring with the "company" query? or that this
is the result of the query that we worked out for member with/without SO?

 

[SillySally]

Hi, there. I believe I do need a signle query and report
to work for all the situations. I have 3 fields:
MemberName, CompanyName and ContactName. If there is a
MemberName there is no CompanyName and no ContactName,
although there could be a SO. So my "old" labels used nz
to say (I think) print on line 1 either MemberName or
CompanyName. If CompanyName is on line 1, then print
ContactName on line 2, else skip line 2.

Now I want: If there is a MemberName on line 1, look for
a SO for line 2, else skip line 2. If there is a
CompanyName on line 1, then print the ContactName on line
2.

The blank lines occur with the query we worked out
with/without SO. All records without a SO give a blank
line 2. This includes MemberName records without a SO.
So the records that have all 4 lines (MemberName, SO,
Address, City/State/Zip) are fine. Those without a SO
also have 4 lines (MemberName, blank line 2, Address,
City/State/Zip).

Those records that need CompanyName on line 1 and
ContactName on line 2, have both lines blank (which makes
sense because we haven't yet added the CompanyName and
ContactName to the Labels_Report).

Thanks for the help. I surely appreciate your efforts!

-----Original Message-----
Do you need a single query and report to work for all three situations: (1)
member with significant other (4 lines); (2) member without significant
other (3 lines plus one blank line); and (3) company and member together (4
lines)?

If yes, how do you determine which scenario applies for (3)? Is it the
presence/absence of a value in the company field?

I'm a bit confused by your statement:
"So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. "

Are you saying that this is occurring with the "company" query? or that this
is the result of the query that we worked out for member with/without SO?


--

Ken Snell
<MS ACCESS MVP>

Thank you for the tutelage. You are very patient.

OK, I think I have it working in that my married couple's
address labels are great! The query for "Labels by Ken"
was just right. I'm going to repost the second
query "Labels_Report". I had some syntax trauma and hope
I did this correctly:
SELECT (IIf(Len(LK.[SO Name] & "")=0,Trim(LK.[Member
Name])) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr (13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10)), Trim(LK.[Member
Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) & Chr (13)
& Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim
(LK.City & ", " & LK.State & " " & LK.PostalCode) AS
MyLabelContents
FROM [Labels by Ken] AS LK;

OK, onto the Company issue. Before SO reared its head, I
had labels that printed either the Member Name and
address (3 lines total), or the Company Name on line 1
with the Contact Name on line 2 and then 2 address lines
(4 lines total). That query looked like this:

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, Contacts.City,
UCase$([StateOrProvince]) AS State, Contacts.PostalCode
FROM Contacts
WHERE (((Contacts.Hydrant)=Yes))
ORDER BY Contacts.PostalCode;

and the labels were formatted like this:
=Trim([Member Name] & [Contact Name])
=Trim([Company Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])

So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. And of course, Company and Contact
showing up blank since I haven't accounted for them (and
then the 2 address lines). So maybe Company is messing
up SO? Thanks for hanging in there ;-)

.

 

[Ken Snell [MVP]]

I see why the "Labels_Report" query would cause a blank second line when
there is no SO Name string. I "forgot" that you're concatenating a blank
space into the SO Name even if there is no SO (see the "Labels by Ken"
query's subquery that returns the SO Name field). Therefore, this query
should be used for the "Labels_Report" query, as it will "ignore" that blank
space:

SELECT (IIf(Len(Trim(LK.[SO Name]) & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13) & Chr(10)),
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.[SO Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode))
AS MyLabelContents
FROM [Labels by Ken] AS LK;

I added a Trim function inside the Len function.


OK - now we can modify the "Labels_Report" query as well to include the
Company and contact information. I'll use the query that I posted above as
the starting point.

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,
IIf(Len(Trim(LK.[SO Name]) & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10),
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.[SO Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)),
Trim(LK.[Company Name]) & Chr(13) & Chr(10) &
Trim(LK.[ContactName]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken] AS LK;

Assuming that I've accounted for all the parentheses correctly, this one
query should now produce your labels with no blank lines between the lines
on each label.
--

Ken Snell
<MS ACCESS MVP>

Hi, there. I believe I do need a signle query and report
to work for all the situations. I have 3 fields:
MemberName, CompanyName and ContactName. If there is a
MemberName there is no CompanyName and no ContactName,
although there could be a SO. So my "old" labels used nz
to say (I think) print on line 1 either MemberName or
CompanyName. If CompanyName is on line 1, then print
ContactName on line 2, else skip line 2.

Now I want: If there is a MemberName on line 1, look for
a SO for line 2, else skip line 2. If there is a
CompanyName on line 1, then print the ContactName on line
2.

The blank lines occur with the query we worked out
with/without SO. All records without a SO give a blank
line 2. This includes MemberName records without a SO.
So the records that have all 4 lines (MemberName, SO,
Address, City/State/Zip) are fine. Those without a SO
also have 4 lines (MemberName, blank line 2, Address,
City/State/Zip).

Those records that need CompanyName on line 1 and
ContactName on line 2, have both lines blank (which makes
sense because we haven't yet added the CompanyName and
ContactName to the Labels_Report).

Thanks for the help. I surely appreciate your efforts!

-----Original Message-----
Do you need a single query and report to work for all three situations: (1)
member with significant other (4 lines); (2) member without significant
other (3 lines plus one blank line); and (3) company and member together (4
lines)?

If yes, how do you determine which scenario applies for (3)? Is it the
presence/absence of a value in the company field?

I'm a bit confused by your statement:
"So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. "

Are you saying that this is occurring with the "company" query? or that this
is the result of the query that we worked out for member with/without SO?


--

Ken Snell
<MS ACCESS MVP>

Thank you for the tutelage. You are very patient.

OK, I think I have it working in that my married couple's
address labels are great! The query for "Labels by Ken"
was just right. I'm going to repost the second
query "Labels_Report". I had some syntax trauma and hope
I did this correctly:
SELECT (IIf(Len(LK.[SO Name] & "")=0,Trim(LK.[Member
Name])) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr (13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10)), Trim(LK.[Member
Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) & Chr (13)
& Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim
(LK.City & ", " & LK.State & " " & LK.PostalCode) AS
MyLabelContents
FROM [Labels by Ken] AS LK;

OK, onto the Company issue. Before SO reared its head, I
had labels that printed either the Member Name and
address (3 lines total), or the Company Name on line 1
with the Contact Name on line 2 and then 2 address lines
(4 lines total). That query looked like this:

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, Contacts.City,
UCase$([StateOrProvince]) AS State, Contacts.PostalCode
FROM Contacts
WHERE (((Contacts.Hydrant)=Yes))
ORDER BY Contacts.PostalCode;

and the labels were formatted like this:
=Trim([Member Name] & [Contact Name])
=Trim([Company Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])

So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. And of course, Company and Contact
showing up blank since I haven't accounted for them (and
then the 2 address lines). So maybe Company is messing
up SO? Thanks for hanging in there ;-)

.

 

[SillySally]

You are very fabulous! That works just right and I'm
going to study it to see what you did.

A couple of questions. Along the way, I've lost the
WHERE statement. Now I realize that that's what happened
when I asked you why your query returned more names than
my query. WHERE (Contacts.Newsletter)=Yes
I was able to tack it on the end of "Labels by Ken" to
return the correct information. But I don't understand
the syntax of how to add it to the Labels_Report.

I have a couple of more difficult WHERE statements that
need to key off multiple tables. This scares me. I also
need to figure out how to handle Members with 2nd
addresses. One of the MVPs suggested a new table, which
makes the queries even worse... Thanks!

-----Original Message-----
I see why the "Labels_Report" query would cause a blank second line when
there is no SO Name string. I "forgot" that you're concatenating a blank
space into the SO Name even if there is no SO (see the "Labels by Ken"
query's subquery that returns the SO Name field). Therefore, this query
should be used for the "Labels_Report" query, as it will "ignore" that blank
space:

SELECT (IIf(Len(Trim(LK.[SO Name]) & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13) & Chr(10)),
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.[SO Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode))
AS MyLabelContents
FROM [Labels by Ken] AS LK;

I added a Trim function inside the Len function.


OK - now we can modify the "Labels_Report" query as well to include the
Company and contact information. I'll use the query that I posted above as
the starting point.

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,
IIf(Len(Trim(LK.[SO Name]) & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10),
Trim(LK.[Member Name]) & Chr(13) & Chr(10) &
Trim(LK.[SO Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)),
Trim(LK.[Company Name]) & Chr(13) & Chr(10) &
Trim(LK.[ContactName]) & Chr(13) & Chr(10) &
Trim(LK.Address) & Chr(13) & Chr(10) &
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken] AS LK;

Assuming that I've accounted for all the parentheses correctly, this one
query should now produce your labels with no blank lines between the lines
on each label.
--

Ken Snell
<MS ACCESS MVP>

Hi, there. I believe I do need a signle query and report
to work for all the situations. I have 3 fields:
MemberName, CompanyName and ContactName. If there is a
MemberName there is no CompanyName and no ContactName,
although there could be a SO. So my "old" labels used nz
to say (I think) print on line 1 either MemberName or
CompanyName. If CompanyName is on line 1, then print
ContactName on line 2, else skip line 2.

Now I want: If there is a MemberName on line 1, look for
a SO for line 2, else skip line 2. If there is a
CompanyName on line 1, then print the ContactName on line
2.

The blank lines occur with the query we worked out
with/without SO. All records without a SO give a blank
line 2. This includes MemberName records without a SO.
So the records that have all 4 lines (MemberName, SO,
Address, City/State/Zip) are fine. Those without a SO
also have 4 lines (MemberName, blank line 2, Address,
City/State/Zip).

Those records that need CompanyName on line 1 and
ContactName on line 2, have both lines blank (which makes
sense because we haven't yet added the CompanyName and
ContactName to the Labels_Report).

Thanks for the help. I surely appreciate your efforts!

-----Original Message-----
Do you need a single query and report to work for all three situations: (1)
member with significant other (4 lines); (2) member without significant
other (3 lines plus one blank line); and (3) company

and
member together (4

lines)?

If yes, how do you determine which scenario applies for (3)? Is it the
presence/absence of a value in the company field?

I'm a bit confused by your statement:
"So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO having a
blank line 2. Don't know why. There should only be 3
address lines. "

Are you saying that this is occurring with

the "company"
query? or that this

is the result of the query that we worked out for

member
with/without SO?

--

Ken Snell
<MS ACCESS MVP>


"SillySally" <[email protected]> wrote

in
message

Thank you for the tutelage. You are very patient.

OK, I think I have it working in that my married couple's
address labels are great! The query for "Labels by Ken"
was just right. I'm going to repost the second
query "Labels_Report". I had some syntax trauma and hope
I did this correctly:
SELECT (IIf(Len(LK.[SO Name] & "")=0,Trim(LK.[Member
Name])) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr (13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode) & Chr(13) & Chr(10)), Trim(LK.[Member
Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) & Chr (13)
& Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim
(LK.City & ", " & LK.State & " " & LK.PostalCode) AS
MyLabelContents
FROM [Labels by Ken] AS LK;

OK, onto the Company issue. Before SO reared its head, I
had labels that printed either the Member Name and
address (3 lines total), or the Company Name on line 1
with the Contact Name on line 2 and then 2 address lines
(4 lines total). That query looked like this:

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, Contacts.City,
UCase$([StateOrProvince]) AS State, Contacts.PostalCode
FROM Contacts
WHERE (((Contacts.Hydrant)=Yes))
ORDER BY Contacts.PostalCode;

and the labels were formatted like this:
=Trim([Member Name] & [Contact Name])
=Trim([Company Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])

So now with "Labels by Ken" and "Labels_Report", all
labels have 4 lines. I have Members without SO

having
a

blank line 2. Don't know why. There should only be 3
address lines. And of course, Company and Contact
showing up blank since I haven't accounted for them (and
then the 2 address lines). So maybe Company is messing
up SO? Thanks for hanging in there ;-)




.

.

 

[Ken Snell [MVP]]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

You are very fabulous! That works just right and I'm
going to study it to see what you did.

A couple of questions. Along the way, I've lost the
WHERE statement. Now I realize that that's what happened
when I asked you why your query returned more names than
my query. WHERE (Contacts.Newsletter)=Yes
I was able to tack it on the end of "Labels by Ken" to
return the correct information. But I don't understand
the syntax of how to add it to the Labels_Report.

You don't need to add it to "Labels_Report" query. So long as you put it in
the "Labels by Ken" query, it'll carry into the other query.

I have a couple of more difficult WHERE statements that
need to key off multiple tables. This scares me. I also
need to figure out how to handle Members with 2nd
addresses. One of the MVPs suggested a new table, which
makes the queries even worse... Thanks!

I saw that thread. If you anticipate having only one additional address, it
may be better for you to just add more fields to the one table for the extra
address. His suggestion gives you maximum flexibility for an unlimited
number of addresses, but sometimes, for limits that will not change (as a
programmer, I should note here: almost always the limits will change as
someone sees what can be done with a database!), adding fields is a quicker
solution (but with limits on changes for the future). You then would add a
field to indicate which address should be "used", and then the "Labels by
Ken" query would be modified to test the value of that "which address to
use" field, and select the Address1 or the Address2 information as the
"Address" data.

 

[SillySally]

All good now! Thanks for all of the help- I appreciate it.

I think I'm going to go with adding additional address
fields in my Contacts table. There comes a point where I
need to draw the line (given little talent!). I think
there are 2 members with 2 addresses. 80/20 rule right?!

I'm sure I'll struggle with the next step. Hope you'll
answer future posts! Cheers.