Choosing one address over another in a table

Hello,
I have a table in access which stores my customer
information. It allows the customer to enter in a
billing address and a shipping address (if different than
the billing address). There is a check box which is
checked if there is a different shipping address. I
would like to merge to word to print out my customer
information, however i would like all of the shipping
addresses to be printed out (not the billing addresses).
How do i send only the shipping addresses to word? I
tried a query, but it will not hold my alphabetical order
sort like the table does, so i would like to send it via
a table.

Thanks in advance for your help. I'm also going to ask
word people too since my problem encompasses both
programs.

Ilan

Ilan

I do not understand your comment about the query "not hold my
alphabetical order sort like the table does". Normally the
functionality is just the opposite... tables do not have the data in any
particular order, whereas you have total control over the sorting in a
query.

In your query you can make a calculated field like this...
OnlyShipping: Nz([Shipping Address],[Billing Address])

In other words, if there is an entry in the Shipping Address field, this
will be used, but if there is no Shipping Address entry, the Billing
Address data will be used. Is this what you want?

--
Steve Schapel, Microsoft Access MVP


Ilan wrote:
Hello,
I have a table in access which stores my customer
information. It allows the customer to enter in a
billing address and a shipping address (if different than
the billing address). There is a check box which is
checked if there is a different shipping address. I
would like to merge to word to print out my customer
information, however i would like all of the shipping
addresses to be printed out (not the billing addresses).
How do i send only the shipping addresses to word? I
tried a query, but it will not hold my alphabetical order
sort like the table does, so i would like to send it via
a table.

Thanks in advance for your help. I'm also going to ask
word people too since my problem encompasses both
programs.

Ilan

See, i sort my information on three levels. Last name,
then first name, then middle name (i.e. I want John B.
Colangelo to come before John P. Colangelo). If i send
to word i can sort with these levels while i have not
been able to do so in the query. As for your suggestion
of the query structure, that is what i want, but since i
broke down my fields into add line 1, add line 2, city,
state, zip, etc. for each address will this still be
plausible. If yes, should i (and would you mind) if
i "borrowed" your SQL structure for my query? Also, do i
need to drop down to SQL view to use it and what does the
NZ stand for?

Thanks,
Ilan
-----Original Message-----
Ilan

I do not understand your comment about the query "not
hold my
alphabetical order sort like the table does". Normally
the
functionality is just the opposite... tables do not have
the data in any
particular order, whereas you have total control over
the sorting in a
query.

In your query you can make a calculated field like
this...
OnlyShipping: Nz([Shipping Address],[Billing Address])

In other words, if there is an entry in the Shipping
Address field, this
will be used, but if there is no Shipping Address entry,
the Billing
Address data will be used. Is this what you want?

--
Steve Schapel, Microsoft Access MVP


Ilan wrote:
Hello,
I have a table in access which stores my customer
information. It allows the customer to enter in a
billing address and a shipping address (if different
than
the billing address). There is a check box which is
checked if there is a different shipping address. I
would like to merge to word to print out my customer
information, however i would like all of the shipping
addresses to be printed out (not the billing
addresses).
How do i send only the shipping addresses to word? I
tried a query, but it will not hold my alphabetical
order
sort like the table does, so i would like to send it
via
a table.

Thanks in advance for your help. I'm also going to
ask
word people too since my problem encompasses both
programs.

Ilan
.

Ilan

If you look at the deign view of a query, you will see that there is a
row in the query design grid for "Sort", which you can use to have
control over the order of the records. This is not possible in a table.

If your addresses are split over a number of fields, it makes things a
bit more complicated, but it is still possible. When the address is
exported to Word, do you want it still split into the separate
components, or do you want the address "unified".

Nz stands for "null to zero". It is one of Access's standard built-in
functions.

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
See, i sort my information on three levels. Last name,
then first name, then middle name (i.e. I want John B.
Colangelo to come before John P. Colangelo). If i send
to word i can sort with these levels while i have not
been able to do so in the query. As for your suggestion
of the query structure, that is what i want, but since i
broke down my fields into add line 1, add line 2, city,
state, zip, etc. for each address will this still be
plausible. If yes, should i (and would you mind) if
i "borrowed" your SQL structure for my query? Also, do i
need to drop down to SQL view to use it and what does the
NZ stand for?

