Mailing Address vs. Courier Address

[Billiam]

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 !!!!!!

 

[Arvin Meyer [MVP]]

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.

 

[Billiam]

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!

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

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 !!!!!!

 

[Ken Sheridan]

Avoidance of duplication is not really to reduce 'bloat' but to ensure
integrity. If the same 'fact' is recorded more than once then it could be
recorded inconsistently, whereas if recorded only once the possibility of
inconsistencies is removed.

As regards your addresses it seems to me that AddressType is an attribute of
the relationship type between Customers and Addresses rather than of the
address AddressType is an attribute of the relationship type betwen Customers
and Addresses rather than of the address per se. Its theoretically possible
that you could have two customers at the same address whwre the addres type
for one is 'mailing' but not for the other.

So you'd have tables Customers and Addresses with primary key columns
CustomerID and AddressID. The relationship type between them is modelled on
a third table CustomerAddreses with columns CustomerID, AddressID and
AddressType. So if an address is both mailing and courier for one customer
it is represented by two rows in CustomerAddreses with identical values in
CustomerID and AddressID, but different values in AddressType. The last
column should reference an AddressTypes table, with referential integrity
enforced, to ensure that there is no inconsistency in the terms used for the
address types values in CustomerAddreses.

As regards the concatenation of the address lines from your contacts table
you can make use of the fact that Nulls propagate, i.e. anything + Null =
Null, to suppress the empty lines:

