# Calculated Columns Same Table And "Column Scrubbing"

1. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12

## Calculated Columns Same Table And "Column Scrubbing"

Ok so these are very simple (potential stupid) questions. Here goes....

1. I have a need to calculate several new columns based on columns that exist in the same table. To illustrate:

Item (Text)
Revenue (Text)
Cost1 (Number)
Qty (Number)
Cost2 (Number)
ItemCost = NEW column = MAX of (Cost1 and Cost2)
TotalItemCost = NEW column = (Qty * Item Cost)
TotalItemRevenue = NEW column = (Qty * Revenue)

How can I do this at the query level?

2. I need to "scrub" a column or in other words, remove a couple of letters before a set of numbers (OrderID). I only have 2 instances:

M123456
SO123456

I tried to simply find and replace but I have too many rows. Any idea on how to do this at the query level? (I'd also like to preserve the original OrderID column so I will probably just replicate the column then create an expression for the "scrubbed" one)

Thank you in advance for your help!

2. VIP
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2010
Location
Dayton, OH
Posts
2,901
Technically speaking, if an item has more than 1 associated cost, that describes a one-to-many relationship, so the two costs should be separate records in a related table. I'll ignore that for now. You could use an expression

SELECT ..., IIF(Cost1>=Cost2,Cost1,Cost2) AS ItemCost, (Qty * ItemCost) as TotalItemCost

Now since you say that the Revenue field is text, you cannot do (Qty * Revenue) without first converting the revenue to a number. (cannot do mathmetical computations on a text value).

Now as to scrubbing the letters from some alphanumeric values in the OrderID field, there are several text manipulation functions available within Access. So is there a defined pattern? Obviously the number of letters at the beginning of the string can vary. Can letters appear within the string? At the end of the string? Is the numeric portion always the last six characters?

3. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12
JZWP,

Long time no talk! I made a few errors in my post and didn't clarify. Please allow me to do so:

The reason I have two cost columns is that I joined in missing cost data (aka - sku's that did not have costs in the order management system and were provided manually for a select few SKU) and in all cases, either cost 1 or cost 2 = 0 with the other containing the correct cost. So it seems like your query would solve this for both revenue and cost!

Also, the Revenue is actually a number (oops!) So I am assuming the syntax would be the same as the cost...

Finally, in regards to your final questions on scrubbing, there are only 2 distinct variations that exist:

M123456
SO123456

So in other words, for all orderID's there exists either a "M" OR a "SO" text value preceding the order number. No letters will ever appear in the middle or end of the string and the numeric portion is only in the last 6 (or 7) characters. (sorry for not clarifying here!)

Thanks again!

4. VIP
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2010
Location
Dayton, OH
Posts
2,901
If the numeric portion of the orderID is ALWAYS six characters then we can use the right() function

SELECT Right(OrderID,6) AS OrderIDnumber

Of course the above still returns a text value, so if you want it to be a number, you will have to convert it

SELECT clng(Right(OrderID,6)) as OrderIDAsNumber

5. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12
JZWP,

AWESOME! With this, I can complete my data organization work for this project.

Thanks so much for all your help!

6. VIP
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2010
Location
Dayton, OH
Posts
2,901
You're welcome.

7. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12
Originally Posted by jzwp11
Technically speaking, if an item has more than 1 associated cost, that describes a one-to-many relationship, so the two costs should be separate records in a related table. I'll ignore that for now. You could use an expression

SELECT ..., IIF(Cost1>=Cost2,Cost1,Cost2) AS ItemCost, (Qty * ItemCost) as TotalItemCost

Now since you say that the Revenue field is text, you cannot do (Qty * Revenue) without first converting the revenue to a number. (cannot do mathmetical computations on a text value).

Now as to scrubbing the letters from some alphanumeric values in the OrderID field, there are several text manipulation functions available within Access. So is there a defined pattern? Obviously the number of letters at the beginning of the string can vary. Can letters appear within the string? At the end of the string? Is the numeric portion always the last six characters?

ZWP,

I actually have a follow up question as to why my IIF statement is not working. I regardless of how I input this, only one of the costs will show up. I even tried IFF(Cost1 = Null or "",Cost2,Cost1) but still I am only seeing costs pulled from one column OR the other. In other words:

Cost 1 Cost 2 New Cost*
10 10
10

*newly calculated column with your formula

Any idea on what could be causing this? If not I am about to export to excel to handle this final step but I'd rather finish strong with Access!

8. VIP
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2010
Location
Dayton, OH
Posts
2,901
You said this:
..and in all cases, either cost 1 or cost 2 = 0
Zero is different from a null field. We have to take care of nulls if they are possible. Can cost1 ever be null? Can cost2 ever be null?

9. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12
Yes, both Cost 1 and Cost 2 can be null.

10. Novice
Windows 7 32bit Access 2007
Join Date
Jul 2012
Posts
12
I only realized this after looking more closely at the data. I though there were in fact "0" values. Sorry again for the error!

11. VIP
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2010
Location
Dayton, OH
Posts
2,901
OK, that will get a little more involved since we will need some nested IIF()'s. I think this should work:

IIF(isNull(Cost1) AND isNull(Cost2),0,IIf(isNull(Cost2),cost1, IIF(isNull(Cost1),cost2,IIF(cost1>=Cost2,Cost1,Cos t2)))) AS NewCost

If both cost1 and cost2 are null, the above returns 0.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums