data type mismatch after log transform

F

fishcakes

I have some data (in a table called [recruitment data]) that is collected in
different years (field [year]) and at different sites (field [site_id]) which
consists of counts for a particular fish species (field [stpa]). I am trying
to write a simple query (1) that will log (x+1) my count data [stpa] and then
use this query to write a second crosstab query (2) were [year] is the row
heading, [site_id] is the column heading and the value is the average of the
log transformed recruitment data [Lgstpa].

Query 1
SELECT [recruitment data].year, [recruitment data].site_id, Log([stpa]+1) AS
Lgstpa
FROM [recruitment];

Query 2
TRANSFORM Avg([Query 1].Lgstpa) AS AvgOfLgstpa
SELECT [Query 1].year
FROM [Query 1]
GROUP BY [Query 1].year
PIVOT [Query 1].site_id;

The first query works fine (I can see the log values in the table and it all
makes sense, with no error message), but the second query returns an error
saying that there is a data type mismatch in criteria expression and appears
to be related to trying to calculate the average of the log transformed data.

Does anyone know how to fix this?
Thanks in advance J
 
Top