• MS Query in Excel – Create a new column

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » MS Query in Excel – Create a new column

    Author
    Topic
    #458958

    Hi,
    I’ve created a MS Query in Excel, and I’m wanting to add a new column, not a column from the file I’m querying. For example, below is the data from the file, and the far right column (New Period) is the data I want to create with a formula:

    Company Nature Period Amount New Period
    75 4000000 JAN09 1,000.00 JAN09B

    I want to define a new column (the New Period above), by putting a formula in the field heading area.
    I’ve used formulas such as:
    left(Period,3) and the result would be JAN, and this works just fine.

    Unlike Exel, MS Query will not let me use the = sign. Likewise, it doesn’t like the & sign, which is what I tried to do by using this formula:
    Period&”B” to create the New Period called JAN09B

    Does anyone know how I could create this inside the MS Query, as opposed to having the formula in Excel AFTER the data has been downloaded. I’m trying to eliminate formulas in Excel, plus I’d simply like to know how far I can take the formula’s inside MS Query. I’d love to know if an IF Then formula inside MS Query works as well.

    Any & all ideas are appreciated!

    Thanks!!
    Lana

    Viewing 0 reply threads
    Author
    Replies
    • #1155768

      In MS Query, select Records | Add Column…
      In the Field box, enter the formula without an =, and use single quotes around a string value. In your example:

      Period & ‘B’

      Enter the name you want to give the new field in the Column Header box. In your example: New Period.

      You can’t use Excel worksheet functions in MS Query, but you can use(some) VBA functions in expressions. For example, the equivalent of Excel’s IF function is IIf. For example:

      IIf(Amount > 1000, ‘Large’, ‘Small’)

      • #1155770

        Thanks Hans… I’m still having trouble with the formula’s. It always gives me these messages:

        Error for when I try using the > sign is this one….
        SQL0104 – Token > was not valid. Valid tokens: ),.

        Error for when I try using the & sign is this one…
        SQL0104 – Token & was not valid. Valid tokens: +- AS .

        Now if I just enter ‘B’ in the field, then the letter B shows up down the whole column, but I’m trying to JOIN the period and the letter B. The If statement you gave me gives the 1st error message above.

        Any other ideas??

        Thanks!
        Lana

        • #1155771

          Can you select View | SQL or click the SQL button on the toolbar?

          If so, could you copy the SQL instruction and paste it into a reply?

          • #1155773

            I already tried to just type it into the SQL area (see below), but it didn’t work… thought I’d give it a whirl. I also tried using the word AND instead of the & sign, and that didn’t work either. The field called BSNBCD is the Period, so I’m trying to join the BSNBCD field with the letter B (as seen in the SQL below… which didn’t work).

            SELECT left(BSJ6CD,2), YABSREP.BSJ6CD, YABSREP.BSBPCD, YABSREP.BSJ7CD, YABSREP.BSBQCD, YABSREP.BSBEVA, YABSREP.BSNBCD, YABSREP.BSGRCD, YABSREP.BSJ9CD, YABSREP.BSGPCD, YABSREP.BSGQCD, YABSREP.BSOMCD, LEFT(BSJCNB,4), YABSREP.BSBEVA, BSNBCD & ‘B’
            FROM SCAR.AMFLIB.YABSREP YABSREP
            WHERE (YABSREP.BSJCNB Between 200700 And 200912)

            • #1155779

              I’m afraid I don’t know why it doesn’t work. The syntax looks correct to me, and similar expressions work OK for me, without error messages.

            • #1155785

              Where do the data come from? An Access or SQL Server database? If so, you could create a query in the database, and get the data from that query instead of the table.

            • #1155786

              The data is coming from our AS400. The software we use is called MAPICS.

            • #1155788

              I have no experience with MAPICS, so I have no idea what is possible with it and what isn’t. Perhaps someone else will have a suggestion.

    Viewing 0 reply threads
    Reply To: MS Query in Excel – Create a new column

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: