relationships 101

[markjkubicki]

this really is a 101,
(and also that area that 'with some minor embarrassment) I've never been able to get right)

relating 2 tables...
the primary (1`) table (tblManufacturerRepAgencies) is the "one" side
the secondary (2`) table(tblManufacturers) is the "many" side

for the related field (RepAgency)
...in the 2`table, whenever the field has a value, it will always also exist in the 1`table;
2` table it will not always have a value in that field; it may be null.

i need to enforce referential integrity so that when a record in 1`is changed, the change also occurs in 2`



i ran a query to find any records in 2` that had a field value that was not in 1` and have corrected all errors;
the only records in 2` that do not have a match in 1` are blank (in the related field)

yet when i set up the relationship

Include ALL records from 'tblManufacturerRepAgencies'
and only those records from 'tblManufacturers' where the joined fields are equal.

and then check enforce... cascade update
...I get the usual: "can't do"


utterly stumped
with appreciation in advance,
m.

[isladogs]

I have an extended article about relationships and referential integrity on my website which may help you get out of room 101
See http://www.mendipdatasystems.co.uk/r...ps1/4594533224

HTH

[ssanfu]

I'll take a stab at this. I can tell you how I design and relate tables.

This is for a 1-to many relationship. To have RI, the linking fields must be the same data type. This means that you cannot have one field as a text type and the other field as a long integer.
And the "1" table field must be unique.

In my tables I always (99.8%) have the PK field as an Autonumber type. This means the linking field in the "many" table must be a long integer. Also, because I use an Autonumber type in the "1" table, the linking field in the "many" table can never be 0 or NULL.

When I name fields in tables, the PK field, because it is a number field (autonumber), I use a suffix of "ID_PK". For the linking field in the "many" table, I use a suffix of "ID_FK". Some may say that the "ID_" is not necessary and they would be correct. But this is how I nave decided to name fields. I do not have any field with the same name in any other table and it is easier to know which fields I am working with, especially when setting the links in Main form/sub form designs or in code.

Speaking of Main form/sub form (and report) designs, because the sub form is linked to the main form, the sub form linking field value is automatically entered when a new record is created in the sub form. If you execute update queries or append queries in code, you are responsible to enter the correct value into the "many" linking field.


So, the 1 table is "tblManufacturerRepAgencies". I would set the PK field type to Autonumber and name it "ManRepAgencyID_PK".
The many table, "tblManufacturers", I would set the PK field type to Autonumber and name it "ManufactureID_PK".
The linking field (to table "tblManufacturerRepAgencies") would be type Long and I would name it "ManRepAgencyID_FK".

I would add the two tables to the Relationship window and drag "ManRepAgencyID_PK" to "ManRepAgencyID_FK". If there are no NULLs, no zeros and any value in "ManRepAgencyID_FK" is in "ManRepAgencyID_PK", a link will be created. The 1 side will have a "1" next to the PK field and the many side will have the infinity symbol next to the FK field.



You might also see Microsoft Access Tables: Primary Key Tips and Techniques



Or just read Colin's site.......

[orange]

I agree see Colin's site for excellent material re Relationships.
Here is a link I often reference for other information re Database Planning and Design.