Thanks,
Ilan

ok i will sort my query accordingly. i would like to keep
the fields separated when they are exported to word.

The NZ feature sounds like it could help, how could i go
about finding more information on how to use it?

Thanks,
Ilan
-----Original Message-----
Ilan

If you look at the deign view of a query, you will see
that there is a
row in the query design grid for "Sort", which you can use
to have
control over the order of the records. This is not
possible in a table.

If your addresses are split over a number of fields, it
makes things a
bit more complicated, but it is still possible. When the
address is
exported to Word, do you want it still split into the
separate
components, or do you want the address "unified".

Nz stands for "null to zero". It is one of Access's
standard built-in
functions.

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
See, i sort my information on three levels. Last name,
then first name, then middle name (i.e. I want John B.
Colangelo to come before John P. Colangelo). If i send
to word i can sort with these levels while i have not
been able to do so in the query. As for your suggestion
of the query structure, that is what i want, but since i
broke down my fields into add line 1, add line 2, city,
state, zip, etc. for each address will this still be
plausible. If yes, should i (and would you mind) if
i "borrowed" your SQL structure for my query? Also, do i
need to drop down to SQL view to use it and what does the
NZ stand for?

Thanks,
Ilan
.

Ilan,

You will find a topic on the Nz() function in Access Help.
The syntax is as I gave it before...
Nz([NameOfField],ValueToUseIfFieldIsNull)

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
ok i will sort my query accordingly. i would like to keep
the fields separated when they are exported to word.

The NZ feature sounds like it could help, how could i go
about finding more information on how to use it?

Thanks,
Ilan

Ok, final question. I have my data stored on two tables
as i mentioned earlier. But data stored in the shipping
address table does not store data in the table at all if
the person is using the billing address for everything.
Therefore, how do i pull the data so it shows everyone.
If i run it right now i only get those records that are
in the shipping address table.

Thanks,
Ilan
-----Original Message-----
Ilan,

You will find a topic on the Nz() function in Access
Help.
The syntax is as I gave it before...
Nz([NameOfField],ValueToUseIfFieldIsNull)

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
ok i will sort my query accordingly. i would like to
keep
the fields separated when they are exported to word.

The NZ feature sounds like it could help, how could i
go
about finding more information on how to use it?

Thanks,
Ilan

.

Ilan,

Actually, you didn't mention earlier that you had the shipping address
in a separate table. What is the reason for this design?

You willl need to use a Left Join between the tables in the query. In
design view of the query, double-click on the join line between the
tables, and then in the properties dialog that pops up, select the
option that says something like "all records from the table with the
billing address, and matching records from the shipping address table".

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
Ok, final question. I have my data stored on two tables
as i mentioned earlier. But data stored in the shipping
address table does not store data in the table at all if
the person is using the billing address for everything.
Therefore, how do i pull the data so it shows everyone.
If i run it right now i only get those records that are
in the shipping address table.

Thanks,
Ilan

I decided to have them on separate tables just because i
thought it would be cleaner. I ran the Nz functions in
my query and it worked perfectly!! Thanks for all of your
help Steve!

Ilan


-----Original Message-----
Ilan,

Actually, you didn't mention earlier that you had the
shipping address
in a separate table. What is the reason for this design?

You willl need to use a Left Join between the tables in
the query. In
design view of the query, double-click on the join line
between the
tables, and then in the properties dialog that pops up,
select the
option that says something like "all records from the
table with the
billing address, and matching records from the shipping
address table".

--
Steve Schapel, Microsoft Access MVP

Ilan wrote:
Ok, final question. I have my data stored on two
tables
as i mentioned earlier. But data stored in the
shipping
address table does not store data in the table at all
if
the person is using the billing address for
everything.
Therefore, how do i pull the data so it shows
everyone.
If i run it right now i only get those records that
are
in the shipping address table.

Thanks,
Ilan

.