Cascading relationships

[colin]

Hi

I have thre tables A,B and C and they all have unique keys

Table 1 field 1 (Unique) field 2
Table 2 field 1 (Unique) field 2
Table 3 field 1 (Unique) field 2

I would like to set up a relationship enforcing referential integrety so
that if table 1 is opened then there is a plus sign to cascade into table 2
and then that has a cascade into Table 3

However when I set the relationship as follows:

Table1Table2
Table1 Table2
ID 1 1 ID
Attributes: Unique, Enforced
RelationshipType: One-To-One
Table2Table3
Table2 Table3
ID 1 1 ID
Attributes: Unique, Enforced
RelationshipType: One-To-One

Table1 cascades to Table 2

But table 2 has no cascade and table 3 cascades into table 2

Any suggestions to resolve this - Iam using access 2007

Kind regards

Colin

 

[Arvin Meyer [MVP]]

I suggest that you only use tables for storing data since there's no way to
protect an Access table from mistake or mischief. Forms (and reports) are
for displaying data. Performance is also drastically affected when you use
cascading tables, although it won't be greatly noticed with smaller amounts
of data. Access is the only database system that allows users easy access to
viewing data in tables. Since it isn't a difficult thing to do, ask yourself
why. That said, open your tables in Design View, right-click on the Title
Bar and choose properties. You should be able to set your cascading tables
from there.

 

[Allen Browne]

You are trying to set up a relationship such that whenever a new record gets
added to Table1, it automatically adds a matching record in Table2 as well?

Access can't do that. At least current versions can't. The next version (due
out next year) will be able to, according to the Access Team blog:
http://blogs.msdn.com/access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx

But the question arises as to why you need these one-to-one relations
anyway. Is it because you can't fit enough fields into one table? If so,
there's a very good chance that you don't have a relational design. If you
want more help about this, tell us about the fields. Are they repeating
fields such as Week1, Week2, etc?

 

[colin]

Hi,

Many thanks for the responses. there is no actual usage for the data tables
apart from the creation of an exported xml file.

The structure will hopefully enable me to export to xml in the following style
where
make name would be table 1
model name would be table 2
color name would be table 3

for the example below the relationship between table 2 and 3 would be one
two many.

<?xml version="1.0" encoding="utf-8" ?>
<make name="Acura">
<model name="Integra">
<color name="Green" />
<color name="Sea Green" />
<color name="Pale Green" />
</model>
<model name="RL">
<color name="Red" />
<color name="Bright Red" />
</model>
<model name="TL">
<color name="Teal" />
<color name="Dark Teal" />
</model>
</make>
<make name="Audi" value="Audi (value)">
<model name="A4" value="A4 (value)">
<color name="Azure" value="Azure (value)" />
<color name="Light Azure" value="Light Azure (value)" />
<color name="Dark Azure" value="Dark Azure (value)" />
</model>
<model name="S4" value="S4 (value)">
<color name="Silver" value="Silver (value)" />
<color name="Metallic" value="Metallic (value)" />
</model>
<model name="A6" value="A6 (value)">
<color name="Cyan" value="Cyan (value)" />
</model>
</make>
<make name="BMW" value="BMW (value)">
<model name="3 series" value="3 series (value)">
<color name="Blue" value="Blue (value)" />
<color name="Sky Blue" value="Sky Blue (value)" />
<color name="Racing Blue" value="Racing Blue (value)" />
</model>
<model name="5 series" value="5 series (value)">
<color name="Yellow" value="Yellow (value)" />
<color name="Banana" value="Banana (value)" />
</model>
<model name="7 series" value="7 series (value)">
<color name="Brown" value="Brown (value)" />
</model>
</make>
</CarsService>

I can only think of using a relational database to generate cascading xml
output. I appreciate that this is not an xml forum but any assistance would
be grateful.

Kind regards

Colin

 

[Allen Browne]

Not sure I follow. Cascading relationships in Access (where an update or
deletion in one table results in an update or deletion in another table) is
not anything like cascading XML.