How to enforce subtypes/supertypes in Access 2000?

[Bob]

Hi folks,

I am creating a client database in MS Access with the following (simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in tblContacts and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At the
moment, I have two subforms - frmIndiv and frmOrg - which are positioned on
my main entry form.

The form contains a combo-box from which the user can select "Indiv" or
"Org" as the ContactType. Depending on the value in the combo-box, one or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType and
proceeds to enter details for this type of Contact. When this happens, the
ContactID for the current record in tblContacts table is mirrored in the
ContactID foreign key in the tblIndividuals table. This is what I want.

The problem is that once the user is finished (and whilst still in the same
record in the tblContacts table), the user can select "Org" from the
combo-box and be provided with a empty copy of the sub-form frmOrg. If the
user proceeds to enter data on the sub-form, the ContactID foreign key in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables (tblIndividuals and
tblOrganisations) having a record which points to the same ContactID in the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each record in
the subtype tables points to a record in the supertype table for which no
subtype record has already been created? (That's a mouthful - I hope it
makes sense). I've seen some references to "check constraints" on the
internet which I believe might help achieve my objective. But - so far as I
am aware - I can't impose check constraints on fields in Access 2000. (I
have seen a suggestion that this might be achieved by using ADO, but no code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[tina]

my first thought is: do you really need to to separate the individuals
records and organizations records into different tables? suggest you post
all the fields in each of those two tables so we can review them; perhaps we
can help you combine the two tables into one, with the addition of a single
field specifying either "individual" or "organization".

hth

 

[Bob]

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or business)
addresses, postal addresses and (residential or business) telecommunication
details for all contacts. The table tblIndividuals segregates the
individual-specific biographical information together with the individual's
work details. The EmployerID links back to the ContactID field in
tblContacts because we often end up acting for employees of existing
corporate clients or for muliple employees of non-client organisations. I
segregate the Organisation details so that I can record details for all
businesses (incorporated and unincorporated (ie sole-proprietorships,
partnerships, associations, churches etc)) that simply aren't relevant to
individuals. It also enables me to set up a separate table (tblOrgContacts)
to identify individual contacts for the organisation entities (a 1:Many
relationship is established between the two tables based on
tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our
purposes, we do not require any contacts to be linked with Individuals as
opposed to Organisations.

The above tables essentially constitute the whole set of "contacts" for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship)


Regards
Bob

 

[tina]

hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't know
that any alternate suggestions i made would actually be "better", rather
than simply different, or even not as good.

so to get back to your original question: AFAIK, table constraints are user
in SQL server, and perhaps other database types; but are not available in
Access. so you'll need to enforce the business rule at the form level. in
the main form, you can set up some code on the combo box control's
BeforeUpdate event to check the "other" subtype table for a record
containing the current Contact record's primary key value (a simple DCount()
function would handle that easily). if it exists, you can either cancel the
control's BeforeUpdate event, with a message box to tell the user to delete
the current subform record before adding a record to the other subform - or
tell the user that if they choose the alternate value in the combo box, the
record in the current subform will be deleted, and asking them to choose to
continue or cancel. if they cancel, then just cancel the BeforeUpdate event;
if they continue, then automatically delete the current subform record, and
then switch to the other subform.

hth

 

[tina]

btw, suggest you use one subform in your main form. when the user chooses an
option in the main form's combo box control, then use VBA to set the
subform's SourceObject, LinkChildFields, and LinkMasterFields properties for
the appropriate subform object. going from memory, you may need to put a
[SubformControlName].Form.Requery command at the end of the code, so that
the correct records will populate the chosen subform.

hth

 

[Bob]

Thanks Tina,

I'll give that go.


Regards
Bob

btw, suggest you use one subform in your main form. when the user chooses
an
option in the main form's combo box control, then use VBA to set the
subform's SourceObject, LinkChildFields, and LinkMasterFields properties
for
the appropriate subform object. going from memory, you may need to put a
[SubformControlName].Form.Requery command at the end of the code, so that
the correct records will populate the chosen subform.

hth

hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't know
that any alternate suggestions i made would actually be "better", rather
than simply different, or even not as good.

so to get back to your original question: AFAIK, table constraints are user
in SQL server, and perhaps other database types; but are not available in
Access. so you'll need to enforce the business rule at the form level. in
the main form, you can set up some code on the combo box control's
BeforeUpdate event to check the "other" subtype table for a record
containing the current Contact record's primary key value (a simple DCount()
function would handle that easily). if it exists, you can either cancel the
control's BeforeUpdate event, with a message box to tell the user to delete
the current subform record before adding a record to the other subform - or
tell the user that if they choose the alternate value in the combo box, the
record in the current subform will be deleted, and asking them to choose to
continue or cancel. if they cancel, then just cancel the BeforeUpdate event;
if they continue, then automatically delete the current subform record, and
then switch to the other subform.

hth


one key record which far

 

[Bob]

Tina,

I did have a few extra questions.

Firstly, in what way might you have otherwise structured the relevant
contact information? I'm a novice, so I'm open to ideas.

Secondly, without complicating things too much, I should mention that my
table structure includes two additional tables:

tblClientFiles - a junction table with ClientID and FileID specified as the
primary key; and

tblFiles - which, amongst other things, has the following three fields:
FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID)
FileNumber (txt - alphanumeric content (needs to be changed from time to
time, so not used as pk)
DateOpened - date/time
DateCompleted - date/time

To be honest, I struggle with insert/update commmands at the best of times.
It seems even more complicated once you start normalising and multiplying
the tables.

Anyway, my queries are:

(a) based on the table structure I have outlined, how would you construct
the sql strings to select, insert and delete a record into tblFiles?; and
(b) how would you construct the same strings where the "client" consists of
two persons (say a husband and a wife) who each have their own record in
tblContacts and tblClients?

By the way, someone else was pretty much asking the same question I asked at
the outset (about ensuring that only one of the subtype tables was updated)
- see http://www.dbforums.com/archive/index.php/t-1053752.html. One of the
responses to that post was as follows:

Because you do not specify what is in the "generic table" [equivalent to my
tblContacts], it is difficult
to offer specific suggestions. I can suggest that in the generic table, you
identify the type of contact [equivalent to my tblContactType], create your
query linking to both, and use an
outer join in order to allow one of the related tables to return a Null
entry. By outer join, I mean rightclicking on the join line between the data
sources in the Query, and choosing "All records from Contacts [ie my
tblContacts] and only those
that match from People [ie my tblIndividuals]" and "All records from
Contacts and only those that
match from Companies [ie my tblOrganisations]".

I don't quite understand what this means. Does it mean that in order to
perform select/insert/update commands programatically on, say, tblFiles or
tblOrganisations, I will need to do a whole lot of "outer joins"? If so,
what would one look like in my case?



Regard
Bob

Thanks Tina,

I'll give that go.


Regards
Bob

btw, suggest you use one subform in your main form. when the user chooses
an
option in the main form's combo box control, then use VBA to set the
subform's SourceObject, LinkChildFields, and LinkMasterFields properties
for
the appropriate subform object. going from memory, you may need to put a
[SubformControlName].Form.Requery command at the end of the code, so that
the correct records will populate the chosen subform.

hth

hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't
know
that any alternate suggestions i made would actually be "better", rather
than simply different, or even not as good.

so to get back to your original question: AFAIK, table constraints are user
in SQL server, and perhaps other database types; but are not available
in
Access. so you'll need to enforce the business rule at the form level.
in
the main form, you can set up some code on the combo box control's
BeforeUpdate event to check the "other" subtype table for a record
containing the current Contact record's primary key value (a simple DCount()
function would handle that easily). if it exists, you can either cancel the
control's BeforeUpdate event, with a message box to tell the user to delete
the current subform record before adding a record to the other subform - or
tell the user that if they choose the alternate value in the combo box, the
record in the current subform will be deleted, and asking them to choose to
continue or cancel. if they cancel, then just cancel the BeforeUpdate event;
if they continue, then automatically delete the current subform record, and
then switch to the other subform.

