OK, your issue is dependency. Determining a cost, pay rate, or other low-level monetary value can be only two basic ways here. You can compute it or look it up. Odds are that computing it won't be feasible. So you will probably have to look it up.
This leads to the principle that "Access can't tell you anything you didn't tell it first." But your critical issue is HOW you tell it. I'm going into "school-teacher" mode for a bit. Hope you understand it as an attempt to be instructive, not insulting.
Let's do this ab initio. On what does this monetary value depend? Let me take an example that might be close to your situation and show you how to structure that table.
First, you have a basic staff cost value, a monetary value of some sort. We assume you must look it up. OK, that has to be in some sort of lookup table. No problem so far.
On what does that value depend? How about some sort of staffing category, rank, or position? OK, that should be part of the same table. This is a "normalization" thing, in that a value that depends on something is a "dependent" field. The thing on which it depends is the "independent" field. Your value should therefore be selectable by a lookup based on the independent field.
Since it could be a long title and you don't want long table keys, have a table of job positions and a job-title code, maybe call it PosID. Then you want this lookup table to have the PosID and Title, and your pay table now has the PosID as a foreign key to the job title table.
Now you have the added wrinkle that the contract complicates that definition. Ok, so you have to know which contract that is. Well, that means you need a contract table. But I work for the government and I know how long contract names and "official" numbers can get, so I bet you'll need a contract ID number for internal use. Call it ContrID. Now you have a contracts table with a prime key of ContrID and the long name and other designations for your contract. So ... how do you USE that?
Well, you said something about the job title depending on the contract. There is another normalization rule that if something depends on more than one factor, you must include all of the independent factors as keys to that table. You just added a field to your job-title table, the ContrID. Here is where you have to make a decision and it requires knowledge of your business operational rules.
Case I: The pay rate depends less on contract and more on the employee's position code. You have a few position codes no matter what the title appears to be. I.e. contract job-title is a sham, the real discriminator is that job ID code and nothing else.
In this case, your job-title table repeats the job-code but the combination of job code and contract ID must be unique. (Compound primary key.) You can use the job code derived from the job title table to select one of a short list of values in your rate table and the contract code doesn't enter into the pay table except through selection of the pay rate number. I.e. an indirect dependence. Normalization says the contract code CANNOT be in the pay table in that case. It would represent a key for which no field in the pay table has any direct dependency. You have a diagram in which you have a dependency link from contract to job title and another link from job title to pay rate.
Case II: The pay rate of a give job code depends on the contract, i.e. Computer Operator I pays X$/hour on contract 1 and Y$/hour on contract 2. In this case, not only does the job-title table require the contract ID, so does the job-pay table. So you add the contract number to the pay rate table as well. That is because the pay rate is dependent on TWO elements - job ID and contract ID. So normalization requires both keys to be part of the lookup table. You have a diagram with an extra link this time. You have the link from contract to job title and from job title to pay rate. BUT you also have a direct link from contract to pay rate.
This is the kind of thinking you can use to construct the contents of these complex tables. Please be aware that there are also "deconstructive" methods for this. I just gave you the bottom-up method because it's what I use. You can apply this style of thinking to your problem one element at a time until you run out of elements.
Remember, dependency is of crucial importance. Things that depend on something else directly must have an ID of that something else in the table. This is a direct dependence. Things that depend only on that something else through another choice made elsewhere don't have that something else in the same table with them.
In either case, YOUR next step is to decide which case applies and search this forum for the topic called "cascading combo boxes." It is a way for you to select the contract ID first and then when you select job title, you only see the titles relevant to that contract.
If you have not read up much on normalization, NOW is the time to start. Or maybe even YESTERDAY. Google or Yahoo search (or pick another flavor) for Database Normalization. It is a crucial topic. But you'll get a gazillion hits on that. Narrow the internet search to the Wikipedia article, a couple of .EDU domain entries, and maybe the .COM sites of DB vendors you know about. ORACLE, Sybase, Focus, Ingres... and the manufacturers of those products. Access Help has a good starter article.
Search this forum, too. We have had some lengthy discussions regarding database normalization here. It will be a lot of reading, but when you get to the point that you have read the fifth or sixth article and don't feel you are getting any more out of it, you can slow down for a while.
Last hint about normalization. There are 5 recognized levels, sometimes referred to using the term "normal forms." As in, 1st normal form, 4th normal form, etc. MOST of the time, getting to 3rd normal form is enough for an Access level database. BUT don't forget that every problem is unique. Read up on the forms with person's names attached, because they represent oddball cases of dependency that aren't specifically addressed by the 1st-5th normal forms. They might not apply, but only YOU can analyze your problem well enough and intimately enough to know that.
