Unique Values vanish when query is turned into a crosstab

C

cscholz

I wrote an SQL statement that allows me to take a standard select quer
that lists the occurrences of "B" for each event "A" and eliminates an
duplicate values. For the first two A events, the following matche
exist:

A1B1
A1B2
A1B2
A1B3
A2B1
A2B1
A2B3

Of course, the unique values causes the query to ignore the duplicat
records and the query looks like this:

A1B1
A1B2
A1B3
A2B1
A2B3

Great! But if I crosstab it, I get:

B1 B2 B3
A1 1 2 1
A2 2 0 1

I want it to read:

B1 B2 B3
A1 1 1 1
A2 1 0 1

Why don't the unique values/record properties work in Crosstabs? I'
trying to implement this all in VBA code--is there a way to use
recordset object as the source for an SQL statement? I know I can d
this if I first save my select statement as a query and then base th
crosstab off that query...can this be done in code?

Thanks,
Christia
 
M

Michel Walsh

Hi,

Have you tried, instead of:

TRANSFORM COUNT(whatever) AS theValue SELECT ...

to use something like:

TRANSFORM iif( 0=COUNT(whatever), 0, 1) As theValue SELECT ...


Hoping it may help,
Vanderghast, Access MVP
 
Top