Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Change positive number to negative for some items in a field

  1. #1

    Change positive number to negative for some items in a field



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, have googled for a solution with no success so hoping to get help. Using Microsoft query through odbc connection have a table such as:

    Item Type Qty
    1 82 10
    2 71 5
    3 71 2

    Qty values for Type 71 should be negative amounts. How can I amend the query to have the Type 71 display negative amounts while retaining the type 82's as positive amounts.

    Thanks and kind regards

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there, and welcome to the forum.

    Can you go into Microsoft Query, click the SQL button, and paste the SQL query here for us? Easier to edit when we see what you already have.

    Surround it with code tags like this: [Code]Your query here[/Code] and it will format nicely for reading too.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    Thanks for this. Have not grasped the instructions about wraping in code. The IM_TYPE field is the one that has the codes 71 and 82 etc where 71 represents sales and comes through as a positive amount whereas want this to show as negative. 82 is item receipts into inventory.

    Code:
    SELECT Inventory_Movement_File.IM_ITMNO, Inventory_Movement_File.IM_TYPE, Inventory_Movement_File.IM_DATE,Inventory_Movement_File.IM_QTY,
    FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
    WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?) AND (Inventory_Movement_File.IM_QTY<>0)

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Try this:

    Code:
    SELECT Inventory_Movement_File.IM_ITMNO, Inventory_Movement_File.IM_TYPE, Inventory_Movement_File.IM_DATE,
    Inventory_Movement_File.IM_QTY*IIF(Inventory_Movement_File.IM_TYPE='82',1,-1)
    FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
    WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?) 
    AND (Inventory_Movement_File.IM_QTY<>0)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hi Ken,

    I get this error message " Syntax error in SQL statement. syntax error line 1 at or after token <IM_TYPE>". Only now am I dipping my toes in SQL so have no idea what the error message is. Grateful for further help.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Can you try removing the single quotes around 82, and if that doesn't work, but in full quotes?

    I'm pretty sure that's where the error is, and the resolution will depend on the data type in that field. (Text string or value.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Changed the = sign to ==, remembered seeing this in some obscured document, this got rid of the syntax error. Now error is incorrect column expression. Tried with and without quotes around the 82 figure to no avail.

  8. #8
    Hi Ken,
    Data type is unsigned char. More searching for me now to understand this.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I would think that unsigned char is a text field on some kind, but every database language is a little different. I'm sure you've noticed that the only part I added is multiplying the QTY by this:
    Code:
    IIF(Inventory_Movement_File.IM_TYPE='82',1,-1)
    I know Access uses IIF, but I'm not sure about your database... you could try IF instead.

    Do you happen to know what language the database is programmed in? MySQL, PervasiveSQL, MSSQL, Oracle, etc...?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Hi Ken,

    I know that Database vendor going to sql in next version. Not certain,but i think current database written in c+ or c++.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •