Address Design Structure

Thanks very much for your input, Lynn...this stuff is so hard to get at
first, but SO important to get! Again, thanks to people like you taking the
time to respond, is so very appreciated!

Billiam

"Lynn Trapp" wrote:

On Fri, 1 Aug 2008 08:02:10 -0700, Billiam
wrote:

examples in our organization include:
Husband Wife and child, all instructors, at the same home address, and any
combination of work, billing and shipping addresses.
Also, many instructors at one school is another scenario.

Our business rules allow a maximum of 5 street addresses, and most use 4
addresses.




The possible need for up to 5 street addresses is definitely reaching
the point where you would need to separate them into a separate table.
I would recommend that you use the approach that Graham Mandeno has
outlined for you.
Lynn Trapp
www.ltcomputerdesigns.com

Billiam,

Thank you for your courtesy! From my point of view, your wanting to wrangle
with your database over time is more than fine and I applaud your wanting to
learn how to use Access properly so that you can really unleash its huge
potential. I am sure your ambition will get you over the learning curve hump
and eventually get you to that point. My point in offering you my help was
only to offer you an option of getting what you want done if you did not
have the time to put into the aforementioned process.

As you pursue your goal, post any questions you have to the newsgroup and
maybe we will meet again.

Good Luck!

Steve

"Billiam" wrote in message
...
Thank you again for your offer, Steve. This free forum, however, suits my
limited budget as a non-profit org. In addition, I think the real point is
that I do have a working non-normalized database, I am here to learn how
to
use Access properly so that I can really unleash its huge potential.
Sometimes the best learned lessons are the ones that you have to wrangle
with
over time, and I have found that the very knowledgeable and talented
people
here ( who do offer their services for free ) to be very helpful,and
thought
provoking. I certainly appreciate their efforts and applaud their
dedication
to what must sometimes be a very frustrating experience for them dealing
with
us newbies!

Billiam

"Steve" wrote:

I offered to help you on July 24 and you chose to listen to Klatuu
instead.
You did not get the help you need in the thread on July 24 so here you
are a
week later and no further ahead. What are you going to do if you do not
again get the help you need in this thread?

Steve


"Billiam" wrote in message
...
With the help of this forum, I am trying to correctly design my tables
to
be
normalized. I would like to break my design down into sections to work
on
just for ease of discussion and for the rest of people trying to figure
this
all out it may make it clearer to them also. I am a beginner in Access
and
TRYING HARD to figure it out so that I can properly design a database
instead
of the 55 field non-normlized one I currently use(:-0

Our non-profit organization has instructors. They may have many
addresses
and therefore address types. Here are the table designs so far:

One instructor can have many addresses
One address can have many instructors
Therefore this is a many to many realtionship, and so will be solved
with
a
junction/intermediate table (called instructorAddresses)

tblInstructors
InstructorID (autonumber, primary key,Long Integer, ref. integrity and
cascade updates)
SpecialID# (text field as this badge number starts with a series of
zeroes)
FirstName (text field)
MidName (text field)
LastName (text field)
Sex (or should this be a lookup table? If so, what relationship do I
create
and where? I know some of you are saying "well an instructor can only
be
one
sex at a time" which is true, but what do you do if an instructor is
also
transgendered and you need to keep a history of when that instructor
was
male, and when they were female)

tblAddresses
AddressID (autonumber,primary key,Long Integer, ref. integrity and
cascade
updates)
AddressLine1
AddressLine2
AddressLine3
City
PostalCode
ProvinceStateCounty
Country

tblInstructorAddresses (the junction or intermediate table)
InstructorAddressID (autonumber,primary key,Long Integer, ref.
integrity
and
cascade updates)
InstructorID (Foreign Key, number, Long integer, Duplicates OK,)
addressID (Foreign Key, number, Long integer, Duplicates OK,)

I have a one to many relationship created from tblInstructors to
tblInstructorAddresses
I have a one to many relationship created from tblAddresses to tbl
InstructorAddresses

Now, I obviously need an addressType Table (Home, Work,
Shipping,Billing
etc):

tblAddressType
AddressTypeID (autonumber,primary key,Long Integer, ref. integrity and
cascade updates)
AddressType (Text )
FromDate (date)
ToDate (date)

One Address can have many address types (i.e 123 maple Ave can be home
and
shipping and billing)

One AddressType has many Addresses (i.e the shipping address type can
vary
between a home address or a school address for one instructor depending
on
the day)

Am I right that this is a many to many relationship, or am I wrong that
AddressType has many addresses? Am I modeling this correctly as a many
to
many relationship?

Billiam

"Steve" wrote in message
...
My point in offering you my help was only to offer you an option of
getting what you want done


WRONG. Your "point" was just another feeble attempt to solicit work from a
group that is set up for FREE peer to peer support.

John...

