CrossTab Query with Parameter

[tizmagik]

I am having a lot of difficulty generating a CrossTab Query based
report. I have looked online for several tutorials and whatnot but I
have not been able to really find what I'm looking for, nor have I been
able to adapt other people's solutions/tips to fit what I need. If
anyone could please help me with the following it would be really
appreciated, thank you!

I need to generate a Report (say: repCrossTab) that grabs it's data
from the CrossTab Query (say: xTabFinal) which utilizes a previous
Query (say: xTabSum) that Joins two other Queries (say: xTab1 and
xTab2). xTab1 contains two parameters that will be filled out by the
user on a form (say: [Forms]![repExecSum]![cboDept] and
[Forms]![repExecSum]![cboRelease])

The following is working SQL code to generate each query, the only
problem is with xTabFinal. Specific errors with that after the code:

xTab1 =
SELECT Initiatives.InitiativeNumber, Initiatives.InitiativeName,
Initiatives.PDD, Initiatives.Scope, Initiatives.InitiativeUniqueNumber,
InitiativeImpactedDepts.DeptNumber, Initiatives.ReleaseNumber
FROM InitiativeImpactedDepts INNER JOIN Initiatives ON
InitiativeImpactedDepts.InitiativeUniqueNumber =
Initiatives.InitiativeUniqueNumber
WHERE
(((InitiativeImpactedDepts.DeptNumber)=[Forms]![repExecSum]![cboDept])
AND ((Initiatives.ReleaseNumber)=[Forms]![repExecSum]![cboRelease]) AND
((Initiatives.DroppedStatus)=0) AND
((Initiatives.NotSupportedStatus)=0));

xTab2 =
SELECT PhaseActivity.PhaseActivity, App, Impacted, Description,
InitiativeUniqueNumber
FROM ImpactApp INNER JOIN PhaseActivity ON
PhaseActivity.PhaseActivity=ImpactApp.PhaseActivit y;

xTabSum =
SELECT InitiativeNumber, InitiativeName, PDD, Scope, App, Impacted,
Description, DeptNumber, ReleaseNumber
FROM xTab1 INNER JOIN xTab2 ON
xTab1.InitiativeUniqueNumber=xTab2.InitiativeUniqu eNumber;

xTabFinal =
TRANSFORM Count(xTabSum.Impacted) AS Expr1
SELECT xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName,
Count(xTabSum.Impacted) AS [Total Impacts]
FROM xTabSum
GROUP BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
ORDER BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
PIVOT xTabSum.App;

The error occrus when running the xTabFinal Query (which is the
CrossTab Query). I get the following error: "The Microsoft Jet database
engine does not recognize 'Forms!repExecSum!cboDept' as a valid field
name or expression". Of cours, I assume that is an expected result
because CrossTab Queries (from what I know so far) require that all
information be present (read: hard-coded) in order for results to be
returned successfully.

Basically, I'm wondering if there is any way around this? I don't mind
using VBA Code if need be, but please try to provide me with as much
VBA Code as possible as I'm completely new to using SQL Queries and
Reports (especially CrossTab Queries) in VBA code. But I am extremely
comfortable with the Visual Basic language and other functions dealing
with Events/Forms/Controls etc in VBA (including within Microsoft
Access).

I'm sorry for the ridiculously long question, but I really don't know
where else I can find informatino or help about this particular
situation. Honestly, I don't even expect anyone to be able to help me
with the extent that I need, but it makes me feel a little bit better
to at least ask. :)

Thanks and God Bless,
-Jeremy

Of course, if you need any further information or clarification, please
let me know!

[HS Hartkamp]

Two possibilities (looking at it without seeing the app.):
- The parameters are not recognised as parameter, because in xTab1 I do not
see the PARAMETER keyword. If this is copy-pasted from your application,
then it seems to be missing. (Solve this in the query design, and choose
Query-Parameters from the menu. In the little box, type
[Forms]![repExecSum]![cboDept] in the left column, and the correct datatype
in the right column)

- In the cross-tab report, the parameter is not filled properly in the
'underlying query'. You'd have to look through the code of the report to
solve this (read it, understand it, and then you know how and what).
Possibly, this second one is caused by the first.

