working as a vlookup function

[cleon]

Hi there im trying to calculate factors base of input percentages. The problem is that you can't join the tables because the input parameter is in a range. This formula is easy in excell buy in access is making me crazy.



For example if the results for Supervisor Lima ED - centro were 136.13% for porcumplpost2g, the factor should be 1.2. If the results for Supervisor Lima TxD - Sur were 96.59% the factor should be 0.95. This factors correspond to tbl perfomance column Factor Post.

In addition, if the results for Supervisor Lima ED - centro were 102.47% for porcumplintot3g , the factor should be 1.1. If the results for Supervisor Lima TxD - Sur were 102.24% the factor should be 1.1 too. This factors correspond to tbl perfomance column Factor in.

This factors should be completed in all the 15 registers in the query sales per zone factors

Attaching the BD

Please help me.

Pd. Im working in excel 2007, windows vista
Best regards, Carlos

[jzwp11]

Welcome to the forum.

In order to do what you want you will need a coupe queries that each have nested queries.

To make things a little easier I added a new field to [tbl performance] which is just a sequence number for the ranges. I also added another record to set an unachievable maximum level in order to set a maximum for the last range. From there, I created the query qryRanges which utilizes a subquery that bring the max value for each range. This allows me to have both the lower and upper ends of the range in 1 record. Please see the attached database.

I then altered your Sales per Zone Factors to include 2 subqueries that pull the two factors from qryRanges depending on the ratios you had.

By the way, it is generally recommended to not have spaces or special characters in your table or field names. Also, I would probably not format the range values in percent in the table; I would just leave them with no format (you can format them in your query when you want).

[cleon]

thanks jzwp11!! I really apreciate your help.!!!
The best gift for Christmas
Gracias Hermano

[jzwp11]

You are welcome. Have a great Christmas