hth


Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business)
addresses, postal addresses and (residential or business)
telecommunication
details for all contacts. The table tblIndividuals segregates the
individual-specific biographical information together with the
individual's
work details. The EmployerID links back to the ContactID field in
tblContacts because we often end up acting for employees of existing
corporate clients or for muliple employees of non-client
organisations. I
segregate the Organisation details so that I can record details for
all
businesses (incorporated and unincorporated (ie sole-proprietorships,
partnerships, associations, churches etc)) that simply aren't relevant to
individuals. It also enables me to set up a separate table
(tblOrgContacts)
to identify individual contacts for the organisation entities (a
1:Many
relationship is established between the two tables based on
tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For
our
purposes, we do not require any contacts to be linked with Individuals as
opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv" or
"Org" as the ContactType. Depending on the value in the combo-box, one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType and
proceeds to enter details for this type of Contact. When this happens,
the
ContactID for the current record in tblContacts table is mirrored
in
the
ContactID foreign key in the tblIndividuals table. This is what I want.

The problem is that once the user is finished (and whilst still in the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form frmOrg. If
the
user proceeds to enter data on the sub-form, the ContactID foreign key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each record
in
the subtype tables points to a record in the supertype table for which
no
subtype record has already been created? (That's a mouthful - I
hope it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so far
as
I
am aware - I can't impose check constraints on fields in Access
2000.
(I
have seen a suggestion that this might be achieved by using ADO,
but no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[tina]

comments inline.

Tina,

I did have a few extra questions.

Firstly, in what way might you have otherwise structured the relevant
contact information? I'm a novice, so I'm open to ideas.

sorry, didn't mean to be a tease. as i said, since i haven't analyzed the
business process, i couldn't state an alternate tables/relationships design
with confidence. i tend to stay very much "inside the box" on
table/relationship design, and have trouble with the more unusual
configurations that are necessary to model some real-world relationships. i
wish we could get Tim Ferguson into this thread; he's about the best i've
seen in these newsgroups re solving non-standard relationships and
explaining the configurations in a way that is easy to understand.

Secondly, without complicating things too much, I should mention that my
table structure includes two additional tables:

tblClientFiles - a junction table with ClientID and FileID specified as the
primary key; and

tblFiles - which, amongst other things, has the following three fields:
FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID)
FileNumber (txt - alphanumeric content (needs to be changed from time to
time, so not used as pk)
DateOpened - date/time
DateCompleted - date/time

hmm, i do have to wonder here about your relationships. if tblFiles is
related to tblClients via a junction table called tblClientFiles, then why
is tblFiles also directly linked to tblClients? use of a junction table
essentially says that on client may be related to many files, and one file
may be related to many clients - a many-to-many relationship that the
junction table resolves into two one-to-many relationships. but a direct
relationship says that one client may be related to many files, but each
file is related to only one client - which is a straightforward one-to-many
relationship. so we have an apparent contradiction here.

To be honest, I struggle with insert/update commmands at the best of times.
It seems even more complicated once you start normalising and multiplying
the tables.

Anyway, my queries are:

(a) based on the table structure I have outlined, how would you construct
the sql strings to select, insert and delete a record into tblFiles?; and
(b) how would you construct the same strings where the "client" consists of
two persons (say a husband and a wife) who each have their own record in
tblContacts and tblClients?

By the way, someone else was pretty much asking the same question I asked at
the outset (about ensuring that only one of the subtype tables was updated)
- see http://www.dbforums.com/archive/index.php/t-1053752.html. One of the
responses to that post was as follows:

Because you do not specify what is in the "generic table" [equivalent to my
tblContacts], it is difficult
to offer specific suggestions. I can suggest that in the generic table, you
identify the type of contact [equivalent to my tblContactType], create your
query linking to both, and use an
outer join in order to allow one of the related tables to return a Null
entry. By outer join, I mean rightclicking on the join line between the data
sources in the Query, and choosing "All records from Contacts [ie my
tblContacts] and only those
that match from People [ie my tblIndividuals]" and "All records from
Contacts and only those that
match from Companies [ie my tblOrganisations]".

I don't quite understand what this means. Does it mean that in order to
perform select/insert/update commands programatically on, say, tblFiles or
tblOrganisations, I will need to do a whole lot of "outer joins"? If so,
what would one look like in my case?

sorry, hon, the above is all way too theoretical for me; i'm very much a
nuts-and-bolts person. if you can lay out a specific scenario, explaining
what the setup is and what you're trying to do in this situation, i'll work
on a solution with you. in any event, i can't help at all until we iron out
the issue of the real-world relationship between tblFiles and tblClients.

hth

Regard
Bob

Thanks Tina,

I'll give that go.


Regards
Bob

btw, suggest you use one subform in your main form. when the user chooses
an
option in the main form's combo box control, then use VBA to set the
subform's SourceObject, LinkChildFields, and LinkMasterFields properties
for
the appropriate subform object. going from memory, you may need to put a
[SubformControlName].Form.Requery command at the end of the code, so that
the correct records will populate the chosen subform.

hth


hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't
know
that any alternate suggestions i made would actually be "better", rather
than simply different, or even not as good.

so to get back to your original question: AFAIK, table constraints are
user
in SQL server, and perhaps other database types; but are not available
in
Access. so you'll need to enforce the business rule at the form level.
in
the main form, you can set up some code on the combo box control's
BeforeUpdate event to check the "other" subtype table for a record
containing the current Contact record's primary key value (a simple
DCount()
function would handle that easily). if it exists, you can either cancel
the
control's BeforeUpdate event, with a message box to tell the user to
delete
the current subform record before adding a record to the other subform -
or
tell the user that if they choose the alternate value in the combo box,
the
record in the current subform will be deleted, and asking them to choose
to
continue or cancel. if they cancel, then just cancel the BeforeUpdate
event;
if they continue, then automatically delete the current subform record,
and
then switch to the other subform.

hth


Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business)
addresses, postal addresses and (residential or business)
telecommunication
details for all contacts. The table tblIndividuals segregates the
individual-specific biographical information together with the
individual's
work details. The EmployerID links back to the ContactID field in
tblContacts because we often end up acting for employees of existing
corporate clients or for muliple employees of non-client
organisations.
I
segregate the Organisation details so that I can record details for
all
businesses (incorporated and unincorporated (ie sole-proprietorships,
partnerships, associations, churches etc)) that simply aren't relevant
to
individuals. It also enables me to set up a separate table
(tblOrgContacts)
to identify individual contacts for the organisation entities (a
1:Many
relationship is established between the two tables based on
tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For
our
purposes, we do not require any contacts to be linked with Individuals
as
opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for
my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv"
or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored
in
the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form frmOrg.
If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID
in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I
hope
it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access
2000.
(I
have seen a suggestion that this might be achieved by using ADO,
but
no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Graham Mandeno]

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of entity
subclassing at the engine level. The only way to do that would be to have
two FK fields in tblContacts - one for IndivID and one for OrgID, and have a
table-level constraint (validation rule) to specify that they cannot both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate (AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.

 

[Bob]

Hi Tina,

Re the relationship between tblFiles and tblClientFiles, I think I got
carried away. There is no link between tblFiles and tblClients. The
tblFiles.FileID has a 1:Many relationship with tblClientFiles.FileID.
Sorry about that.

The real-world scenario I have is this:

The database is for a small accounting firm.

The firm opens one file per new matter. Each time a new file is
opened, the following details are recorded about the file:

(a) the file's number;

(b) the date the file is opened;

(c) the client's name, and various contact details (ie all known
addresses, telephone numbers and email addresses) - the client can be a
single private individual, or a couple; or an organisation
(company/partnership/association).

(d) a one or two line description of the matter - eg tax returns for
2005-2006.

As the file progresses, it is usually the case that a number of
contacts specifically for that file might develop (eg a specific person
at the tax office). The same contact might not apply to other files.
I call these file-specific third-party contacts.

There will usually be a number of contacts that relate specifically to
the client - eg the director of a corporate client. I call these
client-specific contacts.

There will also be third-party contacts with whom the firm liaises on
various files - eg a particular supervisor for a specific section in
the tax office. I call these generic third-party contacts.


I want to do the following:

(a) record each new contact as it is created - regardless of whether
the contact is a client, a file specific third-party contact, a
client-specific client-contact, or a generic third-party contact.
Hence my tblContacts, tblIndividuals and tblOrganisations tables.

(b) readily identify which contacts are clients, and by whom they were
referred. Hence, my tblClients table. The obvious rule here is that
an individual or organisation cannot be client unless they are already
a contact.

(c) create a record that links a particular client (or two clients in
the case of a spousal relationship) to a specific file (obviously the
same client(s) may have other files - hence why I have set up a
junction table between tblClients and tblFiles).

(d) record the required file details. Hence my tblFiles table.

(e) identify the client-specific contacts (hence my tblOrgContacts
table). The same contact can be a contact for more than one client (eg
where a person is the director of multiple companies). My ultimate
intention is to have this information displayed to the user on a form
each time they open a record in tblClients or tblFiles.

(f) finally, identify the file-specific and generic third-party
contacts. This information will also be displayed on a form each time
a record in tblFiles is opened.

My immediate aim is to ensure that anyone who wants to lookup the
contact details for a particular File or Client can do so by simply
opening a form linked to the relevant table and have all of the
information readily displayed for viewing, updating or deleting.

Now, when I create (insert) a record for a new File, what I **think** I
need to do is this:

(i) check if there is already a tblContact record for the Client - if
not, create one. Obviously, if a new contact record is created, I'm
going to need to grab the latest ContactID. Also, inserting a new
record would obviously involve:
(a) ensuring that there isn't already a record in either of the
"subtype" tables (Individuals or Organisations); and
(b) creating records in tblOrgContacts if necessary.

(ii) check if the Contact has already been identified as a client - if
not, create a record in tblClients;

(iii) create the new record in tblFiles; and

(iv) (finally?) create the new record(s) in tblClientFiles.

I assume that the update and delete processes will need to traverse
roughly the same procedure.

What I'm not clear on is:

(1) do I need a whole bunch of joins every time I insert and/or update
and/or delete a record in tblFiles (say)?; and
(2) how do I construct, say, an insert sql statement (for a new file)
that involves so many tables? Do I need to nest a whole bunch of select
statements somewhere; if so, how?


Thanks
Bob

comments inline.

Tina,

I did have a few extra questions.

Firstly, in what way might you have otherwise structured the relevant
contact information? I'm a novice, so I'm open to ideas.

sorry, didn't mean to be a tease. as i said, since i haven't analyzed the
business process, i couldn't state an alternate tables/relationships design
with confidence. i tend to stay very much "inside the box" on
table/relationship design, and have trouble with the more unusual
configurations that are necessary to model some real-world relationships. i
wish we could get Tim Ferguson into this thread; he's about the best i've
seen in these newsgroups re solving non-standard relationships and
explaining the configurations in a way that is easy to understand.

Secondly, without complicating things too much, I should mention that my
table structure includes two additional tables:

tblClientFiles - a junction table with ClientID and FileID specified as the
primary key; and

tblFiles - which, amongst other things, has the following three fields:
FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID)
FileNumber (txt - alphanumeric content (needs to be changed from time to
time, so not used as pk)
DateOpened - date/time
DateCompleted - date/time

hmm, i do have to wonder here about your relationships. if tblFiles is
related to tblClients via a junction table called tblClientFiles, then why
is tblFiles also directly linked to tblClients? use of a junction table
essentially says that on client may be related to many files, and one file
may be related to many clients - a many-to-many relationship that the
junction table resolves into two one-to-many relationships. but a direct
relationship says that one client may be related to many files, but each
file is related to only one client - which is a straightforward one-to-many
relationship. so we have an apparent contradiction here.

To be honest, I struggle with insert/update commmands at the best of times.
It seems even more complicated once you start normalising and multiplying
the tables.

Anyway, my queries are:

(a) based on the table structure I have outlined, how would you construct
the sql strings to select, insert and delete a record into tblFiles?; and
(b) how would you construct the same strings where the "client" consists of
two persons (say a husband and a wife) who each have their own record in
tblContacts and tblClients?

By the way, someone else was pretty much asking the same question I asked at
the outset (about ensuring that only one of the subtype tables was updated)
- see http://www.dbforums.com/archive/index.php/t-1053752.html. One of the
responses to that post was as follows:

Because you do not specify what is in the "generic table" [equivalent to my
tblContacts], it is difficult
to offer specific suggestions. I can suggest that in the generic table, you
identify the type of contact [equivalent to my tblContactType], create your
query linking to both, and use an
outer join in order to allow one of the related tables to return a Null
entry. By outer join, I mean rightclicking on the join line between the data
sources in the Query, and choosing "All records from Contacts [ie my
tblContacts] and only those
that match from People [ie my tblIndividuals]" and "All records from
Contacts and only those that
match from Companies [ie my tblOrganisations]".

I don't quite understand what this means. Does it mean that in order to
perform select/insert/update commands programatically on, say, tblFiles or
tblOrganisations, I will need to do a whole lot of "outer joins"? If so,
what would one look like in my case?

sorry, hon, the above is all way too theoretical for me; i'm very much a
nuts-and-bolts person. if you can lay out a specific scenario, explaining
what the setup is and what you're trying to do in this situation, i'll work
on a solution with you. in any event, i can't help at all until we iron out
the issue of the real-world relationship between tblFiles and tblClients.

hth

Regard
Bob

Thanks Tina,

I'll give that go.


Regards
Bob


btw, suggest you use one subform in your main form. when the user chooses
an
option in the main form's combo box control, then use VBA to set the
subform's SourceObject, LinkChildFields, and LinkMasterFields properties
for
the appropriate subform object. going from memory, you may need to put a
[SubformControlName].Form.Requery command at the end of the code, so that
the correct records will populate the chosen subform.

hth


hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't
know
that any alternate suggestions i made would actually be "better", rather
than simply different, or even not as good.

so to get back to your original question: AFAIK, table constraints are
user
in SQL server, and perhaps other database types; but are not available
in
Access. so you'll need to enforce the business rule at the form level.
in
the main form, you can set up some code on the combo box control's
BeforeUpdate event to check the "other" subtype table for a record
containing the current Contact record's primary key value (a simple
DCount()
function would handle that easily). if it exists, you can either cancel
the
control's BeforeUpdate event, with a message box to tell the user to
delete
the current subform record before adding a record to the other subform -
or
tell the user that if they choose the alternate value in the combo box,
the
record in the current subform will be deleted, and asking them to choose
to
continue or cancel. if they cancel, then just cancel the BeforeUpdate
event;
if they continue, then automatically delete the current subform record,
and
then switch to the other subform.

