Olly,
What are the numbers in the matrix? Client_ID's? Anyway, yes, it is
possible. What you need is a crosstab query. Start by making a simple select
query with all the required fields (Instr_ID, Client_ID, BookingDate,
DayPart) and then go Quriy > Crosstab to change it to a crosstab query. In
the Crosstab line in the grid, set BookingDate and DayPart to Row Heading,
Instr_ID to Column Heading and Client_ID to Value. The Total setting should
be Group By for all but the Value column, which should be set to
First/Last/Min/Max/Sum depending on what you want (if there's only one row
for each day/part/instructor combination in the original table, like it
should be, then it doesn't make any difference which one you use).
The tricky part is to force it to display all possible dates/dayparts in a
range. As it is, if no record exists for a date/part it will not be
displayed at all. One way around it that I can think of is to have a
separate table with all the dates/parts in the period in question and
combine that with the bookings table in the query with a right join so that
it returns all records from the auxiliary table, and the matching records
from the bookings table. If your application involves a form on which the
user selects a date range to display, then you could use some simple VB code
behind the "Show Bookings" button to clear and populate the auxiliary table
for the required date range.
Assuming the table is called tblAux, with fields fDate and fPart (make the
table manually), the form is called MyForm, the two text boxes for the date
range are called txtFrom and txtTo, and the command button is called
Command0, your code would look something like:
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
strSQL = "DELETE * FROM tblAux"
Set db = CurrentDb
db.Execute strSQL
dfm = Forms![MyForm]![txtFrom]
dto = Forms![MyForm]![txtTo]
Set rst = db.OpenRecordset("tblAux")
vdat = dfm
Do Until vdat > dto
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "AM"
rst.Update
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "PM"
rst.Update
vdat = vdat + 1
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
DoCmd.OpenQuery "QueryName", acViewNormal
End Sub
Just change the names to the actual ones and you're there.
HTH,
Nikos