Creating automatic scheduling matrix - crosstab?

  • Thread starter Thread starter Darleen
  • Start date Start date
D

Darleen

I am seeking conceptual here on how to get started with a "3D Matrix"
in Access. We run a training center which holds multiple classes in
multiple cities at multiple times. So I need to create a matrix with
3 "axis". The 3 axes are:

City Location
Week of Class (in 14 week increments that repeat)
Name of Class

The end goal is to be able to have a form that shows these classes in
a grid, and the user can double click on the grid and open up a dialog
box that will allow them to add registrants, instructors, etc...
Before I delve too far into this, I want to be sure I'm going down the
right path. My ideas so far are to do it with a crosstab query, or to
set up a "base schedule" table from which mathematical calculations in
queries repeat scheduling patterns, or to do the whole thing with
formulas, etc... I'm actually pretty lost as I've never worked with
crosstab queries before and don't know if they are they right way to
go, or if the whole thing can be done with VBA coding like a lot of
calendars I've seen in Access.

Here is a link to where you can see what the structure and patterns
are like:

www.kimbrelldesign.com/Matrix2.pdf

Just looking for some general principles here on how to handle the
patterns and form design. Thanks for any much needed help!!!!
 
Hi Darleen,

The screen and reports are both only two-dimensional so there is no way to
display or print anything in three dimensions (maybe some day!). So for now go
with displaying multiple classes at multiple times in two dimensions and choose
which city you want to display. The crosstab query is the way to go. Find a good
reference on how to build dynamic column headings.

Your tables need to look like:

TblCity
CityID
NameOfCity

TblClass
ClassID
ClassName

TblClassSchedule
ClassScheduleID
CityID
ClassID
ClassStartDate
ClassEndDate
ClassStartTime
ClassEndTime

TblClassSchedule assumes classes on multiple days start and end each day at the
same time. If that's not the case, you need another table for Class times that
looks like:

TblClassTimes
ClassTimeID
ClassScheduleID
ClassStartTime
ClassEndTime
 
Hi Darleen, (I've always wanted to know a 'Darleen'!)

A long reply, but keep reading, as it gets better as it goes on!

Crosstab queries can be a pain for doing anything other than displaying
data, but do by all means look into it.

I've fixed a similar problem in two ways before now: 1. Use VB so you can
create control arrays (how I wish Access could) on a form, with some wrapper
code to handle setting the data etc, then compile into a dll, register using
regsvr32.exe (or a decent installer), reference from your Access app, then
call it like any other object, or 2. nifty use of a whole set of subforms
based on SQL written in code at runtime

Hmmm. Both of those look a lot harder on paper than they were to actually
do, but suffice it to say that I ended up doing both after crawling up the
wall, across the ceiling, and down the other side chasing a solution using
crosstabs!

(Pause for 10 minutes research...)

OK, I just tried something dead sneaky. Create a form for your top level
object - in my test I used Northwind's Customers table. Create a crosstab
query (I based mine on NW's Orders & OrderDetails) that includes the top
level objects primary key in a WHERE clause - give it any old spurious value
to work with for now. Use the crosstab query as the SourceObject for a
subform on the main form. In the form's Current event handler write some
code like the following
'~~~~~~
Dim qdf As DAO.QueryDef
Dim strSubformSource As String

Set qdf = CurrentDb.QueryDefs("qxtbOrder Details_Crosstab")

qdf.SQL = "TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity " _
& "SELECT [Order Details].OrderID, Sum([Order Details].Quantity) AS
[Total Of Quantity] " _
& "FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID " _
& "WHERE(Orders.CustomerID) = '" & Me.CustomerID & "' " _
& "GROUP BY [Order Details].OrderID " _
& "PIVOT [Order Details].ProductID;"
qdf.Close

strSubformSource = Me.subCrosstab.SourceObject
Me.subCrosstab.SourceObject = ""
Me.subCrosstab.Requery
Me.subCrosstab.SourceObject = strSubformSource

Me.subCrosstab.Form.Requery

'Clean up
Set qdf = Nothing
'~~~~~~~~~~

I nicked the SQL from the Crosstab query I created before in SQL view, and
changed the WHERE clause to use the main forms current primary key value
(... ='" & "Me.CustomerID" & "' "). So you change the SQL on the fly. Now
get a reference to the querydef for the crosstab query, then update its SQL,
now completely refresh the subform by dropping its SourceObject and putting
it back again so the columns get recreated (that's the really tricky bit!),
then you're in business.

Brilliant! I wish I'd thought of it before. Well, thanks for that. I
should come to you for more help....

er....

I'm off for supper. I hope this has helped you out.

Good luck,

Andrew
 
Hi Darleen,

A bit more on my last post (see it to make sense of this)

In the form's Current event, change the code I wrote before to read (blue for emphasis):
'~~~~~~~~~~~~
Dim qdf As DAO.QueryDef
Dim intCounter As Integer
Const SOURCE_OBJECT As String = "Query.Order Details_Crosstab" 'Change to suit your query name

Set qdf = CurrentDb.QueryDefs(SOURCE_OBJECT )

qdf.SQL = "TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity " _
& "SELECT [Order Details].OrderID, Sum([Order Details].Quantity) AS [Total Of Quantity] " _
& "FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID " _
& "WHERE(Orders.CustomerID) = '" & Me.CustomerID & "' " _
& "GROUP BY [Order Details].OrderID " _
& "PIVOT [Order Details].ProductID"

With Me.subCrosstab
.SourceObject = SOURCE_OBJECT
.Form.Requery
For intCounter = 0 To .Form.Controls.Count - 1
.Form.Controls(intCounter).ColumnWidth = -2
Next intCounter
End With
'~~~~~~~

and add a command button to close the form....

'~~~~~~~
Private Sub cmdClose_Click()

subCrosstab.SourceObject = ""
DoCmd.Close

End Sub
'~~~~~~~

There are two enhancements over the previous code: 1. The subform columns resize themselves to 'best-fit', and 2. You don't get a message box asking if you want to change the layout of the crosstab query.

If you want the data to be updateable, well tough! You'll have to come up with something else. But I'm stoked about working this out, it's something I've been thinking about for probably two years before seeing a simple solution like this.

All the best,

Andrew
 
Back
Top