hth


Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business)
addresses, postal addresses and (residential or business)
telecommunication
details for all contacts. The table tblIndividuals segregates the
individual-specific biographical information together with the
individual's
work details. The EmployerID links back to the ContactID field in
tblContacts because we often end up acting for employees of existing
corporate clients or for muliple employees of non-client
organisations.
I
segregate the Organisation details so that I can record details for
all
businesses (incorporated and unincorporated (ie sole-proprietorships,
partnerships, associations, churches etc)) that simply aren't relevant
to
individuals. It also enables me to set up a separate table
(tblOrgContacts)
to identify individual contacts for the organisation entities (a
1:Many
relationship is established between the two tables based on
tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For
our
purposes, we do not require any contacts to be linked with Individuals
as
opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for
my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv"
or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored
in
the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form frmOrg.
If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID
in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I
hope
it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access
2000.
(I
have seen a suggestion that this might be achieved by using ADO,
but
no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Bob]

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of entity
subclassing at the engine level. The only way to do that would be to have
two FK fields in tblContacts - one for IndivID and one for OrgID, and have a
table-level constraint (validation rule) to specify that they cannot both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate (AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table tblIndividuals
segregates the individual-specific biographical information together with
the individual's work details. The EmployerID links back to the ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc)) that
simply aren't relevant to individuals. It also enables me to set up a
separate table (tblOrgContacts) to identify individual contacts for the
organisation entities (a 1:Many relationship is established between the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts" for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship)


Regards
Bob

 

[Bob]

Graham,

Nevermind my last post re the table-level constraints. I just re-read
yours: BeforeUpdate event - got it. I'm a bit slow today. :-D

I'm still interested re the save prompt question though.


Regards
Bob

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of entity
subclassing at the engine level. The only way to do that would be to have
two FK fields in tblContacts - one for IndivID and one for OrgID, and have a
table-level constraint (validation rule) to specify that they cannot both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate (AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table tblIndividuals
segregates the individual-specific biographical information together with
the individual's work details. The EmployerID links back to the ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc)) that
simply aren't relevant to individuals. It also enables me to set up a
separate table (tblOrgContacts) to identify individual contacts for the
organisation entities (a 1:Many relationship is established between the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts" for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship)


Regards
Bob


my first thought is: do you really need to to separate the individuals
records and organizations records into different tables? suggest you post
all the fields in each of those two tables so we can review them; perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At the
moment, I have two subforms - frmIndiv and frmOrg - which are positioned
on
my main entry form.

The form contains a combo-box from which the user can select "Indiv" or
"Org" as the ContactType. Depending on the value in the combo-box, one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType and
proceeds to enter details for this type of Contact. When this happens,
the
ContactID for the current record in tblContacts table is mirrored in the
ContactID foreign key in the tblIndividuals table. This is what I want.

The problem is that once the user is finished (and whilst still in the
same
record in the tblContacts table), the user can select "Org" from the
combo-box and be provided with a empty copy of the sub-form frmOrg. If
the
user proceeds to enter data on the sub-form, the ContactID foreign key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables (tblIndividuals
and
tblOrganisations) having a record which points to the same ContactID in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each record
in
the subtype tables points to a record in the supertype table for which
no
subtype record has already been created? (That's a mouthful - I hope it
makes sense). I've seen some references to "check constraints" on the
internet which I believe might help achieve my objective. But - so far
as
I
am aware - I can't impose check constraints on fields in Access 2000. (I
have seen a suggestion that this might be achieved by using ADO, but no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Bob]

Hi Jamie,

How exactly do I switch to ANSI mode?


Regard
Bob

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot both
be
Null.

That's got to be a misstatement!

The standard approach to implementing such subclass is to have a
superclass for both individuals and organizations; this can certainly
be implemented in Jet using referential integrity. If there are only
two types, each of which needs a base table in the schema, a
ContactTypes table is overkill:

[I'm avoiding PRIMARY KEY in favour of NOT NULL UNIQUE because
clustering on disk is OT; syntax requires ANSI query mode in the Access
UI or an OLE DB (e.g. ADO) connection.]

CREATE TABLE LegalPersons (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
UNIQUE (legal_person_type, legal_person_ID)
);

CREATE TABLE Individuals (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Indiv'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Org'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
address_line_1 VARCHAR(30) NOT NULL...
<<needs a key>>
);


OK, I know what you are thinking: how to prevent a contact being
created for a row in LegalPersons that does not exist in either
Individuals or Organizations?

Something like this:

CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.legal_person_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.legal_person_ID = Contacts.legal_person_ID)
),
...

Even you implied structure (i.e. omitting the LegalPerson table), you
*can* implement the foreign keys in Jet: using the same CHECKs as
above:

CREATE TABLE Individuals (
individual_ID INTEGER NOT NULL UNIQUE,
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
organization_ID INTEGER NOT NULL UNIQUE,
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.individual_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.organization_ID = Contacts.legal_person_ID)
),
address_line_1 VARCHAR(30) NOT NULL...
<<needs a key>>
);

