Sorry for the delay in replying. Thank you so much for this Jason.
OK based on what you have said how do I go about entering this information as we receive the information as follows:
Each company supplies a list of what occupations they are willing to hire and in what locations:
e.g.
Company B list would look like this:
Carpenter Location 1, 2, 4
Plumber 2,3,4
Electrician 4, 5
Bricklayer 6
So I need to set up the insert and update form as follows:
Company Name: Drop Down Menu (alloiwing only one selection) at the top
Occupation : Now sure how to handle this as there are over possible 500 occupations so drop down box or check box isn't going to work. Ideally would like to type in C (say for Carpenter) and it bring up all the occupations beginning with C and I can choose from that list or put in an occupation code in (which many of the companies use which is formatted as follows: 1234-56) and that is verified in some way against the occupations table (there is an occupation code field in this table).
Locations: This can be a check box allow multiple locations.
Ideally this would bring up excisting occupations that are currently required by the chosen employer through the update multiple records behaviour and give the user the option to either update the excisting locations against the occupations listed or add new occupations or delete occupations (and obviously their related locations).
Hope this makes sense. I have been reading about MRT behaviours, but not sure if this would apply as the occupations cannot be put into a drop down list or check box because of the number of options. Plus not sure how to list this all together on one page.
Also this may seem like a really stupid question, but I notice in your tables you use different field names for the foreign key names from their original primary key names - can you use the same name or is there some reason why you should use different names in each table?
e.g.
occupationlocations:
occupationlocationID - primary key
occupationlocationOcupation ID - Foreign key to Occupation Table Occupation ID
occupationlocationLocationID - Foreign key to Job Location Table Location ID
could I use:
occupationlocations:
occupationlocationID - primary key
Ocupation ID - Foreign key to Occupation Table Occupation ID
LocationID - Foreign key to Job Location Table Location ID
Thank you again Jason. One day I will get my head around this and stop buggin you, but desperate to understand and learn, so hope you can help.
