?
Solved

Calculation in Access

Posted on 2015-02-19
4
Medium Priority
?
91 Views
Last Modified: 2015-02-19
I have a problem I am tracking units delivered into 4 categories.  I created a field for each category and if they get a delivery a 1 or 2 is placed in the boxes that was delivered.  When we bill I sum the totals of each field and multiply the sales price.

Except when they get a delivery of box 1 and box 2 together.  Then I need to give them a special price.  So ... if
box 1 they get 20 delivered and box 2 they get 15 delivered I need to calculate this way.  

Combined box 1 and 2  15 * 7.75= 116.25
in Addition I need 5 * 4.50 =  22.50  
 So the total of the boxes would be $138.50

I can do it if Box 1 and Box 2 are equal no problem but if Box 1 and box 2 are not I run into my complication.  

I have to have it as a separate calculated field so I can mail merge it to my word files for the bill..

Help?
0
Comment
Question by:cres1121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40619584
Not sure I understand, you mention 4 categories and fields for each category, but then only discuss box 1 and box 2 in your example.  Can you provide some sample data, maybe in a spreadsheet with your computations showing in the example?
0
 

Author Comment

by:cres1121
ID: 40619868
This is a spreadsheet with the export from a query.  Box 3 and Box 4 are just calculated total * 4.50.  The only problem I am having is when box 1 total heatable and box 2 totalcold do not =.  I have a iif doing the logic if box1= box2 then box1*7.75, box 1 *4.5.  In Box2 the iif is  if(box1=box2, then 0, box 2*4.5)

The problem is if box 1 has 20 and box 2 has 15 it does each at 4.50 so a total of 35*4.50.  The company would like 15 at 7.75 and 5 * 4.50.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40619909
Since the individual prices of Box1 and Box2 are both 4.50, then you should be able to use:

IIF(Box1 = Box2, Box1, IIF(Box1 < Box2, Box1, Box2)) * 7.75 + Abs(Box1-Box2) * 4.50

The first part (nested IIF() statement returns the number of boxes in common.
The second part returns the difference between the number of boxes.
0
 

Author Comment

by:cres1121
ID: 40620381
Thanks I needed one more layer but your response got me thinking.  I appreciate the information
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question