Dear Mary:
A crosstab is a query method unique to Access. It is a shortcut to
performing something that can be done without the features that were
added to Access to do that.
These features (basically what is found in the TRANSFORM keyword) make
it easy to perform simple crosstab functionality, but do not permit
the full range of what is possible to do using the SQL language
without the TRANSFORM clause. Your request may well fall into the
category of what is possible, but not using the simpler crosstab
facility.
Implementing for just one range:
SELECT RowIdentity,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.RowIdentity = T.RowIdentity
AND TestScore BETWEEN 1400 AND 1449)
AS CountInRange1400
FROM YourTable T
ORDER BY RowIdentity
In the above, RowIdentity means one or more columns that identify each
row on the report, unually the left-most columns (possibly student
name, for example). RangeFilter means the column with the values that
may be in the range 1400-1449 (I changed it from 1400-1450 because the
next range will likely start from 1450, right?).
If you provide me with column and table names for the above, I'll try
to adapt this closer to what you need.
The subquery in lines 2-5 above can be repeated for as many ranges of
test scores as desired. If these ranges are not fixed, it may be
better to put them into a table. Then the SQL would have to be
written in code (VBA) to create it from the values in the table.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
