crosstab on top of scalar query
Jeff
I think I've annoyed Jet as much as possible, and may need to resort
to VBA to write my crosstab. So I'm sure others have tackled this
before, once again looking for a little direction before embarking in
the forest.
I have a table of well info, "tblWell"; table of water levels
(depths), "tblGroundwaterLevel"; and a table containing historical
surveys for the well elevation (top), "tblWellMaintenance". This well
"top" is used to calculated water level elevations, and may change
through time (new surveys are taken, and, for a variety of reasons,
the "top" value changes annually).
So we have a one-to-many relationship between "tblWell" and
"tblWellMaintenance", on which I placed a scalar query to only pick
out the most recent survey "top" for each well. Then I use this as a
wrapper to make a calculation with the water depths, giving me an
elevation.
Next I'd like to generate a crosstab of these results, with each date
containing one or more records, and the well names as fields (column
headers). But Jet doesn't like using the scalar subquery in a
crosstab.
Hence the sticky part -- or am I misreading the error?
I recall a VBA script someone used to build a crosstab query on a SQL
Server cursor, but I'd like to first 1) get a bit of affirmation that
this is the best next step, and 2) see an example or two since I don't
have the O'Reilly books available (I'm technically on vacation). :-)
Regards,
Jeff