HELP: Cross-Tabs, Stored Procedures and other Wonders of the World

[xFiver]

Hey gang,

I'm having a tough time wrapping my head around this problem. More than
anything, I'm looking for the "why" of why this isn't working.

THE SETUP:

I have a Cross-Tab query (we'll call qCrossTab) that details and sums
up the number of absences from class between a particular time frame
(for testing, I've hard-coded it into the SQL)

WHERE (((URKIDS_ATTENDANCE.CLASS_DATE) >= #8/1/1880# And
(URKIDS_ATTENDANCE.CLASS_DATE) <= #8/2/1880#))

Since the number of columns can vary (or will vary once I've added the
ability for the user to select date parameters), I've created a report
(rptAttendance) that does some formatting based on the number of
columns. The RECORDSOURCE of rptAttendance is set to qCrossTab

With this setup, everything works like a charm.

THE PROBLEM AND MYSTERY:

I am building a form that will eventually ask the user to select the
FROM and TO dates to embed into the WHERE statement above. For right
now, the form simply has one button (GENERATE REPORT) that attempts to
launch rptAttendance while passing it a STRING value (strSQL). NOTE:
strSQL below is an exact copy of the SQL from qAttendance.

Code for cbGenerateReport
================================================== =======
Private Sub cbGenerateReport_Click()
Dim strSQL As String

' builds the SQL one phrase at a time
strSQL = "TRANSFORM Sum(URKIDS_ATTENDANCE.ABSENT) AS SumOfABSENT "
strSQL = strSQL & "SELECT URKIDS_ATTENDANCE.URKIDSID,
URKIDS_ATTENDANCE.GROUPID, Abs(Sum(URKIDS_ATTENDANCE.ABSENT)) AS [Total
of ABSENT] "
strSQL = strSQL & "FROM URKIDS_GROUP RIGHT JOIN URKIDS_ATTENDANCE ON
URKIDS_GROUP.ID = URKIDS_ATTENDANCE.GROUPID "
strSQL = strSQL & "WHERE (((URKIDS_ATTENDANCE.CLASS_DATE) >= #8/1/1880#
And (URKIDS_ATTENDANCE.CLASS_DATE) <= #8/2/1880#)) "
strSQL = strSQL & "GROUP BY URKIDS_ATTENDANCE.URKIDSID,
URKIDS_ATTENDANCE.GROUPID, URKIDS_GROUP.SEASON "
strSQL = strSQL & "ORDER BY URKIDS_GROUP.SEASON "
strSQL = strSQL & "PIVOT URKIDS_ATTENDANCE.CLASS_DATE"

DoCmd.OpenReport "rptURKids-Attendance-Crosstab", acViewPreview, , , ,
strSQL

End Sub
================================================== =========
What's odd, is that when I run the report from this code (which,
technically, should have the exact same control source as just running
the report on it's own), I get the following error:

Run-time error '3637':
Cannot use the crosstab of a non-fixed column as a subquery

>From my research, it sounds as though I need to use STORED PROCEDURES

with bound columnheadings in order for a report to change it's control,
which doesn't make sense since I don't see the difference between the
two directions above...

OTHER POSSIBLE SOLUTIONS

Since I don't really get SP at the moment, I was trying to figure out a
way to get this to work.

I created a query that simply SELECT * from qCrossTab The problem
with this is that there's no way to specify the date parameters because
this is part of qCrossTab.

So, I thought, what if I could create an SQL statement and save it to
qCrossTab, overwriting what's already there. Then, I could run my
report with the proper date parameters. Question is, though, how can
I, in VB, save a query? I'm sure this isn't the best way to structure
a database, but it seems like it could be easier than fumbling around
with stored procedures.
Phew. That's all. Thanks for sticking this out. Thoughts? Suggestions?

Thanks,

Mike

[Rich P]

Hi Mike,

Think of a crosstab query this way. If your database is properly
normalized (no redundant data colums) then a crosstab query takes say 10
rows of data that have 3 colums (the way the computer reads - top/down)
and transforms them to horizontal - now 3 rows and 10 columns - the way
humans read it (well western humans). If you have 11 rows, you now have
11 columns after the transformation. In other words, the resultset is
not static. Subqueries have to return a somewhat static resultset. You
can have variable rows, but only fixed columns - because the computer
reads the data top/down. The fix is either to only return the same
number of columns each time, or to not use a crosstab query as a
subquery.

Without doing any deeper thinking, I would suggest to resort to good ol
fashion spaghetti code (DAO) for the fix. However, I recently had an
unsavory encounter in this NG by prescribing such a fix. So, I hope I
have identified your problem, but you are on your own for the fix.

Regards,
Rich

*** Sent via Developersdex http://www.developersdex.com ***