residential vs. mailing addresses

I just inherited a database whose primary customer entry
form has fields for both residential and mailing
addresses. The staff currently does double entry, entering
the residential addresses into the mailing address fields
in every record, despite the fact that the vast majority
of our customers don't have separate mailing addresses.
It was set up this way in order to generate mailing labels
from the mail address fields.

If I have my staff stop entering the unnecessary
information in the mailing fields, how do I create a
query or report that will pull the main address *except*
when a mailing address is prensent and in those records
pull the mailing address? Or is a resdesign in order?
(Seems like I read something once about putting mailing
addresses in a separate table...)

I don't know Access all that well but I know our current
setup is wasting both the capabilities of the application
and my staff's time. Any suggestions are appreciated.

hank

you're correct - wasting time/resources. but i tremble to think of writing
instructions on how to use "one or the other address", especially as you say
you're not well-versed in Access; and i tend to get wordy even on simple
instructions.
if you want to send me a copy of your db, i'll see if i can fix it up and
send back to you so you can see how i did it. to take up my offer, do the
following:
1. make a copy of your database.
*do everything following in the COPY*
2. remove all proprietary data, but enter a few representative "dummy"
records so i have something to work with.
3. *compact the database* and zip it to under 1 MB in size (i have Winzip at
this end to unzip).
4. attach the zip file to an email.
5. reference the newsgroups in the subject line, and paste the text of your
initial post into the body.
6. email to me at ttaccKILLALLSPAMess1 at yahoo dot com, removing the
"all caps" letters first.


"hank" wrote in message
...
I just inherited a database whose primary customer entry
form has fields for both residential and mailing
addresses. The staff currently does double entry, entering
the residential addresses into the mailing address fields
in every record, despite the fact that the vast majority
of our customers don't have separate mailing addresses.
It was set up this way in order to generate mailing labels
from the mail address fields.

If I have my staff stop entering the unnecessary
information in the mailing fields, how do I create a
query or report that will pull the main address *except*
when a mailing address is prensent and in those records
pull the mailing address? Or is a resdesign in order?
(Seems like I read something once about putting mailing
addresses in a separate table...)

I don't know Access all that well but I know our current
setup is wasting both the capabilities of the application
and my staff's time. Any suggestions are appreciated.

hank

"hank" wrote in message
...
I just inherited a database whose primary customer entry
form has fields for both residential and mailing
addresses. The staff currently does double entry, entering
the residential addresses into the mailing address fields
in every record, despite the fact that the vast majority
of our customers don't have separate mailing addresses.
It was set up this way in order to generate mailing labels
from the mail address fields.

If I have my staff stop entering the unnecessary
information in the mailing fields, how do I create a
query or report that will pull the main address *except*
when a mailing address is prensent and in those records
pull the mailing address? Or is a resdesign in order?
(Seems like I read something once about putting mailing
addresses in a separate table...)

I don't know Access all that well but I know our current
setup is wasting both the capabilities of the application
and my staff's time. Any suggestions are appreciated.

hank

Hi Hank,
How are the labels pulled off currently? Straight from the table or through
a query?

A way to do it without taking into account what is there is:
Create a query with the customer table as the underlying table.
Pull in the name fields
Pull in the street address
Click on the wizard wand while in that column
Type in Line1: Iif(isnull([boxNo]),[streetAd], [boxNo]) - substitute your
field names.
Then Line1 should show the appropriate field.

and repeat for each address line.
HTH
Marc