Table Relationships: Updating Records

[ebs57]

I am looking for some basic help in understanding and setting up table relationships in Access.

I've created one table called PROJECTS and it has the field JobNo which I've declared as the key field. It also contains such fields as Rep, Date, Descrip and so on.

I also have another table called DETAILS and it contains the following fields: ID (key field), JobNo, Rep, Date, Contact, PhNum, FileLoc and so on.

I have created a relationship between PROJECTS and DETAILS using the JobNo field from PROJECTS (the "1" side) to the JobNo field of DETAILS (the "infinity" or many side). Since JobNo from the PROJECTS table is a key field, I am able to turn on "Enforce Referential Integrity" and I select both "Cascade..." options.

My goal is to have the relationship link matching JobNo fields from both tables together, and to have fields with the DETAILS table automatically update if a matching field exists within the PROJECTS table. For example, after creating the relationship I expected to change Rep from the PROJECTS table and have it propagate over to the Rep field in the DETAILS table -- dint happen. Like fields from both tables are declared with the same formatting features, by the way (both Text or Number, same length, no conditions, etc.). Also, I made sure the key field's Indexed property from the parent table was set to YES (No Duplicates).

Despite the relationship between the two tables, matching fields within both tables behaves as if they are completely unique; I can update the Rep field in DETAILS and nothing happens in the Rep field of PROJECTS, and vice versa.

This description is much longer than I anticipated, but if anyone has some pointers on establishing relationships I am 100% ears.

Thanks in advance.

[ebs57]

I made an update query which finds like fields from the primary table and updates secondary tables. The user will invoke the query when the form is opened or closed so that current data is always synchronised.

No idea if that's "the correct way" but it works for me. Again, not pleased with my hacking; want to understand but me not smart. Back to mashing the keyboard...

[MMcCarthy]

Have a look at this tutorial.

Database Normalisation and Table structures

[ebs57]

While connecting some tables in ACC2003 I ran into an interesting problem, not sure if it's me or what. Here's the scenario:

I open the Relationships... window and graphically connect two tables, ProjName and ProjActivities. Within each table is the field "PName" which I join (type Indeterminant, category 1: include rows from both tables where both fields are equal). This field is not the key field in either table and is of the same type in both tables.

When I then double click on the parent table (ProjName) in Datasheet view I don't see the cute little plus signs next to each record like I would expect, and I begin weeping.

After an hour or so I re-open the Relationships table and edit my relationship to join the key field, ID, from the parent table to ID in the child table. This doesn't make sense to me but then again what does in this crazy world. I re-open the parent table and voila - there are my plus signs. No useful data is shown (when I expand a plus sign to reveal the underlying table records) but at least I am reunited with my plus signs.

I then re-re-open the Relationships table, edit the relationship to delete the ID join but leave the PName join intact and re-re-open the parent table: Plus signs are a go and now it shows the linked data between both tables (when I click on a plus sign).

Prior to doing the "key field shuffle" I tried the "Repair and Compact Database" utility with no luck. This d/b is small and I'm just starting on it so it's not like Access has to wrestle with large amounts of data. I'm guessing if I could take a gander at the SQL for the table join something would be different before and after I perform the key field waltz.

Love me some comments!