Query using same field multiple times under varying criteria? (1 Viewer)

[moderatelyclueless]

Preamble edit: I'm running Access 2003.

I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate

I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).

I'm wondering if there's an elegant solution to this that I am totally missing. Here's something I'm currently considering, but it seems so messy that I'm sure there's a better way to do it.

-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days

If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.

That seems very inefficient, help? Making 10 queries separately and then manually merging them seems redundant. Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?

 

[Galaxiom]

You need a CrossTab query.

 

[moderatelyclueless]

Aha! Crosstab query. Man, that sure is useful. Thanks

 

[moderatelyclueless]

You need a CrossTab query.

Hm, actually upon looking further into it, I'm not sure this is working for me. Crosstab queries seem to be read-only without some elaborate and not very elegant looking tricks, am I right?

Unfortunately, my messy solution hit the same wall. Individual query tables for each day are editable, but when I combine all 10 query tables together, the resulting table isn't editable for some reason I don't understand.

Is there an editable version of crosstabs?