Does your relationship graph link Table_B -< Table_A (one-to-many) using Location as the key?
(If yes, I suggest that use a separate, auto-enter, serial number for the primary/foreign key instead because the Location text can change and may not be unique.)
The Location_Rate on Table_A is probably for a specific attendee on a particular date. Since location rates can change over time, I would use a Lookup which copies the current Location_Rate into Table_A from Table_B. That way, the rate on Table_A remains the same even if it is changed on Table_B later.
Edit the Location_Rate field in Table_A and check "Looked-up value" in the Auto-Enter tab. Specify Table_A as the "Starting with table" and Table_B as the "Look-up from related table". Then choose "Location_Rate" as the "Copy value from field". If there is no exact match, then select the "do not copy" radio button.
P.S. Since the Daily_Rate is a global field (and apparently not related to a location), you can probably add this field to Table_B and drop Table_C.
Location is where the attendee is from. Location Rate is dependent on Location and will represent a discount based on the distance travelled to the event, i.e. the further they travel the greater the discount - thus making the event a more equitable cost for all who attend.
Location Rates may change over time.
When using a "Look-up from related table" the value is not coming across to the field when the drop down menu item is selected. Thus my problem...
Daily_Rate can be anywhere, I realise this, I just wanted it in a different Table. Cheers though!
So I made it happen! Probably a work around and so am happy to hear others' solutions!
I made TableA:LocationRates a look-up field from TableB:LocationRates with a "do not copy" match value
with a Data entry of (number) 0
I made relationships graph of:
TableA:Location = TableB:Location
AND TableA:Location Rate X TableB:LocationRate
So on a new record in Table A, the LocationRate has a value of 0 before any data is input from the drop down menu of Location (based on a value list generated from TableB:Location).
Once a value is selected in TableA:Location the TableA:LocationRate looks up the value to replace it as the "X" places the value in no matter what is, as the relationship MUST be the same value for both fields with the AND scenario of the first line.
I hope this makes sense!