Rate changes automatic when i click (urgent)

[bahadhurs]

Dear all

Please find the attachment andkindly help me with the calculation commands mentioned. I need to adopt these calculations in the DBMS.

Since I am new to the DBMS, I am not familier with the advanced calculation commands. So, I kindly request your valuable guidance and suggestions to improve my knowledge in DBMS.

Eagerly waiting for a reply from your end.

 

[oleronesoftwares]

hi,
you can check the file attached, i hope it helps.

i altered your table to include a start date, and end date

the calculations you want are in the form called "ratescalculation"

 

[bahadhurs]

Dear Sir,oleronesoftware

I appreciate your quick response, I have gone through your altered Data Base and it serves my purpose manually. The main problem with the same is its a bit time consuming since we are having so many plants working in our site, individual entries and checking of each Plant is not possible. I want my Data Base to be designed as mentioned below.

Once I click the command button, All the plants in Table 1 (Rate Table) should be picked and then go to Table 2 (Data Entered Table) check for all the plants worked as per the below mentioned three conditions. After this the rates in Table 2 should be automatically replaced with the rates (Daily, Weekly or Monthly) picked from Table 1.

I am looking for the command which will help me link all the below mentioned conditions and give me the desired output. Instead of giving the Start Date, Finish Date and Plant No manually, I need the DBMS to do it automatically.

I Have designed my Data Base on Day rate basis, if the plant has worked for more than 6 days I want the week rates to be applied as per Table-2

Following are the conditions which should be applied while calculating rates.
1) If the Plant No = ''222'' check for Date < or = 06-01-14 then day rates should be applied.
2) If the Plant is hired for > 06-01-14 but < or = 14-01-14 then Weekly rates should be applied.
3) If the Plant is hired for > 14-01-14 then Monthly rates should be applied.

When I click the command button, I need the calculations to be done without any manual input as per the condition.

I Hope you got what I am really looking for. Eagerly waiting for your response

 

[oleronesoftwares]

hi,
i think what you want is achievable, but i have two observations that you have to clarify.

1. in table one,there is no field for number of days(you will need a field that stores the number of days)

2. Do you want to store the the data in table two, or you want the table two data to be overwritten, with the current(latest number of days per plant)

3. what period will the rate calculation done,is it weekly,monthly etc, though i presume it will be done monthly.


once you answer the questions above,then we can go ahead,but to give you a hint, what you want can be done using multiple insert queries to populate table two at the click of a button, these insert queries will then be grouped into macros.


cheers.

 

[bahadhurs]

Hi, sir,oleronesoftware

I am very glad hearing your reply. Following are my suggestions for the observations.

1) In table one is it necessary to create a field for number of days because my observation was the number of days can be calculated from Table two by counting the Date field. If this is not possible please create a field in Table one for the number of days.

2) I want the data to be stored in Table two and at the same time I want the same to be overwritten with the click of the command button as per the conditions being applied.

3) While entering the data the rate shall be calculated in weekly basis.

thank you very much your effort

 

[oleronesoftwares]

hi,
pls check the attachment, the following are the changes i made.

1. new field for nos of days in table 1

2. your command button in form 2, has been attached to a macro that will insert associated rates from table one into table two for all plants.

to use the attachement, open table 1 and enter data into the "nos of days" field for all plants.

then go to form2 and click on the command button,this will run the proccess of populating the rate calculation form.

if you know how to disable action query pop-up in ms access you can disable it ,but if you dont know to disable you can leave it,

if prompted when you click on the command button, continue to click yes for every pop up that comes on, until use see the msgbox saying the routine has finished.

the routine will overwrite what is in the rates calaculation form(table2),



i noticed a qty field and amount field in table 2, you didnt say what its meant for, pls let me know what its meant for.


thanks.

 

[bahadhurs]

Hi,

Thanks a lot for your reply.

The Qty field in Table 2 is for the Hours and the Amount field is for the cost. The Hours will be multiplied with the Rate and the output will be the Cost (Hours x Rate = Amount (Cost).

I only want the rates to be overwritten in Table - 2. Once the rate is overwritten the Amount field should be automatically calculated with the updated rates.