Solved

Calculation in Access

Posted on 2015-02-19
4
84 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
  • 2
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
Comment Utility
Thanks I needed one more layer but your response got me thinking.  I appreciate the information
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now