Re: How to enforce subtypes/supertypes in Access 2000?

comments inline.

"Bob" <fakeemail@xxxxxxxxx> wrote in message
news:44d666e6$0$21306$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



"Bob" <fakeemail@xxxxxxxxx> wrote in message
news:44d65939$0$21282$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks Tina,

I'll give that go.


Regards
Bob


"tina" <nospam@xxxxxxxxxxx> wrote in message
news:5VqBg.221185$mF2.88072@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


"tina" <nospam@xxxxxxxxxxx> wrote in message
news:1oqBg.221099$mF2.203409@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


"Bob" <fakeemail@xxxxxxxxx> wrote in message
news:44d6238f$0$21313$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <nospam@xxxxxxxxxxx> wrote in message
news:bCnBg.220675$mF2.29695@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <fakeemail@xxxxxxxxx> wrote in message

news:44d5b441$0$21304$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
















.

Relevant Pages

  • Re: How to enforce subtypes/supertypes in Access 2000?
    ... There is no link between tblFiles and tblClients. ... the client - eg the director of a corporate client. ... Hence my tblContacts, tblIndividuals and tblOrganisations tables. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to enforce subtypes/supertypes in Access 2000?
    ... tblContacts and tblClients? ... ContactID ... I am creating a client database in MS Access with the following ... ContactID (pk - autonumber) ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to enforce subtypes/supertypes in Access 2000?
    ... created a new unique multifield index based on ContactID and ContactTypeID ... ContactTypeID fields between tblContacts and tblIndividuals. ... and tblOrganisations in the same way. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to enforce subtypes/supertypes in Access 2000?
    ... In the unbound subform control I have already the following settings: ... tblContacts, but the RowSource is bound to my tblContactType. ... blank record except for the ContactID and ContactTypeID fields which are ... In tblIndividuals: ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to enforce subtypes/supertypes in Access 2000?
    ... In the unbound subform control I have already the following settings: ... tblContacts, but the RowSource is bound to my tblContactType. ... blank record except for the ContactID and ContactTypeID fields which are ... In tblIndividuals: ...
    (microsoft.public.access.tablesdbdesign)
Loading