I would add a field to the main table entitled "PolicyTypeID" with
values of 1, 2, 3 for the various types of policies.

Then I would make a form for the main policy table. Include all the
standard field plus a combo box for the policy type. Then I would make
a separate subform for each policy type, including just its particular
fields. Then add the subforms to the main form, and use the
Master/Child linking fields as appropriate. This feature causes Access
to populate the sub-table with the correct PolicyID value from the
main table.

Finally, I would add code that uses the value of the PolicyTypeID to
either make two of the three subforms invisible, OR use a single
subform object and change its source object as appropriate.


On Fri, 25 Feb 2005 07:32:25 -0800, "george"
wrote:

Jeff hi,

thanks for your reply. You see, if I did what you tell me,
in other words if I simply joined each one of the three
tables to my main table with 1:1 relationships then at the
moment I would try to add a record let's say in the Motor
table I would get a message from Access to also add a
record to the Life table as well as to the Household table
and this is not what I want.

I would like to be able to add a record to the Motor table
but not in the other two tables. This can only be done (if
I understood correctly) only throu SQL update triggers in
order to bypass the referential integrity rules of the db.

I will give you an example. Suppose I have two policies
with the following attributes
MOTOR POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. VehicleRegistrationN
6. EngineSize

HOUSEHOLD POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. Address
6. ConstructionYear

To my Policies table I would store attributes 1-4 for both
policies because they are exactly the same and then I
would store attributes 5 and 6 in table Motor for the
Motor Policy and in table Household for the Household
Policy respectively. Going one step further, for the Motor
Policy I don't want to store anything either in Life table
or in Household table but the db won't let me do this with
1:1 relationships. I need to find a constraint so that the
db will let me do this by inactivating the referential
integrity rules. I hope it's more clear now what I mean.

thanks again, george


-----Original Message-----
George

A 1:1 relationship doesn't require any workarounds, and
is not a 1:M
relationship.

If you haven't done so already, open the relationships
window and add your
four tables.

Drag the primary key from your "supertype" table to the
primary key of your
first sub-type table... and this is a good point to
remind you that you can
use an Autonumber for the supertype table's key, but must
then use a LongInt
data type to store the corresponding key in the sub-type
tables.

I'd recommend setting referential integrity on the joins
between your
supertype and sub-type tables. At least initially, I
don't perceive a
reason for you to check the "Cascading Update/Delete"
boxes.

Or have I totally misunderstood what you were asking
about ...?

--
Good luck

Jeff Boyce
Access MVP

"george" wrote in
message
...
Hi again,

I have a main table, Policies, which contains general
information pertaining to all policies regardless of
their
type (i.e. LifePolicies, MotorPolicies,
HouseholdPolicies)
and also three other tables, Life, Motor, Household
which
contain *additional* specific information about the
policies belonging to a particular type.

The database schema can be found at:


http://www.databaseanswers.org/data_...surance_broker
s/index.htm

Because I could not figure out how these four tables are
related among them in the above mentioned schema I
posted
a question (see: "unknown symbol in ER
diagram", "george",
Feb 21/2005, 2:20 AM) and it was kindly explained to me
that this is a supertype - subtype situation involving a
1:1 relationship between each one of the three subtables
and the main table and also the use of some triggers.

Well, all this I think is above my ability to apply as I
am not experienced in SQL programming but I nonetheless
I
really like the idea of having three 1:1 relationships
that are mutually exclusive because of the triggers
since
conceptually this is exactly what I need.

Are there any ideas for any workarounds? Myself I was
thinking (as a last resort) to create three 1:M
relationships and then somehow put some code on the
forms
of the three subtables which would not allow the user to
add more than one record, but, I don't know, I'm kind of
desparate right now.

ANY IDEAS OR HINTS that you may have on the subject will
be greatly appreciated,

thanks in advance, george

.



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security