If this is not the case, I'd have to see the application to say something
meaningful about it.

As I allways tell my customers about computers, "Anything is possible, It's
just a matter of how much time (=money) you'd want to put in"

Good luck,

Bas Hartkamp.
<ti******@gmail.com> schreef in bericht
news:11**********************@j33g2000cwa.googlegr oups.com...

I am having a lot of difficulty generating a CrossTab Query based
report. I have looked online for several tutorials and whatnot but I
have not been able to really find what I'm looking for, nor have I been
able to adapt other people's solutions/tips to fit what I need. If
anyone could please help me with the following it would be really
appreciated, thank you!

I need to generate a Report (say: repCrossTab) that grabs it's data
from the CrossTab Query (say: xTabFinal) which utilizes a previous
Query (say: xTabSum) that Joins two other Queries (say: xTab1 and
xTab2). xTab1 contains two parameters that will be filled out by the
user on a form (say: [Forms]![repExecSum]![cboDept] and
[Forms]![repExecSum]![cboRelease])

The following is working SQL code to generate each query, the only
problem is with xTabFinal. Specific errors with that after the code:

xTab1 =
SELECT Initiatives.InitiativeNumber, Initiatives.InitiativeName,
Initiatives.PDD, Initiatives.Scope, Initiatives.InitiativeUniqueNumber,
InitiativeImpactedDepts.DeptNumber, Initiatives.ReleaseNumber
FROM InitiativeImpactedDepts INNER JOIN Initiatives ON
InitiativeImpactedDepts.InitiativeUniqueNumber =
Initiatives.InitiativeUniqueNumber
WHERE
(((InitiativeImpactedDepts.DeptNumber)=[Forms]![repExecSum]![cboDept])
AND ((Initiatives.ReleaseNumber)=[Forms]![repExecSum]![cboRelease]) AND
((Initiatives.DroppedStatus)=0) AND
((Initiatives.NotSupportedStatus)=0));

xTab2 =
SELECT PhaseActivity.PhaseActivity, App, Impacted, Description,
InitiativeUniqueNumber
FROM ImpactApp INNER JOIN PhaseActivity ON
PhaseActivity.PhaseActivity=ImpactApp.PhaseActivit y;

xTabSum =
SELECT InitiativeNumber, InitiativeName, PDD, Scope, App, Impacted,
Description, DeptNumber, ReleaseNumber
FROM xTab1 INNER JOIN xTab2 ON
xTab1.InitiativeUniqueNumber=xTab2.InitiativeUniqu eNumber;

xTabFinal =
TRANSFORM Count(xTabSum.Impacted) AS Expr1
SELECT xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName,
Count(xTabSum.Impacted) AS [Total Impacts]
FROM xTabSum
GROUP BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
ORDER BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
PIVOT xTabSum.App;

The error occrus when running the xTabFinal Query (which is the
CrossTab Query). I get the following error: "The Microsoft Jet database
engine does not recognize 'Forms!repExecSum!cboDept' as a valid field
name or expression". Of cours, I assume that is an expected result
because CrossTab Queries (from what I know so far) require that all
information be present (read: hard-coded) in order for results to be
returned successfully.

Basically, I'm wondering if there is any way around this? I don't mind
using VBA Code if need be, but please try to provide me with as much
VBA Code as possible as I'm completely new to using SQL Queries and
Reports (especially CrossTab Queries) in VBA code. But I am extremely
comfortable with the Visual Basic language and other functions dealing
with Events/Forms/Controls etc in VBA (including within Microsoft
Access).

I'm sorry for the ridiculously long question, but I really don't know
where else I can find informatino or help about this particular
situation. Honestly, I don't even expect anyone to be able to help me
with the extent that I need, but it makes me feel a little bit better
to at least ask. :)

Thanks and God Bless,
-Jeremy

Of course, if you need any further information or clarification, please
let me know!

[tizmagik]

I have tried using the PARAMETER in the Query Design/Builder, but it
generated the same result. I will try again and also try your
suggestion about the 'underlying query' in the Report.

For now, I have created an ad-hoc work around in which I had to
hard-code all the data fields, it was a pain, but deadlines have to be
met. I'm still interested in learning how to do this, however, should
necessary changes or a new project come up later.