=(([FirstName] & " " & [LastName])+Chr(13)+Chr(10)) &
([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) &
([Address3]+Chr(13)+Chr(10)) & ([City]+Chr(13)+Chr(10)) &
([Province]+Chr(13)+Chr(10)) & [PostalCode]


The ANSI characters 13 and 10 are a carriage return/line feed.

I would point out an element of redundancy in your table, however. You
should not have a Province column in the table as this is determined by City.
Its what is known a 'transitively functionally dependent on the key'. All
non-key columns should be functionally dependent solely on the whole of the
key. You should have a separate Cities table with a Province column
referencing a Provinces table, with referential integrity enforced in each
relationship. This removes the redundancy and obviates any update anomalies.
Your report would be based on a query joining the tables.

You can find a demo of how to handle this type of hierarchical location data
using correlated combo boxes in forms at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative areas in my location of Parish, District
and County, but the principle is the same whatever the scale.

Ken Sheridan
Stafford, England

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 !!!!!!

 

[Billiam]

Ken, thank you for your answers! This will give me a better design than the
one I came up with originally, which works, but i am not sure how well it
will continue to work...
While rooting around in the forums I did come across the proper syntax for
the null and carriage returns, and I even, bravely, inserted commas after
name and address1, as well as city and province...HOWEVER, I am not sure
where to put the & ", " if the line is going to propagate a Null, as I would
be left with an extra ","...
can you help with that? (as in Address2 and Address3, which are usually null)

I will to setup the new tables and see where I get...In the meantime I will
keep reading and trying to figure this all out!

MANY MANY THANKS for your kind and clear assistance!

Avoidance of duplication is not really to reduce 'bloat' but to ensure
integrity. If the same 'fact' is recorded more than once then it could be
recorded inconsistently, whereas if recorded only once the possibility of
inconsistencies is removed.

As regards your addresses it seems to me that AddressType is an attribute of
the relationship type between Customers and Addresses rather than of the
address AddressType is an attribute of the relationship type betwen Customers
and Addresses rather than of the address per se. Its theoretically possible
that you could have two customers at the same address whwre the addres type
for one is 'mailing' but not for the other.

So you'd have tables Customers and Addresses with primary key columns
CustomerID and AddressID. The relationship type between them is modelled on
a third table CustomerAddreses with columns CustomerID, AddressID and
AddressType. So if an address is both mailing and courier for one customer
it is represented by two rows in CustomerAddreses with identical values in
CustomerID and AddressID, but different values in AddressType. The last
column should reference an AddressTypes table, with referential integrity
enforced, to ensure that there is no inconsistency in the terms used for the
address types values in CustomerAddreses.

As regards the concatenation of the address lines from your contacts table
you can make use of the fact that Nulls propagate, i.e. anything + Null =
Null, to suppress the empty lines:

=(([FirstName] & " " & [LastName])+Chr(13)+Chr(10)) &
([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) &
([Address3]+Chr(13)+Chr(10)) & ([City]+Chr(13)+Chr(10)) &
([Province]+Chr(13)+Chr(10)) & [PostalCode]


The ANSI characters 13 and 10 are a carriage return/line feed.

I would point out an element of redundancy in your table, however. You
should not have a Province column in the table as this is determined by City.
Its what is known a 'transitively functionally dependent on the key'. All
non-key columns should be functionally dependent solely on the whole of the
key. You should have a separate Cities table with a Province column
referencing a Provinces table, with referential integrity enforced in each
relationship. This removes the redundancy and obviates any update anomalies.
Your report would be based on a query joining the tables.

You can find a demo of how to handle this type of hierarchical location data
using correlated combo boxes in forms at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative areas in my location of Parish, District
and County, but the principle is the same whatever the scale.

Ken Sheridan
Stafford, England

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 !!!!!!

 

[Arvin Meyer [MVP]]

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 datasheet 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

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!

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

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
!!!!!!

 

[Billiam]

This worked very well for me---many thanks again, Ken!

To suppress the commas you need to include them in a parenthesised
expressions which will evaluate to Null if the column in question is Null.
This is done by using the + operator rather than the & concatenation operator
as when using the former Nulls propagate, but not when using the latter, like
so:

=(([FirstName] & " " & [LastName])+","+Chr(13)+Chr(10)) &
([Address1]+","+Chr(13)+Chr(10)) & ([Address2]+","+Chr(13)+Chr(10)) &
([Address3]+","+Chr(13)+Chr(10)) & ([City]+","+Chr(13)+Chr(10)) &
([Province]+","+Chr(13)+Chr(10)) & [PostalCode]

You have to be careful if a column is a number data type rather than a
string, however, as the + operator will be treated as the arithmetical
addition operator and give rise to an error, so you have to adopt a different
approach, using the IIf function to add the comma and cr/lf only if the
column is not Null, e.g.

([SomeNumberColumn] & IIf(IsNull([SomeNumberColumn]),Null,"," &
Chr(13)+Chr(10)))

Ken Sheridan
Stafford, England

Ken, thank you for your answers! This will give me a better design than the
one I came up with originally, which works, but i am not sure how well it
will continue to work...
While rooting around in the forums I did come across the proper syntax for
the null and carriage returns, and I even, bravely, inserted commas after
name and address1, as well as city and province...HOWEVER, I am not sure
where to put the & ", " if the line is going to propagate a Null, as I would
be left with an extra ","...
can you help with that? (as in Address2 and Address3, which are usually null)

I will to setup the new tables and see where I get...In the meantime I will
keep reading and trying to figure this all out!

MANY MANY THANKS for your kind and clear assistance!

Avoidance of duplication is not really to reduce 'bloat' but to ensure
integrity. If the same 'fact' is recorded more than once then it could be
recorded inconsistently, whereas if recorded only once the possibility of
inconsistencies is removed.

As regards your addresses it seems to me that AddressType is an attribute of
the relationship type between Customers and Addresses rather than of the
address AddressType is an attribute of the relationship type betwen Customers
and Addresses rather than of the address per se. Its theoretically possible
that you could have two customers at the same address whwre the addres type
for one is 'mailing' but not for the other.

So you'd have tables Customers and Addresses with primary key columns
CustomerID and AddressID. The relationship type between them is modelled on
a third table CustomerAddreses with columns CustomerID, AddressID and
AddressType. So if an address is both mailing and courier for one customer
it is represented by two rows in CustomerAddreses with identical values in
CustomerID and AddressID, but different values in AddressType. The last
column should reference an AddressTypes table, with referential integrity
enforced, to ensure that there is no inconsistency in the terms used for the
address types values in CustomerAddreses.

As regards the concatenation of the address lines from your contacts table
you can make use of the fact that Nulls propagate, i.e. anything + Null =
Null, to suppress the empty lines:

=(([FirstName] & " " & [LastName])+Chr(13)+Chr(10)) &
([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) &
([Address3]+Chr(13)+Chr(10)) & ([City]+Chr(13)+Chr(10)) &
([Province]+Chr(13)+Chr(10)) & [PostalCode]


The ANSI characters 13 and 10 are a carriage return/line feed.

I would point out an element of redundancy in your table, however. You
should not have a Province column in the table as this is determined by City.
Its what is known a 'transitively functionally dependent on the key'. All
non-key columns should be functionally dependent solely on the whole of the
key. You should have a separate Cities table with a Province column
referencing a Provinces table, with referential integrity enforced in each
relationship. This removes the redundancy and obviates any update anomalies.
Your report would be based on a query joining the tables.

You can find a demo of how to handle this type of hierarchical location data
using correlated combo boxes in forms at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative areas in my location of Parish, District
and County, but the principle is the same whatever the scale.

Ken Sheridan
Stafford, England

:

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 !!!!!!

 

[Billiam]

I'm so sorry, Arvin, I did not clue in to your responses...will read ASAP!!!
Thank you and sorry for missing your reponses...I've been crazy at work
lately!
Bill

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 datasheet 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

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!

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

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
!!!!!!

 

[Billiam]

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

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 datasheet 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

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!

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

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
!!!!!!

 

[Arvin Meyer [MVP]]

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-Desi...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

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

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 datasheet 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

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!

:

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

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
!!!!!!

 

[Billiam]

Hey Arvin,

A little hungover now, LOL.
Yes, could you please transfer all your database knowledge to me in an hour
or so as I will be visiting Disney World with my family, this Saturday for a
week, and might be able to squeeze in an hour if they clone me...
You are right...I just would like to be able to understand how to do
this...the frustrating thing is i have a working database that gives me what
I want...I just have 1 table and 50 fields, and so one day it's going to bite
me in the...
For now, I will use what I have, and hopefully be able to start from scratch
once i get it. There really seems to be a need for more layman style
tutorials...hopefully this book will work. Thank you for your advice and
reccomendation, i do intend to have a look at it.

I did search the forum, and it seems it is slightly difficult to GET
relationships...will keep trying!

Any chance you can try to simplify the procedure for my Address scenario...I
think I understand now thAT no info populates until you link the tables and
then build a form around them. I did try this, but was not sure where adress
type fit in...or my Courier address.
Anyways thank you very much for your polite and considerate help!
Billiam from Canada

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-Desi...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

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

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 datasheet 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


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!

:

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

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
!!!!!!

 

[Arvin Meyer [MVP]]

I'll do a Vulcan Mind Meld whenever you get here. Shouldn't take more than 5
minutes or so.

To link the tables in Access 2003 and earlier, go to Tools >>> Relationships
and add the 2 tables. You should have a PersonID field (or something like
it) in your Contacts table, and also a PersonID field (a foreign key) in the
Address table. Link the 2 tables by dragging the field from one to the
other. No the wizards will help you make a subform (and subreport) with the
correct relationships and linking.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hey Arvin,

A little hungover now, LOL.
Yes, could you please transfer all your database knowledge to me in an
hour
or so as I will be visiting Disney World with my family, this Saturday for
a
week, and might be able to squeeze in an hour if they clone me...
You are right...I just would like to be able to understand how to do
this...the frustrating thing is i have a working database that gives me
what
I want...I just have 1 table and 50 fields, and so one day it's going to
bite
me in the...
For now, I will use what I have, and hopefully be able to start from
scratch
once i get it. There really seems to be a need for more layman style
tutorials...hopefully this book will work. Thank you for your advice and
reccomendation, i do intend to have a look at it.

I did search the forum, and it seems it is slightly difficult to GET
relationships...will keep trying!

Any chance you can try to simplify the procedure for my Address
scenario...I
think I understand now thAT no info populates until you link the tables
and
then build a form around them. I did try this, but was not sure where
adress
type fit in...or my Courier address.
Anyways thank you very much for your polite and considerate help!
Billiam from Canada

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-Desi...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

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
:

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 datasheet 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


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!

:

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

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
!!!!!!

 

[Billiam]

Thanks Arvin for your help--the mind meld sounds great...
I did take your advice and drive to a town an hour away with a good
bookstore---they did not have the book you suggested, but another rack of
them with easy and thorough explanations...
Arvin, can I ask you about something NONE of the books seem to have, and why
I do not know, as it seems that it would be a very handy thing to have...

I am looking for code for a multiple Parameter query form that is based on
ONE FIELD.

The field is Region# (there are 7 regions, numbered 1 to 7) included in a
region table. The region table is linked to an Instructor table by the
Instructors ID# which is a 4 digit UNIQUE text Primary Key field--I had to
use text as the Instructor ID's begin with a series of zeros in some cases)

I want my end-user (boss) to be able to open the form, which would contain 8
buttons--7 region buttons and an ALL Regions option(Region1, region2,
region3, etc to a final choice of ALL Regions)

The goal is for my boss to be able to choose any combination of regions to
populate the query/report...all the books show is and/or options...but I need
to search one field for a combination of parameters...

there was an example with VBA in Access, which involved building a module,
and a form, but i could not get it to work...any ideas?

I'll do a Vulcan Mind Meld whenever you get here. Shouldn't take more than 5
minutes or so.

To link the tables in Access 2003 and earlier, go to Tools >>> Relationships
and add the 2 tables. You should have a PersonID field (or something like
it) in your Contacts table, and also a PersonID field (a foreign key) in the
Address table. Link the 2 tables by dragging the field from one to the
other. No the wizards will help you make a subform (and subreport) with the
correct relationships and linking.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hey Arvin,

A little hungover now, LOL.
Yes, could you please transfer all your database knowledge to me in an
hour
or so as I will be visiting Disney World with my family, this Saturday for
a
week, and might be able to squeeze in an hour if they clone me...
You are right...I just would like to be able to understand how to do
this...the frustrating thing is i have a working database that gives me
what
I want...I just have 1 table and 50 fields, and so one day it's going to
bite
me in the...
For now, I will use what I have, and hopefully be able to start from
scratch
once i get it. There really seems to be a need for more layman style
tutorials...hopefully this book will work. Thank you for your advice and
reccomendation, i do intend to have a look at it.

I did search the forum, and it seems it is slightly difficult to GET
relationships...will keep trying!

Any chance you can try to simplify the procedure for my Address
scenario...I
think I understand now thAT no info populates until you link the tables
and
then build a form around them. I did try this, but was not sure where
adress
type fit in...or my Courier address.
Anyways thank you very much for your polite and considerate help!
Billiam from Canada

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-Desi...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

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
:

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 datasheet 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


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!

:

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

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
!!!!!!