Formula in a query refering to a table of reference

C

Céline Brien

Hi everybody !
Instead of using this formula in my query :
CostKm: Iff([Date]<38620;0,35;0,4)
I would like my formula to refer to a table of reference where user
would type the date of new cost and the cost.
1 2004-01-01 0,40
2 2005-09-25 0,35
3 ...
Many thanks for your help,
Céline
 
M

MadeleineP

Hi Céline,

Just type in the criteria date column [Please enter the date:] and the
criteria cost column [Please enter the cost:] .

"Céline Brien" a écrit :
 
C

Céline Brien

Hi Madeleine,
Thanks for your answer, but it is not what I need.
This query is feeding a report where I have to calculate the total cost
of Km by month.
Céline

MadeleineP said:
Hi Céline,

Just type in the criteria date column [Please enter the date:] and the
criteria cost column [Please enter the cost:] .

"Céline Brien" a écrit :
Hi everybody !
Instead of using this formula in my query :
CostKm: Iff([Date]<38620;0,35;0,4)
I would like my formula to refer to a table of reference where user
would type the date of new cost and the cost.
1 2004-01-01 0,40
2 2005-09-25 0,35
3 ...
Many thanks for your help,
Céline
Click to expand...
Click to expand...
 
V

Van T. Dinh

Céline

There are a few different ways of getting the applicable rates from a Table
"tblRates".

* The easiet way (but probably not the most efficient) is to use the
DLookUp() function to get the applicable rate. Check Access VB Help on the
DLookUp() function.

* The more efficient way is to construct a Query that involves SubQueries to
select the appropriate rate. However, you will need to know a fair bit of
JET SQL to be able to construct Query / SubQueries.

* I have recently involved in a discussion on similar topic which has a
sample database download. See if you can work your way through the download
provided by Doug from:

http://tinyurl.com/aa24k
 
C

Céline Brien

Hi Van,
Thank you so much for your answer.
I worked out my way through the download. It was not easy but I got it.
I am going to try to adapt this solution to my database and come back
later to tell you if it worked.
I am quite confident that it will.
It is a great + for me.
Thanks again and have a good day,
Céline (alias DameDuNord)
 
V

Van T. Dinh

You're welcome ...

Doug's Query / SQL is probably the most efficient out of all the different
methods. For small Tables this is probably not important but for Tables
with large number of Records, the processing speed difference is quite
noticeable.
 
C

Céline Brien

Hi Van,
It worked ! A great improvement to our database.
I was aware that with large number of Records, the processing speed
difference is quite noticeable.
Another + with this solution, I can create a form with the tbRates and
add it to the menu to let the users add a new Rate and EffDate.
Thanks again,
Céline
 
Top