Re: Mailing Address vs. Courier Address

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It seems as though you are asking me to give you a course in database
design. I can do that, but not in the nesgroups. I teach a class on the
first Tuesday of every month if you happen to live near Orlando, Florida.
You can also read one of several excellent books written so as not to
intimidate novices. The one I'd suggest is:

Database Design for Mere Mortals, by Michael J. Hernandez

http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=pd_bbs_sr_1/002-3867816-1214443?ie=UTF8&s=books&qid=1193236478&sr=8-1

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Billiam" <Billiam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D1AE930-E16F-4883-86C9-2B4E06E3F775@xxxxxxxxxxxxxxxx
Okay Arvin, grab a drink,a strong one.......maybe a few actually...
I got the job of designing a database...NO EXPERIENCE WITH THIS
WHATSOEVER.NONE. DARK ROOM, Do not even know I should be looking for a
lightswitch, let alone how to look. So day after day of fiddling around I
manage to build a database which works...I can produce reports and
queries, i
have a form which updates my one and only table...Okay have your drink
NOW...as the table contains over 50 fields...Now quick have the other
drink.
Yes, 50 fields, but how was I supposed to know this was a no-no, as I was
under the gun to get this done.

Anyways, now i know this is a no-no. I understand that it makes sense to
have tables grouped by subject, and that the items in that table should
solely depend on the Key...But how you are supposed to create the
relationships and eventually join the things up properly is BEYOND ME...as
in
MY BRAIN HURTS.

Here is part of my confusion: When I build the tblAddress, do I actually
type in the fields: CustomerID and AddressTypeID, or do I merely link them
in
relationships, or do I do both?

Second, Can I still use Addr1, addr2, addr3 instead of just 1 address
field
50 characters long as you suggested, to break the address up into RR#'s,
Apt
#'s etc, Street etc... Where does the Courier Address fields go--I suspect
in
a seperate Courier Table, but how do I link all this...I feel like I'm on
a
lazy susan from hell

Also, How do I handle City, province and Postal Code???

Hopefully you can break this down for me to grasp, as you can probably
tell
I am breaking down fast...Of course this just may be too much to ask...and
I
do not mean to ask you to overextend...but your help is truly, truly
appreciated!

I am now going to have a drink and wait for your response!
Billiam
"Arvin Meyer [MVP]" wrote:

I use only autonumbers (long integers) as the Primary Keys, except in a
very
few instances like the State Abbreviation in the States table, which I do
use a 2 character text datatype. I do not want users filling in numbers
because the inevitable mistakes lead to lost data or at the very least,
poor
data integrity. You can still use an autonumber and build update queries
to
add that number as a foreign key in your other existing tables.

The Address table(s) should look like:

tblAddress
AddressID - Primary Key - Long Integer (autonumber)
CustomerID - Foreign Key joined to tblCustomers Long Integer
Address - Text (50 characters is probably enough)
AddressTypeID - Foreign Key to tblAddressType - Long Integer

tblAddressType
AddressTypeID - Primary Key - Long Integer (autonumber)
AddressType - Text - (20 characters is probably enough)

Build a small subform in data*** or continuous form view for the
addresses, use a combo box to select the values from tblAddressType You
can
add a yes/no field if you wish to the address table for preferred use,
but
that shouldn't be necessary as you will only have a few AddressType
values
and when you start a mailing, you will be using Mailing or Both as the
criteria.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Billiam" <Billiam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C64977E8-92B4-45EC-A682-FA8338922A77@xxxxxxxxxxxxxxxx
Thank you for your very quick response...I am taking baby steps so
please
forgive me while I try to get my head around this...

1. The Address table would include both Mailing and Courier addresses?

2. Store the customer id as a foreign key...Can you explain this...is
this
in realtion to a Contacts table which holds the primary key, (in my
case,
my
primary key is a 4 digit numeral , which can begin with a number of
zeros...i.e 0004 or 0026 or 0979 or 1379 etc) and each of the address
tables
would be a foreign key?

3. I understand the purpose of an Address type field-- mailing, courier
or
both. Would this go in it's own table?

4. Should I have an input on my main data input form in the form of a
checkmark for prefferred means to have items sent?

I apologize, but I still do not get many of the concepts of database
design...but I am trying!

"Arvin Meyer [MVP]" wrote:

I'd build an Address table and store the CustomerID as a foreign key,
I'd
also add a field for AddressType linked to a lookup table with address
types. Types could include Mailing, Courier, Both, etc. So a query for
mailing would be Mailing or Both, and a query for courier would be
courier
or both

An alternate, although, not relational (rule-wise) is to add a Boolean
(yes/no) field for each of the address types.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Billiam" <Billiam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09A800F9-BC6E-4A6A-9B38-D576CBF17B06@xxxxxxxxxxxxxxxx
My VERY BASIC understanding of good database design tells me that I
should
always try to avoid entering duplicate information...I assume this
is
to
avoid database "bloat"...Here is my "problem...
What is the best idea when you have mailing address and courier
address
needs...and sometimes both are the same, and sometimes they are
completely
different. Is it best to have each set in different tables, or what
do
you
reccomend?

Also, I have a pretty standard set of contact info fields:
[FirstName]
[LastName] [Address1] [Address2] [Address3] [City] [Province]
[Postal
Code]...
Address 1 tthru 3 allow for individual address text fields such as
RR#5,
Street address and Apt for example. When I print a report of this
information, I would like it to resemble a label, in that the info
is
stacked
on top of each other, and if any of the address 2 or 3 fields is
empty,
they
do not print.

After hours of searching online, I finally came across some kind
soul
that
gave an example of how to concatenate fields with seperators like
hyphens
and
commas, but not carriage returns.

Would some kind soul provide the syntax or example for what almost
every
newbie will need to get a basic start in designing a database?

Thanks to you guys , those of us without the time or means can
sometimes
glean HOW to do something right, so THANK YOU THANK YOU THANK YOU
!!!!!!








.

Quantcast