mailing address vs. physical address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I inherited a db with separate sets of fields for physical address and
mailing address, all housed in tblCustomer. Even though the number of
customers with mailing addresses different from their physical addresses is
very small, the staff here have historically been doing double address entry
(entering the physical address also in the mailing address fields for every
customer) in order to be able to run their labels from the mailing address
fields and not missing anyone.

So. It made sense to me to add a Yes/No field to indicate whether a
different mailing address exists, then I could pull my addresses based on the
value in this field. However, I'm not skilled enough to figure out how to
write the query. (I assume I need an Iff statement of some sort but my syntax
isn't working.) For discussion's sake, let's call my fields:
[PAddress] [PCity][PState] [PZip] (physical address fields)
and
[PAddress] [PCity][PState] [PZip] (mailing address fields)
and
[DiffAddress] (the Yes/No field indicating if the mailing address is
different than the physical address.

I would prefer to do this with a query but am open to tips on redesign. It's
always good to know the right way to do things for future reference. Thanks
very much.
 
aAddress: iif(diffAddress,Maddress,Paddress)
aCity: iif(diffAddress,Mcity,Pcity)
aState: iif(diffAddress,Mstate,Pstate)

etc

After you get this working (just add those calculated
fields to a query), then you can work on a redesign if
you want one.

I use an address table.

I actually use a Join table as well, so I can have
multiple addresses for the same person, and multiple
persons for the same address, and Old addresses, and
Old contacts, but you shouldn't have that complexity
unless you need it.

To keep your system simple, you could have just two
idxAddress fields in your customer table, idxMailAddress
and idxPhysicalAddress, both pointing to the Address
Table. The two fields could be blank, or different,
or the same, both pointing to the same address record.

This may reduce or the size of your database (but not by
much)and is better than entering the same address twice,
but if your system (diffAddress) is working, it is hard
to say that anything else would really be better.

(david)
 
Given that you already have this structure, how about adding a command
button just after the physical address fields for "Same for mailing". When
the user clicks this button, it copies the physical fields into the mail
address fields, so they don't have to retype them:
Private cmdSameAddress_Click()
Me.MAddress = Me.PAddress
Me.MCity = Me.PCity
Me.MState = Me.PState
Me.MZip = Me.PZip
End Sub

If you then want to use the mailing address if anything exists in those
fields, and if not use the physical address, the Control Source of the text
box on your report would be something like this:
=IIf(IsNull(
[MAddress] + Chr(13) + Chr(10) & [MCity] + " " & [MState] + " " & [MZip],
[PAddress] + Chr(13) + Chr(10) & [PCity] + " " & [PState] + " " & [PZip],
[MAddress] + Chr(13) + Chr(10) & [MCity] + " " & [MState] + " " & [MZip])
 
Back
Top