Run query for particular records if a condition is met

[harshadborde]

Hello Friends,

I am trying build a logic for running queries if specific condition is met.

Basically I have a database of 5000 contractors for which I want to calculate rate. There is a 'Rate table' which have rates for 70% people but not for all. So I am calculating rates for rest of people based on 4 different logics (Like based on combination of department, designation, projects, location). I have 4 different queries that calculate 5 different rates.

Rate0 --> from Query0 on rate 'Rate Table'
Rate1 --> Query1
Rate2 --> Query2
Rate3 --> Query3
Rate4 --> Query4

Now I want only one rate per employee number.
As of now I am calculating all 5 rates for all 5000 employees and then selecting on by using if not. Current code as below.

IIf([RATE0] Is Not Null,[RATE0],(IIf([RATE1] Is Not Null,[RATE1],(IIf([RATE2] Is Not Null,[RATE2],(IIf([RATE3] Is Not Null,[RATE3],[RATE4])))))))

It takes too much time.

I want to ask if it is possible to write query, to run query0 if Rate0 is blank it will run Query1 calculate Rate1 only for those employees and so on. It will break once you found rate.

I will be really grateful for any kind of help.

 

[Ranman256]

either,
run a macro to run all the queries. Each would handle each rate
or
make a table, put the rates and queries in it.
run code to scan thru the table running each query.

 

[harshadborde]

As of now I am running all queries and rate from them by using code I mentioned above. But I know that I am getting my 70% Rates from first query itself. I just want to run other queries for those 30%.

In excel, we used to set a flag for null and run the vlookup if flag is positive. Can Similar thing be done here?