Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# MS Access Query: Multiply two fields

Posted on 2004-08-12
Medium Priority
2,262 Views
I have a DB  in MS Access:

Fields:
Batch - Text
Amount - Currency
Percent - Number
Sold - Yes/No

Batch        Amount         Percent       Global
---------------------------------------------------
A5774B    4556.25          90              Yes
C55647    4435.85          85              No
A5774B    5456.55          100            Yes

-------------------------------------------------------------------------------------

I want to make a query that will output the (Amount * Percent/100)
If it is global it will be in the global fiel else in the sub field

viz.

Batch        Global             Sub
---------------------------------------
A5774B    4100.63
C55647                          3785.77
A5774B    5456.55

solution could can be in sql as well!
0
Question by:rishabhpatel
[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

LVL 17

Expert Comment

ID: 11782066
Try this :

SELECT Batch, Amount*Percent/100 as Global, NULL as Sub
FROM MyTable
WHERE Global = 'Yes'
UNION
SELECT Batch, NULL as Global, Amount*Percent/100 as Sub
FROM MyTable
WHERE Global = 'No'
0

Author Comment

ID: 11782092
That will get too long for a large query... I want to use a solution with an if statement
0

LVL 17

Accepted Solution

BillAn1 earned 1000 total points
ID: 11782123
SELECT Batch, iif(Global = 'Yes',Amount*Percent/100,NULL) as Global2, iif(Global = 'No',Amount*Percent/100,NULL) as Sub2
FROM MyTable
0

LVL 1

Expert Comment

ID: 11790623
Is it ok if I write a small prodedure?

Amulya.
0

LVL 17

Expert Comment

ID: 11790946
I'm not sure what a procedure would benefit - it would basically contain the same SQL. If you split the query, you are going to re-create either the union approach, or the iif approach, which will do the same thing, but be less efficient?
0

LVL 1

Expert Comment

ID: 11792739
I don't think it would enhance the performance if you create a procedure and compile it, as the query is without greate complexity.

First and second both soulusion are correct, I would prefer IIF() one.

Best of luck
0

LVL 17

Expert Comment

ID: 11792841
I suspect the union method will be quicker particularly if there is an index on the 'Global' field, as the IIF has to evaluate 2 function calls per row, as opposed to dividing the table in 2 via an index search.
As for performance of procedure - the time here will be in the actual calc, not the compilation etc so there is no benefit in that regard.
If performance becomes an issue it is indexing that will solve it.

Can you explain the term
"That will get too long for a large query... "

long queries are only a problem for programmers, and if it is A union B you just copy/paste the two halves. The database won't mind a long text string.
0

## Featured Post

Question has a verified solution.

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

In this blog post, weâ€™ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month4 days, 8 hours left to enroll