I would not recommend 'mixing' identifiers in this way. However, I
trust the above is sufficient for you to realize that it is possible to
enforce such constraints at the engine level in Jet.

Jamie.

 

[Graham Mandeno]

Hi Jamie

Yes, it was a misstatement - I meant to say "cannot both be NON-Null"

Your solution involving duplicating the sub-type in the sub-tables and using
a two-field relationship is simple, elegant and brilliant. You say this is
"the standard approach" but I can't believe that I have never come across it
before, and it is so simple that I'm ashamed I never thought of it myself!

One question though - is there any reason not to put the "common" fields in
the superclass table? In other words, why not combine the Contacts and
LegalPersons tables?
--
Thanks!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot both
be
Null.

That's got to be a misstatement!

The standard approach to implementing such subclass is to have a
superclass for both individuals and organizations; this can certainly
be implemented in Jet using referential integrity. If there are only
two types, each of which needs a base table in the schema, a
ContactTypes table is overkill:

[I'm avoiding PRIMARY KEY in favour of NOT NULL UNIQUE because
clustering on disk is OT; syntax requires ANSI query mode in the Access
UI or an OLE DB (e.g. ADO) connection.]

CREATE TABLE LegalPersons (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
UNIQUE (legal_person_type, legal_person_ID)
);

CREATE TABLE Individuals (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Indiv'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
legal_person_ID INTEGER NOT NULL UNIQUE,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type = 'Org'),
UNIQUE (legal_person_type, legal_person_ID),
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
FOREIGN KEY (legal_person_type, legal_person_ID)
REFERENCES LegalPersons (legal_person_type, legal_person_ID),
address_line_1 VARCHAR(30) NOT NULL...
<<needs a key>>
);


OK, I know what you are thinking: how to prevent a contact being
created for a row in LegalPersons that does not exist in either
Individuals or Organizations?

Something like this:

CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.legal_person_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.legal_person_ID = Contacts.legal_person_ID)
),
...

Even you implied structure (i.e. omitting the LegalPerson table), you
*can* implement the foreign keys in Jet: using the same CHECKs as
above:

CREATE TABLE Individuals (
individual_ID INTEGER NOT NULL UNIQUE,
last_name VARCHAR(35) NOT NULL...
);

CREATE TABLE Organizations (
organization_ID INTEGER NOT NULL UNIQUE,
trading_name NVARCHAR(255) NOT NULL...
);

CREATE TABLE Contacts (
legal_person_ID INTEGER NOT NULL,
legal_person_type VARCHAR(5) NOT NULL,
CHECK (legal_person_type IN ('Indiv', 'org')),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Org', 1, COUNT(*))
FROM Individuals
WHERE Individuals.individual_ID = Contacts.legal_person_ID)
),
CHECK (1 = (SELECT IIF(Contacts.legal_person_type = 'Indiv', 1,
COUNT(*))
FROM Organizations
WHERE Organizations.organization_ID = Contacts.legal_person_ID)
),
address_line_1 VARCHAR(30) NOT NULL...
<<needs a key>>
);

I would not recommend 'mixing' identifiers in this way. However, I
trust the above is sufficient for you to realize that it is possible to
enforce such constraints at the engine level in Jet.

Jamie.

 

[Graham Mandeno]

Hi Bob

The solution that Jamie gave is simple and elegant. To translate it into
Access table design terms (which might be more familiar to you than ANSI-92
DDL statements!):

