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!!!!