Adding field of one complex query to another.

[Nyanko]

Hi,

I hope this makes sense. I attach a zipped copy of my database in the hope that it clarifies my request.

I am needing to add RPI (tax) to an invoice value under certain conditions :
a) That an RPI date has been entered
b) That the RPI date is used to then find the correct multiplier to calculate the RPI amount.

My 2 main tables are :
tbl_MainData - which holds all the basics of each invoice amount
tbl_RPI - which holds the start of the financial year and the associated RPI value and multiplier

My desired result is to be able to add the correct RPI calculated amount [Qry_Rpi.RPIValue] to Qry_ClientSearch. I'm currently using a Qry_RPI to calculate the value depending on the whether the RPI date falls between the Start/End Dates for the RPI year.

Getting stumped and worried I'm over complicating things !
Suggestions and feedback welcome

 

[arnelgp]

please see if this is what you want, query1.

 

[Nyanko]

Thank you. This gave me the start I needed to get this sorted.