Primary Key and Linking Table Data

[dunlop407703]

Hi all,

I'm just starting out with Access and I think I'm at about the point were I know enough to be dangerous but not enough to really achieve anything.

I thought I understood how this works but apparently not quite.

I have 3 tables, tCollar, tSite, tSurvey, all 3 tables have different primary keys, but also share some fields as I will try to define below.

Table PK Shared Fields
tCollar BOREID BOREID,SITEID,TENEMENT
tSite SITEID BOREID,SITEID,TENEMENT
tSurvey ID BOREID,SITEID,TENEMENT

The tables are linked as below:

Table1 Table2 PK FK
tCollar tSite BOREID BOREID
tCollar tSurvey BOREID BOREID
tSite tSurvey SITEID SITEID

I may need some help on my database design here but basically I want that if I add a new item into the BOREID field in tCollar, it automatically adds a new row with the same data as I add in the tCollar table in both the tSite and tSurvey tables. I though that if I have referential integrity enforced and cascade update it would do this but apparently not. Beyond this if I add a SITEID in the tCollar table I want it to update to the other tables as well.

I've looked around heaps and I'm starting to think that to do this I'll need to use a form to update my data rather than just direct entry into the tables and then run queries or similar to force updates on other tables? Any help will be greatly appreciated.

 

[Micron]

Without benefit of some meaningful data or explanation of how these are related, all I can offer is that it looks all wrong. Do you really have BOREID and SITEID in a table more than once? I thought this was not possible. Some would also have BOREID in the "parent" table, but call the related field in some other table BoreIDFK or BoreID_FK to denote that it is a foreign key whose primary field is in some other table.

If you want an easy way to constrict html tables and keep your header/values in line, stick it in Excel, copy the range and dump here. You will get a table that ought to be good enough to use as is without resizing.

 

[dunlop407703]

Without benefit of some meaningful data or explanation of how these are related, all I can offer is that it looks all wrong. Do you really have BOREID and SITEID in a table more than once? I thought this was not possible. Some would also have BOREID in the "parent" table, but call the related field in some other table BoreIDFK or BoreID_FK to denote that it is a foreign key whose primary field is in some other table.

If you want an easy way to constrict html tables and keep your header/values in line, stick it in Excel, copy the range and dump here. You will get a table that ought to be good enough to use as is without resizing.

Apologies, my original post didn't format very helpfully.

BOREID, SITEID and TENEMENT are not in any one table more than once, what I was trying to show is that they exist as fields in each of the 3 individual tables.

What I wan't is a way to enter a piece of data in the BOREID column in the tCollar table, and have that also create a record in the tSite table with that same BOREID.

 

[Micron]

I get that the repeats aren't really. What I still don't get is which fields are in the table and which are not. It's too hard to read or figure out what you mean by "shared fields".

Without benefit of some meaningful data or explanation of how these are related,

This took about 60 seconds to throw together:

tCollar
BoreID	fld1	fld2
1	apple	sliced
2	orange	diced
3	pear	cubed

<tbody>
</tbody>

In an attempt to answer anyway, you'll likely need an append query to get the data into the table and an update query if you would ever attempt to alter one of the fields in the record.

 

[xenou]

You can use Access forms/subforms to achieve this - if you have a subform with a PK-FK relationship, it will automatically hook up the related keys. In one way this is very simple, in another rather confusing so you just have to play with it and see how it works for you. Obviously you can do the same thing "by hand" entering data as needed, or even using vba code to generate entries that are inserted into various table. (Edit: Probably there are dozens of YouTube videos to demonstrate this - I haven't searched but videos on this material are posted more and more as time goes on).

It is okay to have foreign key fields that have the same name as primary key fields in related tables.