473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating automatic scheduling matrix - crosstab?

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!!!!
Nov 12 '05 #1
3 3177
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

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Darleen" <da*******@sbcglobal.net> wrote in message
news:4f**************************@posting.google.c om...
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!!!!

Nov 12 '05 #2
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

"Darleen" <da*******@sbcglobal.net> wrote in message
news:4f**************************@posting.google.c om...
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!!!!

Nov 12 '05 #3
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
"Darleen" <da*******@sbcglobal.net> wrote in message news:4f**************************@posting.google.c om...
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!!!!

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Ivan Voras | last post by:
Is there a nice(r) way of creating a list of uniform values? I'm currently using: list('0'*N), which makes a string and then chops it up into a list. I need it to create a NxN matrix: matrix = ...
4
by: DebbieD | last post by:
Greetings, This seems a very complicated task, so I welcome any input. My boss wants a data grid or matrix of the top 6 orders with ordered items, and products, for a particular customer and...
3
by: Tony Johansson | last post by:
Hello Experts!! I have two small classes called Intvektor and Matris shown below and a main. Class Intvektor will create a one dimension array of integer by allocate memory dynamically as you...
17
by: Bushido Hacks | last post by:
I've come up with a good set of Matrix codes recently when I cam to a road block. I would like to know if I should destroy an object to create a new object with new dimmensions. class Matrix{...
4
by: Nabil | last post by:
I have to create a report with drill down and I can only think of crosstab report to achieve this task. However 2 things I am lacking, first is I dont know how to enable drill-down on cross tab...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
2
by: Guy_Philly | last post by:
I am trying to add multiple subforms (or subtables?) on an existing form. The underlying table is a very detailed descriptive table for art in a collection. I am already using one subtable to...
4
by: tshad | last post by:
I am trying to set up an Image authorization where you type in the value that is in a picture to log on to our site. I found a program that is supposed to do it, but it doesn't seem to work. ...
0
by: =?Utf-8?B?TmFzc2VyIEJ1bmR1a2E=?= | last post by:
Hello, Configuring incremental NT Backup in windows XP Professional From start menu go to RUN. ntbackup.exe Advance Mode Backup Wizard (Advanced Mode) Next and specify backup Items Locate...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.