Results 1 to 11 of 11
  1. #1
    defaultuser909 is offline 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. #2
    jzwp11 is offline 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. #3
    defaultuser909 is offline 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. #4
    jzwp11 is offline 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. #5
    defaultuser909 is offline 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. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome.

  7. #7
    defaultuser909 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    12
    Quote Originally Posted by jzwp11 View Post
    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. #8
    jzwp11 is offline 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. #9
    defaultuser909 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    12
    Yes, both Cost 1 and Cost 2 can be null.

  10. #10
    defaultuser909 is offline 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. #11
    jzwp11 is offline 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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-25-2012, 06:42 AM
  2. "transposing vertical data into horizontal columns"
    By stevelondon in forum Queries
    Replies: 3
    Last Post: 03-20-2012, 12:36 PM
  3. Replies: 1
    Last Post: 04-25-2011, 12:36 PM
  4. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM
  5. Replies: 1
    Last Post: 12-11-2008, 01:28 PM

Tags for this Thread

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