Hi Billiam

Following your preference, my answers are inline...

It is not a technical possibility but a fact, and I do need to track the
history as the person is submitting to a Government office.

I understand Government requirements - say no more! G

While I do not
believe it is likely to happen with another instructor, what do you think
is
the best way to handle/link Patrick/Patricia's (not real name) records
since
the first name and middle names have also changed, and I have to produce
reports of this person's Instructor Activity as either male or female.

Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my pants
here, I'm wondering if it would be better to create a new instructor record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

If I understand you correctly, we are treating this as a many to many
relationship, and resolving that with the junction table
InstructorGenderHistory.

So one instructor can have many genders (as in Pat/Patricia)
One gender can have many Instructors.

Yes, that's the idea.

What was/is confusing me is that really an instructor can only be one sex
at
a time, so wouldn't that make it a one to one relationship??? And if so,
all
I would do is have a gender reference table with a Start date field?

The StartDate field cannot be in the reference table, because all the males
don't start being males on the same day!

The "point-in-time" value is ascertained by the StartDate field in the
junction table...

Note that you don't need an end date. The current gender (your most
common
query) is the record with the latest start date and the gender at a given
date in the past is the record with the latest start date BEFORE the
given
date.

Okay, that makes sense.

.... yes, I see the penny has dropped now :-)

The actual gender code could be a simple M or F, or it could be a foreign
key to a reference table (lookup tables have bad connotations!).

Do you mean they sometimes cause confusion between lookup fields and
lookup
tables?

Yes, reference table/lookup table are the same thing. I just prefer
"reference" :-)

Often it is useful to have a static table of other gender-related terms,
such as
pronouns (he/she, him/her, his/hers, etc).

Yes, this reference table is exactly what I would like, and the personal
pronouns are a great idea.

Glad you like it :-)

On the address question, the correct place for your AddressType FK field
is
in the junction table (tblInstructorAddresses).

Say you have two instructors: 1=John and 2=Mary.
They live together at 123 Maple Avenue (AddressID 1)
John works at 456 Elm Street (AddressID 2)
Mary works at 789 Oak Street (AddressID 3)

Your junction table has these fields:
iadID (optional primary key)
iadInstructor (FK to tblInstructors)
iadAddressType (simple code H/W/S/B or FK to tblAddressTypes)
iadAddress (FK to addresses)
iadStartDate (only if you need to keep address change history)

Your data looks like this:
(1) 1 H 1 (John lives at address 1)
(2) 2 H 1 (Mary lives at address 1)
(3) 1 W 2 (John works at address 2)
(4) 2 W 3 (Mary works at address 3)

The address type field is like the gender field - it's up to you whether
you
have a reference table or keep it as a simple one-letter code. Whatever
you
decide, the history tracking dates DO NOT belong in your reference table,
but in the junction table.

If you want an instructor to have at most ONE address of each type, then
make a unique composite key of iadInstructor and iadAddresstype.

Just a point on cascading relationships - it often makes sense to have
cascading deletes between a main table and a junction table, but it never
makes sense to have cascading updates on an autonumber field, because it
is
impossible to update an autonumber field.

Sorry, that was an error on my part, as I know an autonumber field cannot
be
updated. On this note, I was concerned that since i need to keep track of
the
history of addresses if selecting cascade updates/deletions would delete
the
history...I am correct that this is not the case, right? If they change
their
address that would remain in the InstructorAddress junction table since
their
is the unique StartDate field?

I sounds like you would not want to cascade deletes. In any case, the
related "many-side" records would olny be deleted if either the
tblInstructors record or the tblAddresses record were deleted.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for sticking with me Graham! My answers are inline.

"Graham Mandeno" wrote:
Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my pants
here, I'm wondering if it would be better to create a new instructor record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I also
want you to be aware of another problem I have. Some Instructors wish to have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they teach
in, an e-mail address, and a website link, and their qualifications. (BTW, I
have tblQualifications in a many to many with tblnstructors and a junction
table InstructorQualifications). Here is my problem...instructors must be
entered in by their Given names, and as you probably know, not everyone uses
their given name as their first name. Also, the cities entered in for address
are not necessarily where the instructor teaches. As you can probably see,
then, it is difficult to build the report to export to the website of all the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated doing it
that way. I know it sounds ridiculous, but should I have a first names table?
If I have a seperate table of cities, could I somehow use a kind of multiple
combo box to choose a maximum of three cities from the city table for each
instructor that wishes to be on the website , on an Instructor Info form? Is
there an easier way to solve this...as I said, I am really new to access, and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the time
this is blank, in which case FirstName is used. It handles the problem of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your forms
in a similar way.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Billiam" wrote in message
...
Thanks for sticking with me Graham! My answers are inline.

