Just open field options for LinesPrice and set it to auto-enter the value of Price from the prices table. This can be an auto-enter calculation or you can use the looked up value option. Prices 2 is not needed to make this work.
Thanks but I still don't have it working on either of those options, any suggestion of what I may have wrong. I might not have asked the question properly - It needs to drill down to get the correct Price when (RentalLines::LinesDept = Prices::Dept) and (RentalLines::LinesDays = Prices::Days) and (RentalLines::AgeGroup = Prices::AgeGroup) and (RentalLines::BootsCode = Prices::BootsCode). All 4 conditions met would leave on one record in the Prices table.
In your sample data, the _fkPricesID field is empty and thus no record in RentalInes currently matches to any record in Prices.
But that's not the only issue. _pkPricesID is a unique value in your table. If you match records using the _fkPricesID and _pkPricesID fields, then you cannot also match by the other three fields since this pair of fields can only match to one record to begin with. The other match fields serve no purpose in matching to a specific record in PriceLines.
There are two approaches you might take to get this to work:
Use this relationship:
RentalLines::_fkPriceID = Prices::_pkPriceID
And then use days, age group and department in a conditional value list to limit the options that pop up when you select from a value list that enters an ID value into _fkPriceID.
Or set up a relationship that does not use the _fk to _pk pair of match fields.
Just from the data provided, I can see two records in Prices where Dept is Skis, AgeGroup is Adult and Days is 2. The only visible difference is the BootCode. If that's significant difference--the reason why one option is $55 and the other $35, then you could use this relationship:
RentalLines::AgeGroup = Prices::AgeGroup AND
RentalLines::Days = Prices::Days AND
RentalLines::Dept = Prices::Dept AND
RentalLInes::BootCode = Prices::BootCode
Thank You I feel like such an idiot for not seeing it- it totally makes sense now "I just removed the _fk to _pk match fields and it worked" I just had it stuck in my mind that that had to be there, even though I have some conditional value lists with their own TOs.
This forum is such an awesome resource, especially when you work on a database by yourself without anyone else to bounce off.
But just removing the PK and FK fields WON'T work!
Note that you have at least two records in your sample data where the department, days and age group are identical, but the price is not. You need some way to further refine the matching to match to the correct record. I can't tell whether or not the "BootsCode" is the missing factor or not.
Yes the "BootsCode" is definitely one of the match fields, it is a result of a calculation to determine whether the rental is "WithBoots" "WithoutBoots" or "BootsOnly" giving an "A" "B" or "C" result in that field. I have the Prices working from your help in my actual database now which has nearly 500 different prices and is matching back to last years data correctly. Thanks for the help.