Cannot add record(s) join key of table error

[qxvqt]

Hi there, I'm new to databases and I have a problem.

I'm doing a TAFE assignment (I'm from Australia) and I have tried to populate a field in my subform called RestaurantID with data and it has given me the error

'Cannot add record(s) join key table Restaurant not in recordset

I have attached the database I am working on as an attachment.

Thanks/

 

[namliam]

I have my doubts about your design, but your query is non-updatable because the join field of your Restaurant table is not in your query.

Random tips:
- use a naming convention to keep things seperated i.e., tblRestaurant, qryRestaurant, frmRestaurant ....
- Keys, your contract id seems to be a natural key, i.e. something that is used in real life as well.
"good practice" is to not use these as your database key, but to use an autonumber field as your (hidden) key values inside your database.

For me as a european idiot, TAFE assignment?

 

[qxvqt]

I have my doubts about your design, but your query is non-updatable because the join field of your Restaurant table is not in your query.

Random tips:
- use a naming convention to keep things seperated i.e., tblRestaurant, qryRestaurant, frmRestaurant ....
- Keys, your contract id seems to be a natural key, i.e. something that is used in real life as well.
"good practice" is to not use these as your database key, but to use an autonumber field as your (hidden) key values inside your database.

For me as a european idiot, TAFE assignment?

I appreciate your input but this is the third and last assignment I ever have to do on a database so specifics don't bother me, they will accept the names as they are.

What do you mean my contract id is a natural key?

They won't accept autonumber, I've already tried that previously.

TAFE is education after grade 12, you can either go to tafe and get certifcations (1,2,3,4 or a diploma) or go to university and get a degree.

Also, sorry I'm still confused as to how to fix my problem.

 

[namliam]

Natural key is a meaningfull key, something that the user inputs....
For example your restaurantID, you dont have Restaurant Name in that table.... Thus RestaurantID could very well be it's name.
There is no "real" uniqueness to names of restaurants and even if there was/is it may remain the same restaurant under the same contract etc.... but still change name... Changing (Primary) Keys is a nightmare...
This is true for your different keys .... using a (hidden) autonumber (as a surogate key), will make for a much more stable and constructive database.... but if these are your constraints, then yeah... just for reference I guess.
For more details ... check wiki: http://en.wikipedia.org/wiki/Natural_key

The thing about your design is... What happens if a restaurant has a contract for 2 years and it expires and then is renewed again for 2 years but with different royalty/fees etc...
You either lose your old contract data or atleast lose the join between the restaurant and its old contract(s)
Same for Franchisee, what if I sell my franchisee to John Doe?

To fix your immediate problem, in your recordsource of your subform you have the query:

SELECT Contract.ContractID, Restaurant.RestaurantID, Restaurant.Franchisee
FROM Contract 
INNER JOIN Restaurant ON Contract.[ContractID] = Restaurant.[ContractID];

This query does not have the joined field from the Restaurant table in the select
So you must either add Restaurant.ContractID to the Select part or change Contract.ContractID to that of Restaurant which would obsolete the join to Contract table all together.

 

[qxvqt]

Sorry I am still insanely confused, everything you are saying is making no sense...

 

[namliam]

It all makes perfect sense, but did you even read this part? or try it?

To fix your immediate problem, in your recordsource of your subform you have the query:

SELECT Contract.ContractID, Restaurant.RestaurantID, Restaurant.Franchisee
FROM Contract 
INNER JOIN Restaurant ON Contract.[ContractID] = Restaurant.[ContractID];

This query does not have the joined field from the Restaurant table in the select
So you must either add Restaurant.ContractID to the Select part or change Contract.ContractID to that of Restaurant which would obsolete the join to Contract table all together.

What dont you understand about this?

 

[qxvqt]

It all makes perfect sense, but did you even read this part? or try it?


What dont you understand about this?

I know literally nothing about access and it was hard enough wrapping my head around primary and foreign keys but I've never heard of anything you are saying nor do I understand what the hell to do, it's all jargon to me.

 

[namliam]

If you go to your subform Restaurant subform and look at the properties
The first property is the Recordsource, aka the query feeding your form... This query needs to be changed to fix your immediate problem...

It wont fix your overall issue, but will fix this issue