"Graham Mandeno" wrote:
Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications. (BTW,
I
have tblQualifications in a many to many with tblnstructors and a junction
table InstructorQualifications). Here is my problem...instructors must be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably see,
then, it is difficult to build the report to export to the website of all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for each
instructor that wishes to be on the website , on an Instructor Info form?
Is
there an easier way to solve this...as I said, I am really new to access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries (should
have studued Geography more ,g see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set this
up right, finally!

Billiam

"Graham Mandeno" wrote:

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the time
this is blank, in which case FirstName is used. It handles the problem of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your forms
in a similar way.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Billiam" wrote in message
...
Thanks for sticking with me Graham! My answers are inline.

"Graham Mandeno" wrote:
Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications. (BTW,
I
have tblQualifications in a many to many with tblnstructors and a junction
table InstructorQualifications). Here is my problem...instructors must be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably see,
then, it is difficult to build the report to export to the website of all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for each
instructor that wishes to be on the website , on an Instructor Info form?
Is
there an easier way to solve this...as I said, I am really new to access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

Hi Billiam

Like I said, you can go overboard with normalisation!

As far as I know, a city can NOT be in two countries. The London in England
is not the same as the London in Ontario, Canada. They bear no more
relationship to one another that my uncle George to George Bush. So, if you
have a table of Cities, London would have two records, distinguishable by
other fields such as StateProvinceCounty and/or Country.

You can also fall into the trap of making a box that requires many objects
to be distorted in order to fit it. For example, some countries do not have
any states/provinces/counties. Also, some countries have a concept of a
suburb within a city (while smaller towns don't have suburbs) and other
countries have the concept of a suburb or town as being the "city" within a
state.

It's difficult to provide a definitive answer! I can only caution you
against (a) over-normalising and (b) creating a design that doesn't fit all
cases.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



"Billiam" wrote in message
...
Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I
am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can
be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries
(should
have studued Geography more ,g see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set
this
up right, finally!

Billiam

"Graham Mandeno" wrote:

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the
time
this is blank, in which case FirstName is used. It handles the problem
of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you
might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any
logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to
have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form.
My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named
"MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your
forms
in a similar way.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Billiam" wrote in message
...
Thanks for sticking with me Graham! My answers are inline.

"Graham Mandeno" wrote:
Hmmm... this adds a whole new dimension. Actually, names can change
for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes,
with a
DateSuperseded field, so that all historic records can be linked to
the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish
to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications.
(BTW,
I
have tblQualifications in a many to many with tblnstructors and a
junction
table InstructorQualifications). Here is my problem...instructors must
be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably
see,
then, it is difficult to build the report to export to the website of
all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated
doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for
each
instructor that wishes to be on the website , on an Instructor Info
form?
Is
there an easier way to solve this...as I said, I am really new to
access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

Hi Graham,

Thanks again for the great advice. I had read somewhere else that a city
"could be in 2 different countries, and so was confused...thanks for clearing
that up.

I really appreciate you sticking with me on this, and for your timely
responses! I will keep plugging away at it. Have a great day,

Billiam
"Graham Mandeno" wrote:

Hi Billiam

Like I said, you can go overboard with normalisation!

As far as I know, a city can NOT be in two countries. The London in England
is not the same as the London in Ontario, Canada. They bear no more
relationship to one another that my uncle George to George Bush. So, if you
have a table of Cities, London would have two records, distinguishable by
other fields such as StateProvinceCounty and/or Country.

You can also fall into the trap of making a box that requires many objects
to be distorted in order to fit it. For example, some countries do not have
any states/provinces/counties. Also, some countries have a concept of a
suburb within a city (while smaller towns don't have suburbs) and other
countries have the concept of a suburb or town as being the "city" within a
state.

It's difficult to provide a definitive answer! I can only caution you
against (a) over-normalising and (b) creating a design that doesn't fit all
cases.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



"Billiam" wrote in message
...
Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I
am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can
be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries
(should
have studued Geography more ,g see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set
this
up right, finally!

Billiam

"Graham Mandeno" wrote:

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the
time
this is blank, in which case FirstName is used. It handles the problem
of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you
might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any
logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to
have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form.
My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named
"MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your
forms
in a similar way.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Billiam" wrote in message
...
Thanks for sticking with me Graham! My answers are inline.

"Graham Mandeno" wrote:
Hmmm... this adds a whole new dimension. Actually, names can change
for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes,
with a
DateSuperseded field, so that all historic records can be linked to
the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish
to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications.
(BTW,
I
have tblQualifications in a many to many with tblnstructors and a
junction
table InstructorQualifications). Here is my problem...instructors must
be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably
see,
then, it is difficult to build the report to export to the website of
all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated
doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for
each
instructor that wishes to be on the website , on an Instructor Info
form?
Is
there an easier way to solve this...as I said, I am really new to
access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam