Dear Julie:
Is it possible? Yes, but it won't be a crosstab technically.
SELECT StudentName AS RowHeading,
(SELECT DateSubmitted FROM YourSource S1
WHERE S1.StudentName = S.StudentName
AND S1.Unit = 'U1Value'
AND S1.Assignment = A1Value) AS U1A1Date,
(SELECT Mark FROM YourSource S1
WHERE S1.StudentName = S.StudentName
AND S1.Unit = U1Value
AND S1.Assignment = A1Value) AS U1A1Mark
FROM YourSource S
ORDER BY StudentName
The above is just a sample. The two subqueries (lines 2-5 and 6-9)
need to be repeated for each combination of ColumnHeadings you have.
I have called the Unit/Assignment combinations U1A1 for the first
instance. You may want to name them after the actual values.
Now there are many additional things to consider. If the Unit and
Assignment values for the column headings are not known in advance,
then the query will probably have to be written dynamically. You
would typically open a recordset of all the distinct Unit/Assignment
combinations to be shown and step through those records, writing SQL
like the above for each value.
A significant consideration is how wide the data may be. Before you
ever reach the 255 column limit for a query you will reach a limit of
what can be displayed on the screen or on paper. You may need to
segment the reporting or have a horizontal scroll bar to repeat the
query for a reasonable number of columns at a time to make it
manageable.
If you're doing this for a form, there are even techniques to make it
updatable so you can actually post values in the two dimensional
fashion.
Most of this is well up into the expert level of programming. Even
then it takes a rather large amount of programming to achieve, perhaps
a couple of days for a moderate application.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
