[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Calculation in Access

Posted on 2015-02-19
Medium Priority
92 Views
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
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
• 2
• 2

LVL 48

Expert Comment

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

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

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

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

## Featured Post

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…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
###### Suggested Courses
Course of the Month14 days, 16 hours left to enroll