Solved

Calculation in Access

Posted on 2015-02-19
4
86 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)
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 47

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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