Solved

Calculation in Access

Posted on 2015-02-19
4
89 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 (Access MVP)
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 (Access MVP) earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

695 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