Help Please

[affan]

Dear all,

I would like to know if you can help me with my Plant Rates which are calculated on Daily, Weekly & Monthly basis. I want to design the DBMS in a such a manner that, the rates are picked considering the number of days hired.

For example if the plant has worked for below 7 days day rate should be considered, for 7 to 14 days weekly rate should be considered and for more than 14 days monthly rate should be considered.

I hope you can help me in this regard. Eagerly waiting for your reply

 

[pr2-eugin]

Hello affan, Welcome to AWF

A simple function springs to mind for what you want to do. But then, will this tend to increase or change in the days to come?

 

[RichP]

You can use a 'BETWEEN' join between two tables to do this if you like. Then one table is a look up table of the rates e.g.
from, to, rate
1, 6, 10.23
7, 13, 8.345
14, 100000, 4.22

 

[affan]

You can use a 'BETWEEN' join between two tables to do this if you like. Then one table is a look up table of the rates e.g.
from, to, rate
1, 6, 10.23
7, 13, 8.345
14, 100000, 4.22

hi sir,
thank you very much for your reply can you give me any sample mdb it will help for me i am new

 

[RichP]

i'm sorry i haven't made enough posts to upload a sample db. I also made a small mistake

Anyway, assume you have two tables. Table1 contains the data ou want to calculate the rate for and has a field called 'days' containing the number of days

RateTable contains three fields called FromDay, ToDay and Rate. If 'days' is between FromDay and ToDay then 'Rate' is the rate

Normally in Access you would create a query using the 'design view' and you join to tables by drawing a line between two fields. This doesn't work in this case so you need to switch to creating a query in SQL mode. The sql looks like this

SELECT Table1.days, RateTable.Rate
FROM Table1 INNER JOIN RateTable ON Table1.days >=RateTable.FromDay AND Table1.days <= RateTable.ToDay

As you see I have had to use <= and >= because BETWEEN doesn't work in this case

Anyway - I would suggest you try creating a few tables and queries first and then looking at the sql of the queries to get an idea of what is going on. Until you do that the stuff written above will look like gobbledegook but afterwards it should make sense

good luck

 

[pr2-eugin]

i'm sorry i haven't made enough posts to upload a sample db.

You can upload ZIP files. Create your DB, then ZIP the file, then try uploading.

 

[RichP]

You're right, I can
Here ya go - the query Query1 does what you want I think

 

[bahadhurs]

Dear RichP


thank you very much for your quick reply and your help.

 

[affan]

Dear RichP


thank you very much for your quick reply and your help.