Dynamic Crosstab Query

[Guest]

Hello

TIA. I have a report that I want to open from a form called atnMEETINGATTENDANCE. The form has a field called txtCOMMITTEE. This field hold a Committee ID, and the form is bound to a committee table. So basically, when the person presses on the button to create the crosstab report, the RecordSource of the report must use that field information. The query is

TRANSFORM Count(atnMEETINGATTENDANCE.[ID#]) AS [CountOfID#] SELECT atnMEETINGATTENDANCE.[ID#
FROM atnMEETING LEFT JOIN atnMEETINGATTENDANCE ON atnMEETING.MeetingID = atnMEETINGATTENDANCE.MeetingI
WHERE (((atnMEETING.CommitteeID)=[forms]![atnmeetingattendance]![txtcommittee]) AND ((atnMEETING.MeetingDate)>Date()-365)) GROUP BY atnMEETINGATTENDANCE.[ID#] PIVOT atnMEETING.MeetingDate

Thanks for you help!

Eri

 

[Roger Carlson]

Reports based on crosstab queries are problematic because the bound fields
may change. On my website (see sig below) is a small sample database called
"CrosstabReport" which illustrates one way to do this.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hello,

TIA. I have a report that I want to open from a form called

atnMEETINGATTENDANCE. The form has a field called txtCOMMITTEE. This field
hold a Committee ID, and the form is bound to a committee table. So
basically, when the person presses on the button to create the crosstab
report, the RecordSource of the report must use that field information. The
query is:

TRANSFORM Count(atnMEETINGATTENDANCE.[ID#]) AS [CountOfID#] SELECT atnMEETINGATTENDANCE.[ID#]
FROM atnMEETING LEFT JOIN atnMEETINGATTENDANCE ON atnMEETING.MeetingID = atnMEETINGATTENDANCE.MeetingID
WHERE

(((atnMEETING.CommitteeID)=[forms]![atnmeetingattendance]![txtcommittee])
AND ((atnMEETING.MeetingDate)>Date()-365)) GROUP BY
atnMEETINGATTENDANCE.[ID#] PIVOT atnMEETING.MeetingDate;

 

[Guest]

Thanks Roger, the problem is that this doesn't explain how to get specific information from a form before the Crosstab query is created. In other words, a user might be on a specific committee and want to print a report. The user can then move to the next committee and print the same report again, but the report will ahve to get the committee code from the form.

eric

 

[Roger Carlson]

Oh. This is a separate problem that occurs with crosstab queries. If you
want to have a parameter in a crosstab query, you have to add a Parameter
line to the SQL statement, which specifies what the datatype of the
parameter is:

PARAMETERS [forms]![atnmeetingattendance]![txtcommittee] Text ( 255 );
TRANSFORM Count(atnMEETINGATTENDANCE.[ID#]) AS [CountOfID#] SELECT
atnMEETINGATTENDANCE.[ID#]
FROM atnMEETING LEFT JOIN atnMEETINGATTENDANCE ON atnMEETING.MeetingID =
atnMEETINGATTENDANCE.MeetingID
WHERE
(((atnMEETING.CommitteeID)=[forms]![atnmeetingattendance]![txtcommittee])
AND ((atnMEETING.MeetingDate)>Date()-365)) GROUP BY
atnMEETINGATTENDANCE.[ID#] PIVOT atnMEETING.MeetingDate;

The value between PARAMETERS and the dataype must match the parameter in the
Where clause exactly. Notice the PARAMETERS statement IS followed by a
semi-colon.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks Roger, the problem is that this doesn't explain how to get specific

information from a form before the Crosstab query is created. In other
words, a user might be on a specific committee and want to print a report.
The user can then move to the next committee and print the same report
again, but the report will ahve to get the committee code from the form.

 

[Guest]

Thanks Roger

This now works for the query, but when I base the report on this query I get an error stating: The Microsoft Jet Database Engine does not recognize " as a valid field name or expression. If I run the query directly from the query screen - and enter a criteria manually - it works.

 

[Roger Carlson]

You have to go back to my first reply, now. Because your column headings
can change each time you run the report, you can't use the hard-coded
ControlSource names that a report uses. You have to devise some way to make
generic field names. My sample does this by creating a second query from
the first and aliasing the columns with generic field names like Field1,
Field2, etc.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks Roger,

This now works for the query, but when I base the report on this query I

get an error stating: The Microsoft Jet Database Engine does not recognize "
as a valid field name or expression. If I run the query directly from the
query screen - and enter a criteria manually - it works.