Results 1 to 6 of 6
  1. #1
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7

    Looking to add a parameter to my SQL code to multiple a group of records by -1

    Good day all, and happy Friday for some!



    I am currently working on building out a financial database used for monthly reporting. My code is currently taking trail balance information, grouping certain records together by account numbers and returning 1 category grouping with a $amt total. I am looking to include a statement within my code to multiple 1 specific category of records by a (-1) after the SUM has taken place so that my outcome is a negative number. My code is below:

    SELECT [Trend Type Grouping].[Trend Type], [ctAccountGrouping-PYvsCY].[Grouping Category], [ctAccountGrouping-PYvsCY].FY15, [ctAccountGrouping-PYvsCY].FY16, [FY16]-[FY15] AS [YoY Var], [YoY Var]/[FY15] AS [YoY Var (%)]
    FROM ([Trend Type Grouping] INNER JOIN [Account Grouping] ON [Trend Type Grouping].[Account Number] = [Account Grouping].[Account Number]) INNER JOIN [ctAccountGrouping-PYvsCY] ON [Account Grouping].[Grouping Category] = [ctAccountGrouping-PYvsCY].[Grouping Category]
    GROUP BY [Trend Type Grouping].[Trend Type], [ctAccountGrouping-PYvsCY].[Grouping Category], [ctAccountGrouping-PYvsCY].FY15, [ctAccountGrouping-PYvsCY].FY16
    ORDER BY [Trend Type Grouping].[Trend Type];

    Thanks for any help, if additional information is required, I'll be sure to provide if need be.

    -Grizz

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Which column did you want to make negative? Just multiple by -1.

    For example, "[FY16]-[FY15] AS [YoY Var]".
    To make it negative : ([FY16]-[FY15]) * -1 AS [YoY Var]



    You really need work on your naming convention.

    Suggestions:
    Don't use spaces in object names.
    Don't use punctuation in object names.
    Don't use special characters in object names.

    You have "[YoY Var (%)]" and "ctAccountGrouping-PYvsCY" (a dash)



    And your table/query names!!... They are sooooo long, you should think about using aliasing.
    Code:
    SELECT TTG.[Trend Type], AGPYCY.[Grouping Category], AGPYCY.FY15, AGPYCY.FY16, [FY16]-[FY15] AS [YoY Var], [YoY Var]/[FY15] AS [YoY Var (%)]
    FROM ([Trend Type Grouping] TTG INNER JOIN [Account Grouping] AG ON TTG.[Account Number] = AG.[Account Number]) INNER JOIN [ctAccountGrouping-PYvsCY] AGPYCY ON AG.[Grouping Category] = AGPYCY.[Grouping Category]
    GROUP BY TTG.[Trend Type], AGPYCY.[Grouping Category], AGPYCY.FY15, AGPYCY.FY16
    ORDER BY TTG.[Trend Type];

  3. #3
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    hi ssanfu,

    I have to agree with you, my naming convention could use some improvements. The beauty of Access (which I'm finding in my first built database) is there is much to learn as you progress. I'm often finding things I would like to do differently as a work my way through different scenarios. I appreciate the tip, and will take your advice.

    As far as the original issue goes, I'm looking for 1 of my category results to be multiplied by -1, not necessarily the entire grouping of results. So for example, my code is consolidating trail balance information down to summarize many account #s into 1 category, Revenue A, for example. The piece of code that im interested in is how to make the consolidation for category Revenue B get multiplied by -1.

    I know this isnt the correct syntax but this is the logic behind what im trying to do... When Category Grouping = Revenue B, Sum (Revenue B * -1)

    I hope this sheds some light on whats going on in my mind.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    As far as the original issue goes, I'm looking for 1 of my category results to be multiplied by -1, not necessarily the entire grouping of results.
    Have you tried using the IIF() function?
    Something like:
    Code:
    SELECT TTG.[Trend Type], AGPYCY.[Grouping Category], AGPYCY.FY15, AGPYCY.FY16,([FY16]-[FY15]) * IIF([Grouping Category]  = "Revenue B",-1,1) AS [YoY Var]

  5. #5
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Got it! thanks Saanfu for all the help and the advice on the naming convention. IIF is exactly what I was looking for.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great... glad it worked for you....

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

Similar Threads

  1. Replies: 11
    Last Post: 04-04-2015, 08:53 AM
  2. Code: Group and select first records
    By cfobare79 in forum Access
    Replies: 9
    Last Post: 02-10-2015, 04:08 PM
  3. Code to create multiple records from one form
    By g4tv4life in forum Programming
    Replies: 18
    Last Post: 02-06-2014, 11:25 AM
  4. Code to add multiple records
    By rachello89 in forum Programming
    Replies: 1
    Last Post: 06-25-2012, 10:04 PM
  5. Replies: 13
    Last Post: 01-10-2012, 09:56 AM

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