multiple-key relationship, I think

[mcourtney]

Hello. I want to correlate information from several tables in my database. One table (tbl_USDA) contains information about seed that's available from the USDA; it includes the genus, species, and infraspecies of the seed ("Brassica oleracea italica", or broccoli). This table doesn't include the keys that would relate it to the rest of my database because I import the information from the USDA's website.

 

I'd like to be able to relate this information into the rest of my database, which I think I should be able to do because in it I have a table of crops (tbl_crop) which includes the species and infraspecies name, plus a relationship to a table (tbl_genus) that contains the names of the genera. What I'm hoping to do is have the database assign crop numbers to the records in the seed table, along these lines:

 

for a given record in tbl_USDA, if species and infraspecies equal the equivalent fields in tbl_crop and genus equals that field in tbl_genus, then generate the crop key of the equivalent crop in tbl_crop. That way I could sort the records in tbl_USDA by higher taxonomic levels like family, or include other information that I've researche myself, such as haploid number, in the layout I've made for displaying the USDA seed records. Any ideas? Hope my explanation of what I'm trying to do isn't too opaque.

 

Thanks!

[mcourtney]

Maybe this is a better way to explain it. Here are the two tables concerned. I tbl_USDA is the one I want to get to relate to tbl_crop. tbl_crop containss the fields species and infraspecies; it has a foreign key to tbl_genus which contains the field genus.

 

 

tbl_USDA::genus

tbl_USDA::species

tbl_USDA::infraspecies

tbl_USDA::kp_crop calculationgoeshere

 

tbl_genus::genus

tbl_crop::species

tbl_crop::infraspecies

tbl_crop::kp_crop

 

 

I want the calculation to generate the number in the kp_crop field in the record from tbl_crop that matches all three criteria (genus, species, infraspecies).

[Weetbicks]

First if you want to relate on all three values, tbl_genus::genus needs to be stored on the crop table. I'm not sure what that bit is, cos you mentioned only two tables , yet this looks like a third table. I'm assuming somehow tbl_crop is related to a tbl_genus record, but the genus is not stored on the crop record?

 

store a crops genus on the crop record.

 

Then you can create a relationship between tbl_usda and tbl_crop using the three fields genus/species/infraspecies

 

 

tbl_usda -> tbl_crop

----------------------------

tbl_USDA::genus = tbl_crop::genus

tbl_USDA::species = tbl_crop::species

tbl_USDA::infraspecies = tbl_crop::infraspecies

 

Then you can set tbl_USDA::kp_crop

 

However since you want this to be a primary key (kp) it must be stored and indexable. If you make it a calc to link thru to the crop table, it would be unstored. Thus make it an auto-enter calc instead

 

The calc would be:

 

tbl_crop::kp_crop

 

if you leave "Do not replace existing value (if any)" checked, the key gets set once and stays like that. If you untick it, later if you change genus/species/infraspecies fields on the usda table, it will update the primary key (which seems weird since primary keys shouldnt change)

 

It seems more like what you actaully want to do is set the primary key on usda ONCE, using info from tbl_crop, then change the primary key on tbl_crop into a foreign key (which relates to tbl_usda). Once this is done, u can change the genus/species/infraspecies fields on tbl_crop to be auto-enters which get their value from the related tbl_usda table.

 

I hope all this makes sense. Be aware if spelling doesn't match between the usda fields and the crop fields in the relationship, it wont evaluate, all 3 will have to match.