Formula - Calculating Charges Based On Stepped Rates
Jun 20, 2014
Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rates.
For example:
Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
I have created a make table query and using expressions for two of the fields. I have set the field properties (format) to "Percent". The expressions are calculating rates based on two other fields. The expressions are currently as such:
tblTimeEntry records individual time slips for attorneys keeping track of their time. Each slip records the client, attorney, time, rate, and value (time * rate) of the slip.tblPeople holds all the timekeepers and their current rate. Their rates change once a year.tblCustomRate will hold the exceptions,
How I imagine tblCustomRate will be set up is as follows (and this may be how it's wrong):Columns for each timekeeper. Each record is a client entry. When a new time entry gets entered, as it gets saved into tblTimeEntry, it should check to see if the client number is one that has a record on tblCustomRate. If it does, it should find the column for the timekeeper and use that rate. could add columns to the tblPeople for each client that gets a custom rate and use IF statements to get it to add, but that's a lot of legwork and code for a few clients.
tblTimeEntry TimeID (primary key) ClientID TKFN (Timekeeper First Name) Rate Value
If it matters, this is Access front end with SQL Express tables. It's few and far between that I set up new People and it would be few and far between that there would be any custom rates (currently, there would be 2 out of 2000). People's rates change, but the time slips they entered at their old rate should stay at their old rate. If they were only worth $100/hour in 2010, but now they're worth $200/hour, the 2010 slips stay at the $100/hour rate.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I am making a table/form that will look at the wieght of an item and work out a costing based on a number of different delivery companies and will find the cheapest, I would like any comments on how to go about this.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like: =IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
I have a table with inventory items, a separate table with storage charges per day (ex .03, .04, .05 per day/per item)
I have created some queries where I take the items & # of days they have been in storage and when I try and create an expression for storage charges based upon QTY & # of days I am getting results like it is multiplying whole numbers and not very small increments like .03
I have checked the math, and its not multiplying by 3 instead of .03... I cant quite figure out how it is coming up with the numbers.
Again, the pricing is coming from a lookup wizard to another table. It seems like it should be a very straight forward expression but I cannot get it to work. Does the figures being from a lookup have any issues?
I got a chance to make some extra money outside of my usual job.
I am creating a billing system for a plumbing business. It's pretty easy and shouldn't take me long to do. The only thing is I'm not sure what to charge.
On my master form I have a subform called invoices this is tied by Site Number to the record on the form which is has the following important information, site code. group code, exp code an amount.
On another subform via a query I have a summary of all the invoices by exp code and group code.On another subform I need to create a Income an Exp.
I have two combo boxes 1 for the Group Code, and another for the expense code what I want it to now do is look up group code exp code = Total amount. There is currently only 1 line as you will see but there will be many rows of summary data...do i need a subform for the summary?
And I need to do it this way as not all exp appears on the income and exp and I want to be able to lay out the expenses for each site differently Attached is the zip file, of the database the only record that has data is site code 0289S
I am using a basic Order form and Order Details subform to capture my information. Customer is selected, then Products and Quantities are filled in... the subform calculates the total price. This all works great. Then I have a problem.
I have some national customers that require shipping to multi regions. I would need to select the regions and it has to calculate a new case cost for each product for each area. Could be up to 100 (10 products x 10 areas) new calculated prices.
Then for other customers, I will use just the case cost for the whole order (multiple products) and add one shipping fee to cover the drop. 3 products and only 1 fee added to the total.
Currently, my Order Form uses a Tab page and the Order Details are on the first tab. I could put the Shipping Info on the second tab, I just don't know how to add up to 10 different shipping regions to generate 10 new prices or the one drop fee to the total price.
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
I'm trying to create something like an online banking view that shows the running balance as each transaction occurs. I have tblTransactions with fields AccountNumber, ItemDescription, and TransactionAmount. I'm trying to create something that shows these three fields and a fourth field with the running balance.
So if I initially deposit $100 it will show the first record with TransactionAmount = $100 and RunningBalance = $100. Then the next transaction will subtract the Transactionamount for the new record from the RunningBalance from the previous record to get the RunningBalance for the new record. So if I make a purchase for $2, the AvailableBalance for that record is $98. Is this possible with a query? Here's a picture to describe what I'm talking about ....
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate John Smith$15$10$15$30$8 Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.
I have tried using the iif formula without success. Here is what I want to calculate.
I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.
If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....
If both fields are equal then score is 4.0 .05 score is 3.5 1.0 score is 3.0 1.5 score is 2.5 2.0 score is 2.0 2.5 score is 1.5
I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:
Transaction ID Order Number Customer ID Title ID Date Status
I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?
Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.
Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.
