Problem linking drop list to field values?

[clayfacebatman]

OK - I'm a bit of a novice at access and the answer to this may be very simple but any help would be much appreciated.

I have a problem getting a Combo box field in a table to link to another table and input a relevant value in another field of the same table. Here is what I've got:

Table 1 - Client
Fields - CLIENT ID, CLIENT NAME

Table 2 - Client Rates (The rates charged to the client for 3 specific items that do not change)
Fields - CLIENT ID, SC, LX, SX. (These last three are the codes for the items and the values in the field are the currency values for the items as they are charged to the specific client)

Thanks
Table 3 - Job Details
Fields - JOB ID (Autonumber for the Job), CLIENT ID (Who the job is for), ITEM (This is a combo box that selects between the items i.e. SC, LX and SX), RATE (I want this to recognise the item chosen in the previous field and display the corresponding rate for that item and for that client - This is my problem!)

I will attach the database as I currnetly have it so that you can see what I'm talking about. As I say - any help is muchly appreciated

Thanks muchley

 

[ScottGem]

The problem is your structure. You have what is called a repeating group in table 2. that violates normalization rules. The strucutre for table 2 should be:

RateID (PK Autonumber)
ClientID (FK)
RateType (SC, LX, SX)
RateAmount

From there is a simply matter to join on ClinetID and RateType to return the Rate.