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