1

Access 2010 here. I have a query (Thank you Andomar!):

SELECT Inspection.Date, count(*) AS [# Insp], sum(iif(Disposition = 'PASS',1,0)) AS [# Passed], sum(iif(Disposition = 'FAIL',1,0)) AS [# Failed], sum(iif(Disposition = 'PASS',1,0)) /  count(*) AS [% Acceptance]
FROM Inspection
WHERE Disposition in ('PASS', 'FAIL') AND ((Inspection.Date) Between Date() And Date()-30)
GROUP BY Date;

That gives a table like this:

Date       | # Insp | # Passed | # Failed | % Acceptance
11/26/2012 | 7      | 5        | 2        | 71 
11/27/2012 | 8      | 4        | 4        | 50 
...

I am looking to use this query to make a "table" for a sub-form that will be below a graph, for reference only. The formatting of the "table" is of importance, as it needs both Column (Date) and Row headings. I have table in parentheis to emphasize that the table is generated in real time; in other words, not stored as an Access object.

The end result will be someting like this:

Date         | 11/26/2012 | 11/27/2012 ...
# Insp       | 7          | 8
# Passed     | 5          | 4
# Failed     | 2          | 4
% Acceptance | 71         | 50

It seems to be an optimal case, as the axis are just flipped, but for the life of me, I cannot find a solution that does not destroy the data. A Crosstab Query only gave me filtering on one or more categories against a single value. Is this something a union would be used for; or a pivot? Would a transform be needed? It seems like it should be such a simple problem. Is this something that can be done in SQL or would VBA be needed to arrange the "table?" Thanks for the help!

These links do seem applicable:

Columns to Rows in MS Access

how to pivot rows to columns

Community
  • 1
  • 1
Phizon
  • 93
  • 4
  • 14

1 Answers1

1

This will have to be a two-step process to transform. First you will have to rotate the data in your current query to be in rows instead of columns, then you will have to transform the dates into columns instead of rows.

The query will be something like this:

TRANSFORM max(val) as MaxValue
SELECT col
FROM
(
  SELECT [Date], '# Insp' as Col, [# Insp] as  val 
  FROM yourQuery
  UNION ALL
  SELECT [Date], '# Passed' as Col, [# Passed] as val
  FROM yourQuery
  UNION ALL
  SELECT [Date], '# Failed' as Col, [# Failed] as val
  FROM yourQuery
  UNION ALL
  SELECT [Date], '% Acceptance' as Col, [% Acceptance] as val
  FROM yourQuery
) 
GROUP BY col
PIVOT [Date]

I am guessing the your current query is saved in your database, you will replace the yourQuery in my example with the name of your query.

I just tested this in MS Access 2003 with the values in your sample above and it produced the result you want.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Absolutely perfect! The only nagging thing about it is that the first column's heading is "col." This seems to be set in your code, but changing any instances of "col" change the column contents. Would this require a further assignment after the transform and pivot? Thank you! Thank you! – Phizon Jan 04 '13 at 21:20
  • @Phizon that is just the alias of the value for the `UNION ALL`, you can call it whatever you want. That column of data is going to be the first column in your final result, it is just the string name of the original column. – Taryn Jan 04 '13 at 21:22
  • Dumb me, I just altered it in the Sub-form and "col" is now "Date" with no repercussions. Thanks again! – Phizon Jan 04 '13 at 21:24
  • Yep, I see that now. Everything works as expected! Thanks again bluefeet! Have a great weekend! – Phizon Jan 04 '13 at 21:30