Add a unique index to tblContacts involving ContactID AND ContactType. Next
add a ContactType field to both tblIndividuals and tblOrganisations and for
each, set the default value to the corresponding contact type and set the
validation rule to =<contact type> and set required=Yes. (In other words, an
individual MUST be an individual and cannot be an organisation, and
vice-versa).

Now, add a 1:1 relationship with referential integrity between
ContactID/ContactType in tblContacts and tblIndividuals, and the same for
tblOrganisations.

Now the engine will look after the integrity for you. If a record in
tblContacts has a matching record in one of the other tables, then the
contact record can neither be deleted, nor changed to the other contact
type, unless the related subclass record is first deleted.

To answer your other questions:

1. A table-level validation rule can be created in the Table Properties
window (View>Properties in design view). For example:
([IndivID] Is Not Null) Xor ([OrgID] Is Not Null)
However, I would NOT use this two-field approach for your particular
problem.

2. You can force a prompt before saving a record using the form's
BeforeUpdate event.
Select case MsgBox("Save changes?", vbYesNoCancel)
case vbYes
' do nothing
case vbNo
Cancel = True
Me.Undo
case vbCancel
cancel = true
End Select

3. You can set ANSI-92 mode via Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot both
be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate
(AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table tblIndividuals
segregates the individual-specific biographical information together
with
the individual's work details. The EmployerID links back to the
ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can
record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc)) that
simply aren't relevant to individuals. It also enables me to set up a
separate table (tblOrgContacts) to identify individual contacts for the
organisation entities (a 1:Many relationship is established between the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require
any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts" for
my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select "Indiv"
or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored in
the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from the
combo-box and be provided with a empty copy of the sub-form frmOrg.
If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables (tblIndividuals
and
tblOrganisations) having a record which points to the same ContactID
in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I hope
it
makes sense). I've seen some references to "check constraints" on the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access 2000.
(I
have seen a suggestion that this might be achieved by using ADO, but
no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Graham Mandeno]

Thanks, Jamie.
--
Cheers,
Graham

Yes, the shared fields would be best in the superclass table. As I'm
sure you are aware, there is distinction between a shared attribute
(inheritance) and two attributes sharing a name (coincidence) e.g.
patient may have a legal name attribute and a medication may have a
legal name attribute but they do have a shared superclass.


It's probably a 1:m relationship e.g. an organization may have more
than one contact. Also, an organization may have a contact that is an
individual who has a collection of contacts in the context of being a
contact for that organization as well as contacts in their own right
e.g. work, home (and they may have more than one home), etc. There is
certainly potential for the contacts to be a structure: each contact
type - postal, email, web, telephone, fax, etc - should have probably
be in a table of their own, with perhaps shared super classes (e.g.
telephone and fax)... this can quickly get out of hand!

One issue here is whether all contact types share a superclass; if so,
what would the identifier be? There is a similar issue with the
proposed LegalPerson superclass: is there a real life identifier shared
by organizations and individuals?

Jamie.

 

[Bob]

Hi Graham,

This doesn't seem to be working properly for me.

Here's what I've done so far:

In tblContacts:
- left the pk as it was (ie tblContactsID remains an autonumber pk field)
- created a new unique multifield index based on ContactID and ContactTypeID
as per the instructions in the Access help file

