Hi everyone,

I wonder if a related table or secondary table can be automatically updated once a new record is created in a "primary" table. I have a one to many relation where the "one" is on the primary table and "many" is on the secondary table.

For example I have a table of "crops" with 10 records (primary table). I have a table of "counties" with 30 records (related table). Each county has the 10 "crops" from the primary table. So my counties table has a total of 30X10 records.

Suppose I want to add a record to my "crops" table which would now have 11 records. I would now like each of my counties to have 11, not 10 crops. The counties table should have 30X11 records.

How can I update my "counties" table in such a way?
Can this be done automatically once I create a new "crop" (primary table)? I have been able to do this with an append query, but I have many such tables and I would like to avoid doing several append queries.

Victor

Is it that you want to have "many" crops and "many" countries? Like in a many to many relationship?

Thanks oumahexi,

No, I''m pretty sure it's a one-to-many relation. The primary or "crops" table I refer to above stores all possible crops. The referential integrity properties from the relationships options updates all related tables when I DELETE a crop. However, nothing happens in the related tables when I ADD a new crop in the primary or "crops" table.

I have had to update the related tables using append queries. I'm wondering if the related tables can be updated automatically without having to use append queries when I add a new category or record in the primary or "crops" table.

Victor

This sounds very suspiciously like a normalization problem. What you describe SHOULD exist as THREE tables. Otherwise, you run into the "what do I do when I add a new crop" OR "what do I do when I add a new county" problem. With a denormalized database, one of those two questions is drastically harder than the other one.

The normalized version of this would be

tblCounty, CtyID (prime key), county name, other data related ONLY to counties.

tblCrop, CropID (prime key), crop name, other data related ONLY to crops

tblCtyCr, CropID (foreign key to Crop table), CtyID (foreign key to County table), data specific to that crop in that county. This is a JUNCTION table.

To add a new crop, you add a record in the crop table, then add as many records in the junction table as the number of counties. To add a new county, add a record in the county table, then add as many records to the junction table as the number of crops. Since you have a list of counties and a separate list of crops, it would be fairly easy to write an append query for the junction table to add a record for every combination of crop and county where the COUNT of such records is currently zero.

The thing is, without knowing where you are going with this, it is impossible for us to know what ELSE you are going to need. I suspect you need even more than this.

You seriously need to understand normalization before doing too much more to your DB. It is a topic in the Access Help files. You can also visit Wikipedia.ORG and search for Database Normalization. You can also web-search for Database Normalization. If you are reading web articles, limit yourself to well-known colleges and universities in the .EDU domain. Otherwise, you'll get a gazillion hits.

Thank you for your help. I will follow your recommendations and see how things go. The junctions table seems to be the direction I'm going with this.

I believe I follow the guidelines you set in your comments above.

I do have:

tblCounty, CtyID (prime key), county name, other data related ONLY to counties.

tblCrop, CropID (prime key), crop name, other data related ONLY to crops.

Regarding the junction table you mention I believe I have several junction tables with different information. For example, I have tables titled:

tblYield
tblFixedCosts
tblVariableCosts

They all hold data specific to crop and county (including cropid and countyid).

I had been able to create an append query for tblYield. That is I added a new crop in tblCrop, and then I added as many records to tblYield as number of Counties.

I see I will have to do the same thing for

tblFixedCosts
tblVariableCosts.

I was wondering if tblYields and the others would automatically take the new dimensions when creating a new crop, but I think we have to go through Append Query process first as you mention.

Thanks again.