Thank you for your great reply :)

[HS Hartkamp]

<ti******@gmail.com> schreef in bericht
news:11*********************@y43g2000cwc.googlegro ups.com...

I have tried using the PARAMETER in the Query Design/Builder, but it
generated the same result. I will try again and also try your
suggestion about the 'underlying query' in the Report.

For now, I have created an ad-hoc work around in which I had to
hard-code all the data fields, it was a pain, but deadlines have to be
met. I'm still interested in learning how to do this, however, should
necessary changes or a new project come up later.

Thank you for your great reply :)

Below is a bit of code from a booking-application that I made nearly 10
years ago for a scouting campsite. In there, I have a similar situation
where a crosstab query is used for a report, and the query itself is based
on a number of underlying queries (even more complex than yours I might
add).

What I did (then) was to generate the report using a wizard, and then I read
through the code to understand what was happening. During this, I added a
series of comments to be able to understand the details later. This way, I
learned the lot.

The trick in crosstab-reports is that the report itself opens the query, and
the resulting table (regardless of the number of columns) triggers a series
of events based on the (volume of) row-data. Think of pre-historic line
printing of reports that you might know from the Cobol-era.
In these events, the column data is linked -using VB-code- to a large number
of pre-formatted unbound (!) data fields that have logical names, but no
other link to the columns of the query.
This VB-code does of course need access to the real data, and therefore the
code *also* opens the query. This is done in the Report_open event.
Furthermore, in the Detail_format section, the data is put in the (unlinked)
textboxes, and in the Page_header_format bit, the column names are put in
other (unlinked) textboxes. The surplus in data field is hidden when your
query has less columns than the blank report.

I suspect that the error in your app is where the code tries to open the
query, but then realises that the parameter is not filled. I vaguely
remember this not working at first in my program, whereas in normal reports
this is never a problem. I strongly remember that it took me quite a long
time without someone explaining me this lot.

If this is indeed the error, the solution should be to modify the code
(Report_open) by inserting the parameter into the query just before the
recordset gets opened. My hunch is that your report is based on the same
standard as mine once was, so by comparing the two, you should be able to
find the relevant differences. (mind you, I changed more than just this).

Best of luck,

Bas Hartkamp.

PS: I'm sure Dutch is not a too difficult language understand for you, as my
16-month old daughter seems to have no problems with it. If you do want to
know what the comments mean, let me know.
-----------
Example code from Scouting Campsite Booking program v7.1
-----------

Private Sub Report_Open(Cancel As Integer)

' Maak schaduwrecordset om gegevens in het rapport te kunnen zetten.
' Dit gaat op basis van de begindatum in 'Print Overzicht'
'

Dim intX As Integer
Dim qdf As DAO.QueryDef
Dim frm As Form

' Rapport niet openen zonder startwaarden uit scherm
If Not FormOpen("Print overzicht") Then
Cancel = True
MsgBox "Dit rapport kun je alleen opvragen vanuit het menu."
Exit Sub
End If

' Maak schaduw-recordset om gegevens te kunnen vullen.
' Structuur van rapport geeft alleen alle events en aantallen regels;
alle
' boxen zijn verder 'unbound' en worden vanuit deze schaduwrecordset
gevuld.

Set dbsReport = CurrentDb
Set frm = Forms![Print overzicht]
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Planboek2")
' Zet parameter voor startdatum voor schaduw-query; rapport pakt 'm
direct
qdf.Parameters("[Forms]![Print overzicht]![StartDatum]") =
frm![StartDatum]
qdf.Parameters("[Forms]![Print overzicht]![EindDatum]") =
frm![EindDatum]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Even het aantal kolommen onthouden..
' (= aantal uit query -1 vanwege de verborgen <NULL>-waarde
intColumnCount = rstReport.Fields.Count - 1

' maar mag maximum van rapport niet overstijgen
If intColumnCount > conTotalColumns Then
intColumnCount = conTotalColumns
End If

' Zet knoppenbalk goed en maximaliseer rapport.
DoCmd.ShowToolbar "tbrPrintreport", acToolbarYes
DoCmd.Maximize

End Sub

[tizmagik]