In tblIndividuals:
- deleted the original autonumber pk field (IndividualsID)
- converted the existing ContactID (number) field as the new pk
- inserted a new ContactTypeID (number) field
- set the "default value" property to 1 (corresponding to the ContactTypeID
for the "Indiv" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and ContactTypeID
as per the instructions in the Access help file

In tblOrganisations
- deleted the original autonumber pk field (OrganisationsID)
- converted the existing ContactID (number) field as the new pk
- set the "default value" property to 2 (corresponding to the ContactTypeID
for the "Orgs" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and ContactTypeID
as per the instructions in the Access help file

I then created a 1:1 relationship based on the combined ContactID and
ContactTypeID fields between tblContacts and tblIndividuals.
I did this by selected the two fields in tblContacts and dragging them over
to tblIndividuals. I created a 1:1 relationship between tblContacts
and tblOrganisations in the same way.

I then deleted all existing test data from tblContacts, tblIndividuals and
tblOrganisations - starting with a clean slate.

I then opened my client data-entry form. The main form has all fields from
tblContacts. The subform is unbound, but I have
inserted vba into the AfterUpdate event section of the form to ensure that
the SourceObject of the subform control is changed to the required subform
depending on the
selected ContactType (selected from a combo box).

I can enter the first record of either tblIndividuals or tblOrganisations
without difficulty - once I open the required subform the value of ContactID
is
the same as the autonumber pk in tblContacts, and the value of the
ContactTypeID defaults to the relevant default value.

However, after having entered this first record, if I then close and reopen
the main form, I am prevented from creating any new reords in either table
by a
popup box which states "The record cannot be deleted or changed because the
table "Individuals" includes related records."

What could be causing this error? There are no "related" records in
existence!


TIA
Bob

Hi Bob

The solution that Jamie gave is simple and elegant. To translate it into
Access table design terms (which might be more familiar to you than
ANSI-92 DDL statements!):

Add a unique index to tblContacts involving ContactID AND ContactType.
Next add a ContactType field to both tblIndividuals and tblOrganisations
and for each, set the default value to the corresponding contact type and
set the validation rule to =<contact type> and set required=Yes. (In other
words, an individual MUST be an individual and cannot be an organisation,
and vice-versa).

Now, add a 1:1 relationship with referential integrity between
ContactID/ContactType in tblContacts and tblIndividuals, and the same for
tblOrganisations.

Now the engine will look after the integrity for you. If a record in
tblContacts has a matching record in one of the other tables, then the
contact record can neither be deleted, nor changed to the other contact
type, unless the related subclass record is first deleted.

To answer your other questions:

1. A table-level validation rule can be created in the Table Properties
window (View>Properties in design view). For example:
([IndivID] Is Not Null) Xor ([OrgID] Is Not Null)
However, I would NOT use this two-field approach for your particular
problem.

2. You can force a prompt before saving a record using the form's
BeforeUpdate event.
Select case MsgBox("Save changes?", vbYesNoCancel)
case vbYes
' do nothing
case vbNo
Cancel = True
Me.Undo
case vbCancel
cancel = true
End Select

3. You can set ANSI-92 mode via Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot
both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate
(AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table tblIndividuals
segregates the individual-specific biographical information together
with
the individual's work details. The EmployerID links back to the
ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can
record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc)) that
simply aren't relevant to individuals. It also enables me to set up a
separate table (tblOrgContacts) to identify individual contacts for
the
organisation entities (a 1:Many relationship is established between
the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require
any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select "Indiv"
or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored in
the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from the
combo-box and be provided with a empty copy of the sub-form frmOrg.
If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same ContactID
in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I hope
it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access
2000. (I
have seen a suggestion that this might be achieved by using ADO, but
no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Graham Mandeno]

Hi Bob

Your changes to the design sound fine.
The only thing I would add is a validation rule for ContactTypeID:
=1 in tblIndividuals
=2 in tblOrganisations
to ensure that one entity cannot accidentally "morph" into the other.

As to your strange error, are you certain that you are not trying to change
tblContacts.ContactTypeID for an *existing* record (the one you have
previously entered)?

Do you get the error if you are on a new, empty record?

How have you set up the Link Master/Child Fields properties of your subform
control? If you change the SourceObject of a subform control then you must
also respecify the link fields, even though the field names have not
changed.

Also, you should be checking in Form_Current that the appropriate subform is
loaded and changing it if necessary.

If you're still having trouble, post the code behind your form and I'll try
to reproduce the problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

This doesn't seem to be working properly for me.

Here's what I've done so far:

In tblContacts:
- left the pk as it was (ie tblContactsID remains an autonumber pk field)
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblIndividuals:
- deleted the original autonumber pk field (IndividualsID)
- converted the existing ContactID (number) field as the new pk
- inserted a new ContactTypeID (number) field
- set the "default value" property to 1 (corresponding to the
ContactTypeID for the "Indiv" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblOrganisations
- deleted the original autonumber pk field (OrganisationsID)
- converted the existing ContactID (number) field as the new pk
- set the "default value" property to 2 (corresponding to the
ContactTypeID for the "Orgs" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

I then created a 1:1 relationship based on the combined ContactID and
ContactTypeID fields between tblContacts and tblIndividuals.
I did this by selected the two fields in tblContacts and dragging them
over to tblIndividuals. I created a 1:1 relationship between tblContacts
and tblOrganisations in the same way.

I then deleted all existing test data from tblContacts, tblIndividuals and
tblOrganisations - starting with a clean slate.

I then opened my client data-entry form. The main form has all fields
from tblContacts. The subform is unbound, but I have
inserted vba into the AfterUpdate event section of the form to ensure that
the SourceObject of the subform control is changed to the required subform
depending on the
selected ContactType (selected from a combo box).

I can enter the first record of either tblIndividuals or tblOrganisations
without difficulty - once I open the required subform the value of
ContactID is
the same as the autonumber pk in tblContacts, and the value of the
ContactTypeID defaults to the relevant default value.

However, after having entered this first record, if I then close and
reopen the main form, I am prevented from creating any new reords in
either table by a
popup box which states "The record cannot be deleted or changed because
the table "Individuals" includes related records."

What could be causing this error? There are no "related" records in
existence!


TIA
Bob

Hi Bob

The solution that Jamie gave is simple and elegant. To translate it into
Access table design terms (which might be more familiar to you than
ANSI-92 DDL statements!):

Add a unique index to tblContacts involving ContactID AND ContactType.
Next add a ContactType field to both tblIndividuals and tblOrganisations
and for each, set the default value to the corresponding contact type and
set the validation rule to =<contact type> and set required=Yes. (In
other words, an individual MUST be an individual and cannot be an
organisation, and vice-versa).

Now, add a 1:1 relationship with referential integrity between
ContactID/ContactType in tblContacts and tblIndividuals, and the same for
tblOrganisations.

Now the engine will look after the integrity for you. If a record in
tblContacts has a matching record in one of the other tables, then the
contact record can neither be deleted, nor changed to the other contact
type, unless the related subclass record is first deleted.

To answer your other questions:

1. A table-level validation rule can be created in the Table Properties
window (View>Properties in design view). For example:
([IndivID] Is Not Null) Xor ([OrgID] Is Not Null)
However, I would NOT use this two-field approach for your particular
problem.

2. You can force a prompt before saving a record using the form's
BeforeUpdate event.
Select case MsgBox("Save changes?", vbYesNoCancel)
case vbYes
' do nothing
case vbNo
Cancel = True
Me.Undo
case vbCancel
cancel = true
End Select

3. You can set ANSI-92 mode via Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Graham Mandeno wrote:

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot
both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate
(AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table tblIndividuals
segregates the individual-specific biographical information together
with
the individual's work details. The EmployerID links back to the
ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can
record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc)) that
simply aren't relevant to individuals. It also enables me to set up
a
separate table (tblOrgContacts) to identify individual contacts for
the
organisation entities (a 1:Many relationship is established between
the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require
any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for my
employer's business; tblContacts is then linked with tblClients which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest you
post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv" or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored
in the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form frmOrg.
If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I
hope it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access
2000. (I
have seen a suggestion that this might be achieved by using ADO,
but no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Bob]

Hi Graham,

Thanks for sticking with me on this.

My setup is this:

In the unbound subform control I have already the following settings:
Link Child Fields - ContactID;ContactTypeID
Link Master Fields - ContactID;ContactTypeID

I do not create or amend this links programmatically in any way. The only
thing I change via VBA is the SourceObject.

As for the VBA Code itself, this is what I have:

Private Sub cboContactType_AfterUpdate()
If Me.cboContactType.Value = 1 Then ' Individual
Me.Contactsubfrm.SourceObject = "NewIndiv" End If

If Me.cboContactType.Value = 2 Then ' Organisation
Me.Contactsubfrm.SourceObject = "NewOrgs"
End If
End Sub

I've placed this code in the "After Update" event for a combobox in my main
form. The control itself is bound to the ContactTypeID field in my
tblContacts, but the RowSource is bound to my tblContactType.

"NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my
modified subform for Organisations. "Contactsubfrm" is the name I have
given the subform control on my main form.

I've tried adding the following lines in each of the If ... Then statemens
in my code:
Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID"
Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID"

But this just causes the error message to popup sooner - as soon as the
subform opens rather than when I try to select a control or move to a new
record. This time I get "Run Time 3200" as part of the error message as
well. Curiously, the record selectors on the subform are not grayed out in
this scenario - but still result in an error message if I press any button
on the record selector itself.

As for the validation rules - I forgot to mention that I had already put
these in place (as per the directions in your previous post).

As for the problem itself, it occurs on both subforms:
- when the NewIndiv opens up, the first record it shows is the existing
record that I just entered. The message I quoted earlier pops up regardless
of whether I'm trying to select a textbox on the subform or simply using the
record selector (on either the main form or the subform itself) to create a
new record. (By the way, the record selector on the subform remains grayed
out)
- the message also pops up when I open the NewOrgs subform (which shows a
blank record except for the ContactID and ContactTypeID fields which are
already filled in when the form opens).

I'm not sure what you mean when you say I should be checking in Form_Current
to make sure the appropriate subform is loaded. I can definitely see the
subforms changing depending on the value in my combobox (for ContactType)
if that's what you mean.


TIA
Bob

Hi Bob

Your changes to the design sound fine.
The only thing I would add is a validation rule for ContactTypeID:
=1 in tblIndividuals
=2 in tblOrganisations
to ensure that one entity cannot accidentally "morph" into the other.

As to your strange error, are you certain that you are not trying to
change tblContacts.ContactTypeID for an *existing* record (the one you
have previously entered)?

Do you get the error if you are on a new, empty record?

How have you set up the Link Master/Child Fields properties of your
subform control? If you change the SourceObject of a subform control then
you must also respecify the link fields, even though the field names have
not changed.

Also, you should be checking in Form_Current that the appropriate subform
is loaded and changing it if necessary.

If you're still having trouble, post the code behind your form and I'll
try to reproduce the problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

This doesn't seem to be working properly for me.

Here's what I've done so far:

In tblContacts:
- left the pk as it was (ie tblContactsID remains an autonumber pk field)
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblIndividuals:
- deleted the original autonumber pk field (IndividualsID)
- converted the existing ContactID (number) field as the new pk
- inserted a new ContactTypeID (number) field
- set the "default value" property to 1 (corresponding to the
ContactTypeID for the "Indiv" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblOrganisations
- deleted the original autonumber pk field (OrganisationsID)
- converted the existing ContactID (number) field as the new pk
- set the "default value" property to 2 (corresponding to the
ContactTypeID for the "Orgs" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

I then created a 1:1 relationship based on the combined ContactID and
ContactTypeID fields between tblContacts and tblIndividuals.
I did this by selected the two fields in tblContacts and dragging them
over to tblIndividuals. I created a 1:1 relationship between tblContacts
and tblOrganisations in the same way.

I then deleted all existing test data from tblContacts, tblIndividuals
and tblOrganisations - starting with a clean slate.

I then opened my client data-entry form. The main form has all fields
from tblContacts. The subform is unbound, but I have
inserted vba into the AfterUpdate event section of the form to ensure
that the SourceObject of the subform control is changed to the required
subform depending on the
selected ContactType (selected from a combo box).

I can enter the first record of either tblIndividuals or tblOrganisations
without difficulty - once I open the required subform the value of
ContactID is
the same as the autonumber pk in tblContacts, and the value of the
ContactTypeID defaults to the relevant default value.

However, after having entered this first record, if I then close and
reopen the main form, I am prevented from creating any new reords in
either table by a
popup box which states "The record cannot be deleted or changed because
the table "Individuals" includes related records."

What could be causing this error? There are no "related" records in
existence!


TIA
Bob

Hi Bob

The solution that Jamie gave is simple and elegant. To translate it
into Access table design terms (which might be more familiar to you than
ANSI-92 DDL statements!):

Add a unique index to tblContacts involving ContactID AND ContactType.
Next add a ContactType field to both tblIndividuals and tblOrganisations
and for each, set the default value to the corresponding contact type
and set the validation rule to =<contact type> and set required=Yes. (In
other words, an individual MUST be an individual and cannot be an
organisation, and vice-versa).

Now, add a 1:1 relationship with referential integrity between
ContactID/ContactType in tblContacts and tblIndividuals, and the same
for tblOrganisations.

Now the engine will look after the integrity for you. If a record in
tblContacts has a matching record in one of the other tables, then the
contact record can neither be deleted, nor changed to the other contact
type, unless the related subclass record is first deleted.

To answer your other questions:

1. A table-level validation rule can be created in the Table Properties
window (View>Properties in design view). For example:
([IndivID] Is Not Null) Xor ([OrgID] Is Not Null)
However, I would NOT use this two-field approach for your particular
problem.

2. You can force a prompt before saving a record using the form's
BeforeUpdate event.
Select case MsgBox("Save changes?", vbYesNoCancel)
case vbYes
' do nothing
case vbNo
Cancel = True
Me.Undo
case vbCancel
cancel = true
End Select

3. You can set ANSI-92 mode via Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Graham Mandeno wrote:

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot
both be
Null.

Using the structure you have, you can go most of the way there using a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate
(AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table
tblIndividuals
segregates the individual-specific biographical information together
with
the individual's work details. The EmployerID links back to the
ContactID
field in tblContacts because we often end up acting for employees of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can
record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc))
that
simply aren't relevant to individuals. It also enables me to set up
a
separate table (tblOrgContacts) to identify individual contacts for
the
organisation entities (a 1:Many relationship is established between
the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not require
any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for my
employer's business; tblContacts is then linked with tblClients
which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest
you post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of
a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details. At
the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv" or
"Org" as the ContactType. Depending on the value in the combo-box,
one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the ContactType
and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored
in the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still in
the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form
frmOrg. If
the
user proceeds to enter data on the sub-form, the ContactID foreign
key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I
hope it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But - so
far
as
I
am aware - I can't impose check constraints on fields in Access
2000. (I
have seen a suggestion that this might be achieved by using ADO,
but no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob

 

[Graham Mandeno]

Hi Bob

Is your database very large? Would you mind zipping it and emailing it to
me? That would be quicker than me trying to set up something from scratch
to try to reproduce the problem.

Send it to ng1.g.mandeno at xoxy.net.
--
Thanks!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for sticking with me on this.

My setup is this:

In the unbound subform control I have already the following settings:
Link Child Fields - ContactID;ContactTypeID
Link Master Fields - ContactID;ContactTypeID

I do not create or amend this links programmatically in any way. The only
thing I change via VBA is the SourceObject.

As for the VBA Code itself, this is what I have:

Private Sub cboContactType_AfterUpdate()
If Me.cboContactType.Value = 1 Then ' Individual
Me.Contactsubfrm.SourceObject = "NewIndiv" End If

If Me.cboContactType.Value = 2 Then ' Organisation
Me.Contactsubfrm.SourceObject = "NewOrgs"
End If
End Sub

I've placed this code in the "After Update" event for a combobox in my
main form. The control itself is bound to the ContactTypeID field in my
tblContacts, but the RowSource is bound to my tblContactType.

"NewIndiv" is my modified subform for Individuals, and "NewOrgs" is my
modified subform for Organisations. "Contactsubfrm" is the name I have
given the subform control on my main form.

I've tried adding the following lines in each of the If ... Then statemens
in my code:
Me.Contactsubfrm.LinkChildFields = "ContactID;ContactTypeID"
Me.Contactsubfrm.LinkMasterFields = "ContactID;ContactTypeID"

But this just causes the error message to popup sooner - as soon as the
subform opens rather than when I try to select a control or move to a new
record. This time I get "Run Time 3200" as part of the error message as
well. Curiously, the record selectors on the subform are not grayed out
in this scenario - but still result in an error message if I press any
button on the record selector itself.

As for the validation rules - I forgot to mention that I had already put
these in place (as per the directions in your previous post).

As for the problem itself, it occurs on both subforms:
- when the NewIndiv opens up, the first record it shows is the existing
record that I just entered. The message I quoted earlier pops up
regardless of whether I'm trying to select a textbox on the subform or
simply using the record selector (on either the main form or the subform
itself) to create a new record. (By the way, the record selector on the
subform remains grayed out)
- the message also pops up when I open the NewOrgs subform (which shows a
blank record except for the ContactID and ContactTypeID fields which are
already filled in when the form opens).

I'm not sure what you mean when you say I should be checking in
Form_Current to make sure the appropriate subform is loaded. I can
definitely see the subforms changing depending on the value in my combobox
(for ContactType) if that's what you mean.


TIA
Bob

Hi Bob

Your changes to the design sound fine.
The only thing I would add is a validation rule for ContactTypeID:
=1 in tblIndividuals
=2 in tblOrganisations
to ensure that one entity cannot accidentally "morph" into the other.

As to your strange error, are you certain that you are not trying to
change tblContacts.ContactTypeID for an *existing* record (the one you
have previously entered)?

Do you get the error if you are on a new, empty record?

How have you set up the Link Master/Child Fields properties of your
subform control? If you change the SourceObject of a subform control
then you must also respecify the link fields, even though the field names
have not changed.

Also, you should be checking in Form_Current that the appropriate subform
is loaded and changing it if necessary.

If you're still having trouble, post the code behind your form and I'll
try to reproduce the problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

This doesn't seem to be working properly for me.

Here's what I've done so far:

In tblContacts:
- left the pk as it was (ie tblContactsID remains an autonumber pk
field)
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblIndividuals:
- deleted the original autonumber pk field (IndividualsID)
- converted the existing ContactID (number) field as the new pk
- inserted a new ContactTypeID (number) field
- set the "default value" property to 1 (corresponding to the
ContactTypeID for the "Indiv" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

In tblOrganisations
- deleted the original autonumber pk field (OrganisationsID)
- converted the existing ContactID (number) field as the new pk
- set the "default value" property to 2 (corresponding to the
ContactTypeID for the "Orgs" ContactType in tblContactTypes)
- set the "required" property to Yes
- created a new unique multifield index based on ContactID and
ContactTypeID as per the instructions in the Access help file

I then created a 1:1 relationship based on the combined ContactID and
ContactTypeID fields between tblContacts and tblIndividuals.
I did this by selected the two fields in tblContacts and dragging them
over to tblIndividuals. I created a 1:1 relationship between
tblContacts
and tblOrganisations in the same way.

I then deleted all existing test data from tblContacts, tblIndividuals
and tblOrganisations - starting with a clean slate.

I then opened my client data-entry form. The main form has all fields
from tblContacts. The subform is unbound, but I have
inserted vba into the AfterUpdate event section of the form to ensure
that the SourceObject of the subform control is changed to the required
subform depending on the
selected ContactType (selected from a combo box).

I can enter the first record of either tblIndividuals or
tblOrganisations without difficulty - once I open the required subform
the value of ContactID is
the same as the autonumber pk in tblContacts, and the value of the
ContactTypeID defaults to the relevant default value.

However, after having entered this first record, if I then close and
reopen the main form, I am prevented from creating any new reords in
either table by a
popup box which states "The record cannot be deleted or changed because
the table "Individuals" includes related records."

What could be causing this error? There are no "related" records in
existence!


TIA
Bob




Hi Bob

The solution that Jamie gave is simple and elegant. To translate it
into Access table design terms (which might be more familiar to you
than ANSI-92 DDL statements!):

Add a unique index to tblContacts involving ContactID AND ContactType.
Next add a ContactType field to both tblIndividuals and
tblOrganisations and for each, set the default value to the
corresponding contact type and set the validation rule to =<contact
type> and set required=Yes. (In other words, an individual MUST be an
individual and cannot be an organisation, and vice-versa).

Now, add a 1:1 relationship with referential integrity between
ContactID/ContactType in tblContacts and tblIndividuals, and the same
for tblOrganisations.

Now the engine will look after the integrity for you. If a record in
tblContacts has a matching record in one of the other tables, then the
contact record can neither be deleted, nor changed to the other contact
type, unless the related subclass record is first deleted.

To answer your other questions:

1. A table-level validation rule can be created in the Table Properties
window (View>Properties in design view). For example:
([IndivID] Is Not Null) Xor ([OrgID] Is Not Null)
However, I would NOT use this two-field approach for your particular
problem.

2. You can force a prompt before saving a record using the form's
BeforeUpdate event.
Select case MsgBox("Save changes?", vbYesNoCancel)
case vbYes
' do nothing
case vbNo
Cancel = True
Me.Undo
case vbCancel
cancel = true
End Select

3. You can set ANSI-92 mode via Tools>Options>Tables/Queries. Use with
caution!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the tips. I'll re-examine the use of the autonumbers in
the
two sub-tables.

As to the use of table-level constraints, how do I set this up? I
mean, I know I can specify that a particular field can't be null. But
how do I force a check on the null value of another field (field B)
before allowing one field (field A) to be null? Along the same lines,
how can I make sure that at least one field must be have a value?

I assume that this would ordinarily be achievable at the form level -
but you mention table-level constraints. I'm all ears

Going off topic a bit, where the form is concerned I've noticed that
my
database saves data automatically even if I close the form within
pressing save on the toolbar. I assume there is some kind of
auto-save
when you enter data into a form - but this does not always happen.
Sometimes it saves, sometimes it doesn't. How do I force a prompt to
save every time the form closes? (I located some example code - which
I don't have handy - but it does not seem to work).


TIA
Bob

Graham Mandeno wrote:

Hi Bob

PMFJI

In a Jet (Access) database, there is no way to enforce this sort of
entity
subclassing at the engine level. The only way to do that would be to
have
two FK fields in tblContacts - one for IndivID and one for OrgID, and
have a
table-level constraint (validation rule) to specify that they cannot
both be
Null.

Using the structure you have, you can go most of the way there using
a
BeforeUpdate event procedure on your ContactType control.

Something like this (pseudo-code):

If ContactType.OldValue isn't null then
lookup corresponding record in table corresponding to OldValue
If record exists then
Heavy warning message about changing contact type
If user wishes to continue then
delete old related record
else
cancel = True
End If
End If
End If

BTW, I think you are complicating matters by having separate
(AutoNumber?)
PKs in your Individuals and Organisations tables. I suggest you make
ContactID the PK in both those tables.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your interest Tina,

My full table structure is as follows:

tblContacts:
ContactID (pk)
ContactType (fk)
Address1
Address2
City
State
PostCode
PostalAddress1
PostalAddress2
PostalCity
PostalState
PostalPostCode
Tel
Fax
Mob
Email

tblIndividuals:
IndivID (pk)
ContactID (fk)
Title
FirstName
MiddleNames
LastName
Suffix
EmployerID (fk) (links back to tblContacts.ContactID (1:Many))
EmpDirectPhn
EmpDirectFax
EmpEmail

tblOrganisations:
OrgID (pk)
ContactID (fk)
OrgName
TradingName
IsACompany (yes/no)
ACN (Australian Company Number)
ABN (Australian Business Number)
Website

tblContactType
ContactTypeID (pk)
ContactType ("Indiv" or "Org")

As you can see, tblContacts lists the location (ie residential or
business) addresses, postal addresses and (residential or business)
telecommunication details for all contacts. The table
tblIndividuals
segregates the individual-specific biographical information
together with
the individual's work details. The EmployerID links back to the
ContactID
field in tblContacts because we often end up acting for employees
of
existing corporate clients or for muliple employees of non-client
organisations. I segregate the Organisation details so that I can
record
details for all businesses (incorporated and unincorporated (ie
sole-proprietorships, partnerships, associations, churches etc))
that
simply aren't relevant to individuals. It also enables me to set
up a
separate table (tblOrgContacts) to identify individual contacts for
the
organisation entities (a 1:Many relationship is established between
the
two tables based on tblOrganisations.OrgID (pk) and
tblOrgContacts.ContactID (fk)). For our purposes, we do not
require any
contacts to be linked with Individuals as opposed to Organisations.

The above tables essentially constitute the whole set of "contacts"
for my
employer's business; tblContacts is then linked with tblClients
which
identifies those contacts that are in fact clients:

tlbClients:
ClientID (pk) (autonumber)
ContactID (fk) (related to tblContacts.ContactID) (1:1
relationship)
ReferrerID (fk) (related to tblContacts.ContactID) (1:Many
relationship)


Regards
Bob


my first thought is: do you really need to to separate the
individuals
records and organizations records into different tables? suggest
you post
all the fields in each of those two tables so we can review them;
perhaps
we
can help you combine the two tables into one, with the addition of
a
single
field specifying either "individual" or "organization".

hth


Hi folks,

I am creating a client database in MS Access with the following
(simplified)
table structure:

tblContacts:
ContactID (pk - autonumber)
ContactType (fk) (from tblContactTypes)
ContactDetails (text)

tblIndividuals
IndivID (pk - autonumber)
ContactID (fk) (from tblContacts)
IndivDetails (txt)

tblOrganisations
OrgID (pk - autonumber)
ContactID (fk) (from tblContacts)
OrgDetails (txt)

tblContactTypes (serves as a lookup table)
tblContactTypeID (pk - autonumber)
tblContactType (txt - contains values "Indiv" or "Org")

There is a 1:1 relationship between the ContactID (pk) in
tblContacts
and
the ContactID (fks) in tblIndividuals and tbleOrganisations.

I have created a form in MS Access for entering client details.
At the
moment, I have two subforms - frmIndiv and frmOrg - which are
positioned
on
my main entry form.

The form contains a combo-box from which the user can select
"Indiv" or
"Org" as the ContactType. Depending on the value in the
combo-box, one
or
other of the two subforms will become visible.

At the moment, the user selects - say - "Indiv" as the
ContactType and
proceeds to enter details for this type of Contact. When this
happens,
the
ContactID for the current record in tblContacts table is mirrored
in the
ContactID foreign key in the tblIndividuals table. This is what I
want.

The problem is that once the user is finished (and whilst still
in the
same
record in the tblContacts table), the user can select "Org" from
the
combo-box and be provided with a empty copy of the sub-form
frmOrg. If
the
user proceeds to enter data on the sub-form, the ContactID
foreign key
in
the frmOrg will also mirror the ContactID in tblContacts.

This results in a record in both of my subtype tables
(tblIndividuals
and
tblOrganisations) having a record which points to the same
ContactID in
the
supertype table (tblContacts).

How can I prevent this from happening? - ie make sure that each
record
in
the subtype tables points to a record in the supertype table for
which
no
subtype record has already been created? (That's a mouthful - I
hope it
makes sense). I've seen some references to "check constraints" on
the
internet which I believe might help achieve my objective. But -
so far
as
I
am aware - I can't impose check constraints on fields in Access
2000. (I
have seen a suggestion that this might be achieved by using ADO,
but no
code
example was given).

Any pointers would be appreciated.

Please note, I am a complete novice at this.


TIA
Bob