Relationship issues (1 Viewer)

[danbl]

Ok here I am again.

Please look at the relationships. When I create the primary and foreign keys I successfully get the data to align. Ideally the primary key in the advance directive table should match with one say the DVT table, but they do not. There will always be a record and therefore a Primary / foreign key relatiionship between the Advance directive table and the Perioperative table. But as subprojects evolve as with DVT and Temperature there will not always be the same relation. When I change the foreign key to numberfield then nothing shows.

Also when I try to build one form for all data entry, I get the message which attached with the screen shot called "relationships11911.doc". I did set the index to no duplicates in the advance directive table.

Can you help me with this???

 

[David Eagar]

I don't think you have set up your table relationships correctly. I have modified them to what I think is correct

 

[danbl]

David,

Thank you for looking at this!!

Let me toss another wrinkle in this and tell if it makes a difference. For the table "Advanced_Directive" and "Perioperative Data" there should always be a matching record. Using the primary key / foreign key relationship the data should align correctly and in my tests it does or did prior to your changes. For the other two tables this is not the case as these represent the tracking of new data. Example the "Advanced_Directive" and "Perioperative Data" were started in January 2010 but the later two were not started until March 2010. Will the autonumber values match up? Also there should be a match for each record in the later two tables which I think will be maintained by what you did.

Thanks,
Dan

 

[David Eagar]

The two autonumbers in each table will never always match, it will increment every time there is an entry in that table. By using a foreign key, the second table will always record the autonumber of the primary table - a 1 to many relationship

in the words of James Brown "Where is the one!"

 

[danbl]

David,

So if I want to link all the tables I could add the idLink to the DVT table and then make the one to many relatioship from the Advanced_Directive table to the DVT table?

My question was not about the autonumber fields in the two tables being equal but about the idLink fields. For example; if we assume there are 200 records in the Advance_Directive table for the first three months and now the DVT table begins with month four, will the first record in the DVT table (idLink field) show the same value (201)the 201st record in the Advance_Directive table?

Thanks
Dan

 

[David Eagar]

If the DVT table is to be your primary table (The ONE), then yes, other tables must be linked to it

So for patient 201, the foreign key (link number) will always be 201 in the link field in the other tables

 

[danbl]

This is all very frustrating ... I did as you suggested and then appended some data into the trial database I am using. The result is no numbers show up in the idlink field.

Any thoughts????

 

[David Eagar]

Lets go back to basics - if the link is not being populated, then the form where you are entering data is not set up correctly

Try this

 

[danbl]

Okay ... I am working in two databases. The first had no primary / foreign fields. (MY ignorance) I have a years worth of data in this database. In the second I am attempting to correct prior errors; getting primary and foreign fields established with the hope of appending all the data in each table to the new or second database. Another objective is to have one data entry form for all four tables. I have tried numerous time to accomplish this with no result.

Your form is close to what I want but each table would be a sub of the Advanced_Directive data form.

I am going to attempt to attach a previous posted attachment for you to look at. AS you will see there are four data entry forms and four date edit forms.

How can I accomplish this?? Can you explain why when I append the real data into the second data base the idlink field does not populate??

Appreciate all your help.
Dan

 

[David Eagar]

Can you explain why when I append the real data into the second data base the idlink field does not populate??

Appreciate all your help.
Dan

When you append data into a secondary table, Access will not populate the link field for you, you must add that to your data source

 

[danbl]

I get it ..... so if I make a copy of the original database, add the primary/foreign fields, and estableish the relationships it should bring all the data together?

Let me know what you think after you look at the tables, as to how to make the form I am looking for.