Table relationships

[matches]

Hello all,

I have tables related by key fields. Is there a way to enter data into one table and have the key fields of the related table automatically enter into the key field of the related table?

Also, can you make custom drop down menus in a field? For instance, I have field that I would like to have a text in, there are only three values that are needed but spelling is important so I would just like to pull a list down to enter the data.

Thanks for any help!

 

[GMLWORLDCHAMP!]

How's this...

See attached zip file for the basic drop down combo box development...

Your table relationships are set for standard referential integrity and if you select the Cascade updates and Deletes in the detailed relationships, you will insure that each table has values that will always match in each table going forward...as Pat points out in better detail, corresponding subforms within a main form will accomplish this for you by writing to both tables...

Hope this helps!

The Northwind.mdb has examples of all this...take a look...this is the database that is well documented throughout the length of the Access 1997-2000 Bibles, one of my favorite texts...

 

[matches]

Not sure if I am retarded or not, but when I enter data into my main table the related fields are still not updating on there own. I set it to cascade update/delete related fields but it is still not working. I going fuss with it of course, but maybe I am missing something?

 

[Pat Hartman]

1. Cascade Update is used ONLY to propagate changes to the primary key of the parent table to the related foreign key value in the child tables. It does NOT copy data.
2. It makes no sense for Jet to automatically create child records since the ONLY value it has is what would go into the foreign key. So, essentially it would be making empty child records.
3. You need to create the child records yourself. The best method is to use a bound subform. As long as the master/child links are properly set, Access will automatically populate the foreign key field as soon as you type something in to some other field of the subform.
4. As to your lookup values, the best method depends on which version of Access you are using. I know that the beta for Access 2007 will propagate value changes at the table level to the combo boxes on forms. Since you are probably not using an unreleased version, the best method is to make a table that holds the lookup values. Use that table as the RowSource for the combo. Set the LimitToList property of the combo to true to prevent the user from entering values not in your list table.

 

[dreamspy]

Hi

I'm in a similar case as Matches is describing. I have two tables linked by key field. I would like access to create the entry in the child table, when I make a new entry in the parent table. Pat says as long as the master/child links are properly set. Aren't you just talking about properly linked in the relationships window?

Inside the form I use to type in my data, I created a subform, which has the child table as record source. Unfortunately this did not create child records automaticaly.



though... it might be better to attack my problem a little bit earlier in the progress:

My main problem is:
I have a master table, and then I have a child table. It's a one-to-one relationship. Then I have a query which shows some information from the master table, but the output is filtered by the items in the child table, which is a bunch of yes/no (true/false) fields. Let's say f.x. the Query Criteria for the field "item arrived" in the child table is set to "no". Now if the that record doesn't exist in the child table, this returns false and doesnt show the record. But what I want is to show this record since the this field is not "true".

Maybe there is a way around this. Is there some statement I can put in the criteria which returns true both if the entry is "no", and if it doesn't exist?

Boy it's hard to describe these kinda things, especially since english isn't my mother language... hope you understood my problem



regards
Frímann

 

[dreamspy]

I actually found out the answer to my second question. Quite simple actually. I set the query criteria to "is null or no". That includes also records that don't have an entry in the child table.

But I'm still interested in how to to automatically create entries in linked child tables

 

[Pat Hartman]

What is the point of creating empty child records? As soon as you enter a value in a single child table field, Access creates the child record and assigns the parent's foreign key value.
Why do you have a 1-1 relationship? They are extremely rare in the real world.
To handle sparsely populated 1-1 or 1-m relationships, use a left join. That will allow records on the 1-side to be returned even if they have no children.

 

[dreamspy]

In this case if the child record doesn't exist, I get into troubles when I'm using filters in a form.

I have a form which is shoving some fields from a parent record, and some true/false fields from a related child record. Now when I do "filter by selection" on an empty (not ticked) box, and the child record for that item doesn't exist, access shows me only records which child records do not exist. The same happens if I do "filter by selection" on an empty box that has a related child record. So if every record in the parent table would have a related record in the child table, this wouldn't be a problem.

You ask why I have 1-1 relationships. My table is set up like this:

Table 1 - parent table:
Basic information about items. In this case movies on video cassets.

Table 2 - child table:
Holds fields about what has been "done" to the video cassets. Subtitles been made, trailers edited, encoded on server etc. And dates of when each thing was done.

I set this up in two tables with 1-1 relationship because I thought it was convenient to not have all the data in one table. I get kinda confused working with tables with to many fields. This serves no other purpose than just giving me better view of what I'm doing. Although it seems now that this is only creating problems for me



What is the point of creating empty child records? As soon as you enter a value in a single child table field, Access creates the child record and assigns the parent's foreign key value.
Why do you have a 1-1 relationship? They are extremely rare in the real world.
To handle sparsely populated 1-1 or 1-m relationships, use a left join. That will allow records on the 1-side to be returned even if they have no children.

 

[dreamspy]

In this case if the child record doesn't exist, I get into troubles when I'm using filters in a form.

I have a form which is shoving some fields from a parent record, and some true/false fields from a related child record. Now when I do "filter by selection" on an empty (not ticked) box, and the child record for that item doesn't exist, access shows me only records which child records do not exist. The same happens if I do "filter by selection" on an empty box that has a related child record. So if every record in the parent table would have a related record in the child table, this wouldn't be a problem.

Also regarding left-join, then I'm using that, but it doesn't fix my filter problem

You ask why I have 1-1 relationships. My table is set up like this:

Table 1 - parent table:
Basic information about items. In this case movies on video cassets.

Table 2 - child table:
Holds fields about what has been "done" to the video cassets. Subtitles been made, trailers edited, encoded on server etc. And dates of when each thing was done.

I set this up in two tables with 1-1 relationship because I thought it was convenient to not have all the data in one table. I get kinda confused working with tables with to many fields. This serves no other purpose than just giving me better view of what I'm doing. Although it seems now that this is only creating problems for me