Thank you very much for your reply HS Hartkamp. It looks like I'm going
through a (perhaps less complicated) version of what you experienced
some time ago.

This code seems to be what I'm looking for, but yea, if it's not too
much trouble can you please translate the comments? I do not know Dutch
:)

Thanks again!

[HS Hartkamp]

See below.

<ti******@gmail.com> schreef in bericht
news:11**********************@c74g2000cwc.googlegr oups.com...

Thank you very much for your reply HS Hartkamp. It looks like I'm going
through a (perhaps less complicated) version of what you experienced
some time ago.

This code seems to be what I'm looking for, but yea, if it's not too
much trouble can you please translate the comments? I do not know Dutch
:)

Thanks again!

Private Sub Report_Open(Cancel As Integer)

' Open second recordset
' based on the parameter 'Begin date' on the form 'Print Reports'
'

Dim intX As Integer
Dim qdf As DAO.QueryDef
Dim frm As Form

' Make sure form is open (i.e. parameter is available)
' otherwise do not open this report.

If Not FormOpen("Print overzicht") Then 'FormOpen is custom
function based on SysCmd function
Cancel = True
MsgBox "Dit rapport kun je alleen opvragen vanuit het menu."
Exit Sub
End If

' Make Shadow-recordset to be able to fill in the values in the report
' Structure of the report triggers events based on number of rows
' All text boxes are unbound and are filled by code based on this
recordset.

Set dbsReport = CurrentDb
Set frm = Forms![Print overzicht]
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Planboek2")
' Fill parameter (start date). The report gets this directly
qdf.Parameters("[Forms]![Print overzicht]![StartDatum]") =
frm![StartDatum]
qdf.Parameters("[Forms]![Print overzicht]![EindDatum]") =
frm![EindDatum]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Remember number of columns for later use..
' (minus one only relevant in this context, because the column with
<null>label is hidden later
intColumnCount = rstReport.Fields.Count - 1

' Make sure # columns is less than available # text boxes
If intColumnCount > conTotalColumns Then
intColumnCount = conTotalColumns
End If

' show toolbar and maximise report
DoCmd.ShowToolbar "tbrPrintreport", acToolbarYes
DoCmd.Maximize

End Sub

[tizmagik]

Thank you so much, that definitely helps! Cheers!
HS Hartkamp wrote:

See below.

<ti******@gmail.com> schreef in bericht
news:11**********************@c74g2000cwc.googlegr oups.com...

Thank you very much for your reply HS Hartkamp. It looks like I'm going
through a (perhaps less complicated) version of what you experienced
some time ago.

This code seems to be what I'm looking for, but yea, if it's not too
much trouble can you please translate the comments? I do not know Dutch
:)

Thanks again!

Private Sub Report_Open(Cancel As Integer)

' Open second recordset
' based on the parameter 'Begin date' on the form 'Print Reports'
'

Dim intX As Integer
Dim qdf As DAO.QueryDef
Dim frm As Form

' Make sure form is open (i.e. parameter is available)
' otherwise do not open this report.

If Not FormOpen("Print overzicht") Then 'FormOpen is custom
function based on SysCmd function
Cancel = True
MsgBox "Dit rapport kun je alleen opvragen vanuit het menu."
Exit Sub
End If

' Make Shadow-recordset to be able to fill in the values in the report
' Structure of the report triggers events based on number of rows
' All text boxes are unbound and are filled by code based on this
recordset.

Set dbsReport = CurrentDb
Set frm = Forms![Print overzicht]
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Planboek2")
' Fill parameter (start date). The report gets this directly
qdf.Parameters("[Forms]![Print overzicht]![StartDatum]") =
frm![StartDatum]
qdf.Parameters("[Forms]![Print overzicht]![EindDatum]") =
frm![EindDatum]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Remember number of columns for later use..
' (minus one only relevant in this context, because the column with
<null>label is hidden later
intColumnCount = rstReport.Fields.Count - 1

' Make sure # columns is less than available # text boxes
If intColumnCount > conTotalColumns Then
intColumnCount = conTotalColumns
End If

' show toolbar and maximise report
DoCmd.ShowToolbar "tbrPrintreport", acToolbarYes
DoCmd.Maximize

End Sub