Results 1 to 6 of 6
  1. #1
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    620

    Need a Query which rotates/transposes table output in order to accurately calculate % values

    Experts:

    I need some assistance with developing/modifying a query. Please allow me to provide some background (see attached sample database).

    Background:
    1. Attached DB contains a single table [tbl_SourceData]... it includes only 6 sample records.
    2. Also, the DB includes 3 queries:
    2a. qry01_SourceData is a simple data pull of the table
    2b. qry02_Calculations_Input performs calculations based on qry01_SourceData
    2c. qry03_Calculations_Output summarizes the calculations (qry02) without the "helper columns/fields"

    Process/calculation for "qry02_Calculations"
    - In 1st expression, I am deriving the "total" for each of the 6 categories across the 6 cities. For instance, the calculated value for "Total_Cat1" = 29 (based on: Unknown = 0; Boston = 5; Denver = 12; Hartford = 4; Richond = 7; Sacramento = 1)
    - In 2nd expression, I am taking the value of, e.g., "5" (Boston | Category 1) and divide it by "29". This gives me the correct percentage of 17.2%. The same principle is applied for each city and category.
    - Again, qry03_Calculations_Output simply displays the "Percent_Cat#" for each value intersection.

    Note: I have included an Excel spreadsheet ("ExcelCalculations.xlsx") which validates the calculation in my query. Please see calculations in columns A:G.

    Now, here's what I need some help with in Access:
    =================================================

    - First though, let's look back at the Excel spreadsheet.
    - Specifically, columns I:P illustrate the different process for calculations (i.e., deriving the 'total' across rows vs. columns).
    - Based for the example of **Boston / Category1**, I am now dividing "5" by "85" (vs. previously "29"). Thus, my percentage changes from 17.2% to actually 5.9% (see cell J16).
    - Next, given that Access (well, my current query) sums up the fields (aka columns), I presume I could use the same query process; however, it would required the input query to be **rotated** by 90 degrees (see cell I27:O33).

    My question:
    a. Is there another method available (in MS-Access) which would allow me to calculate one record at a time (across the 6 categories). This would mimic the same calculation in Excel where I sum up across rows.
    b. If the latter is not feasible OR not efficient, is there a mechanism where I can rotate/transpose the query output (qry01_SourceData) and thus come up with the same (desired) percentages?

    Thank you in advance!!!

    Cheers,
    Tom

    P.S. I have attached (.zip) both the spreadsheet and database
    Attached Thumbnails Attached Thumbnails ExcelCalculations.JPG  
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,799
    the query wizard always makes the SUM TOTAL field like:
    the field in a query view:


    Total of Field: [field]
    [query/table source]
    sum
    row heading

  3. #3
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    620
    ranman256:

    I'm not entirely understanding your recommendation. Could you please post the query or SQL for the transformation?

    Thank you.

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Hi Tom,

    Is late for me but I'll try to help...

    At first, have to normalize the source table via this union query (name it qryNormData):
    Code:
    SELECT City, "Category1" AS CatName, Category1 AS Val FROM tbl_SourceData UNION ALL 
    SELECT City, "Category2" AS CatName, Category2 AS Val FROM tbl_SourceData UNION ALL
    SELECT City, "Category3" AS CatName, Category3 AS Val FROM tbl_SourceData UNION ALL
    SELECT City, "Category4" AS CatName, Category4 AS Val FROM tbl_SourceData UNION ALL
    SELECT City, "Category5" AS CatName, Category5 AS Val FROM tbl_SourceData UNION ALL 
    SELECT City, "Category6" AS CatName, Category6 AS Val FROM tbl_SourceData;
    Then, you can create a cross tab query with the code below and work with it:
    Code:
    TRANSFORM Min(qryNormData.Val) AS MinOfVal
    SELECT qryNormData.CatName
    FROM qryNormData
    GROUP BY qryNormData.CatName
    PIVOT qryNormData.City;
    I hope it helps,
    John


    PS.: I always enjoy your original posts.

  5. #5
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    620
    John -- your solution -- AS ALWAYS -- is simply perfect and absolutely elegant.

    I thank you for your assistance (and also for your nice P.S. note).

    Stay safe and all the best,
    Tom

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Tom,
    You are ―always― welcome! Happy to help.
    Also, thanks for the star.

    Best wishes to you too,
    John

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

Similar Threads

  1. Calculated field in query or report output
    By Keefay78 in forum Queries
    Replies: 2
    Last Post: 05-28-2019, 03:08 PM
  2. Replies: 3
    Last Post: 02-24-2019, 02:35 PM
  3. Calculated Percentage showing as 0%0%
    By wtucker in forum Forms
    Replies: 3
    Last Post: 08-04-2017, 03:23 PM
  4. Replies: 2
    Last Post: 10-15-2013, 02:14 PM
  5. Replies: 1
    Last Post: 04-21-2013, 03:20 PM

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