Crosstab represent a value in middle - HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, I have done this in Access 97 but for some reason my query no longer
works. I have a standard query (basic) that has the following elements.

cpva, feature, option, item

My end result should be this:

feature1 feature2 feature3
cpva item A 9987 Green

The fields are numeric, text, text,text - respectively.
The middle part represented as (A, 9987, Green) are the options, and the
features go across the top. CPVA is like the sheet of paper that has a lot
of questions (features) with answers (options). I would like to see this
brokent out into Excel but first I need the crosstab to work. Right now I
only get a value of 1 or zero in the middle part - I don't know how to make
the value appear.
 
Assuming that the combination of CPVA and Item is unique (that is no more
than one record with the same combination of the two fields would be
selected by your query).

SQL would probably look something like:

TRANSFORM First(Option) as x
SELECT CPVA, Item
FROM YourTable
GROUP BY CPVA, Item
PIVOT Feature

In the crosstab query grid (Feid - Total / Crosstab setting)
CPVA - Group by / Row Heading
Item - Group by / Row Heading
Feature - Group by / Column heading
Option - First / Value (Total could be Min, Max, First, or Last - First
would be most efficient)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Your a life saver - thank you so much! It works like my old one now and all
is right - smile. Thanks again.
 
Back
Top