Query Design/Layout

  • Thread starter Thread starter Mathew
  • Start date Start date
M

Mathew

Hello,

I have inhereted a database with a structure similar to
what is shown below ... PA,DV,PL,OL are simple audit
results (yes the item was there - no it was not there -
n/a - and blank for those too dumb to answer ;-) ...

rev_dt area PA DV PL OL
11/4/2003 Bowdoin Yes Yes NO N/A
11/4/2003 Kirstein Yes Yes No Yes
11/4/2003 Kirstein Yes Yes Yes
11/4/2003 Kirstein No Yes Yes Yes
11/4/2003 Bowdoin Yes No Yes N/A
11/4/2003 Kirstein Yes N/A Yes Yes
11/4/2003 Kirstein Yes No Yes Yes

I would to limit the results to a date span and specific
area (which i think i can accomplish easily) but then want
to breakout the results of the audit in the following
layout:

(Date is 11/4/2003 / Area Kirstein)
Yes NO N/A Blank
PA 4 1 0 0
DV 3 1 1 0
PL 3 1 0 1
OL 5 0 0 0

Hopefully the layout came out alright with the web
posting ... any help with this would be appreciated ... i
can send you a sample data set if needed as well

Thanks in Advance
Mathew
 
Hi,


The field name is NOT data... at least not data that any database engine can
handle as data. So, we have to make it as data, and on solution is to use
UNION query:

SELECT rev_dt, area, "PA" as type, Nz(PA, "") As z FROM myTable
UNION ALL
SELECT rev_dt, area, "DV", Nz(DV, "") FROM myTable
UNION ALL
SELECT rev_dt, area, "PL", Nz(PL, "") FROM myTable
UNION ALL
SELECT rev_dt, area, "OL", Nz(OL, "") FROM myTable


save it as a query, say, for illustration, QU

Then, make a crosstab on QU (well, filter it before, accordingly to the date
and area of interest), Grouping on Type, PIVOT on z, aggregate with COUNT.

Note that your parameters (use to filter) data type must be defined in the
crosstab (from the User interface, check the menu, under Query)


I imagine you really have blank, not NULL. To be safe, I added Nz( PA, "" )
in the union query ("" is two double quotes).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top