CutAndPaste
Registered User.
- Local time
- Today, 11:56
- Joined
- Jul 16, 2001
- Messages
- 60
I've got to create some reports showing simple Cross Tab results that calculate numbers and percentages of a various fieldsCross Tabbed against their Gender.
I thought I'd create just one Report template and set the datasource of the report to the required query by referencing the query name in a list in a combo box on a form.
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Forms![frmMenuMain]![ComboChooseQuery] ' Chooses the query from a list of relevant queries
Me.[Variable1].ControlSource = Forms![frmMenuMain]![ddbAdviceCategory] ' Sets name of the field in the query to report upon
Me.GroupLevel(0).ControlSource = Forms![frmMenuMain]![ddbAdviceCategory] ' Sets the Group level of the report.
End Sub
This works great, So now I have one report and several optional queries as datasources. The existing SQL for one of the the Cross Tab queries is:
PARAMETERS [Forms]![frmMenuMain]![StartDate] DateTime, [Forms]![frmMenuMain]![EndDate] DateTime;
TRANSFORM Count(*) AS CountOfClients
SELECT qryClientsAllReportNoDates.Employment, Count(qryClientsAllReportNoDates.Gender) AS CountOfGender
FROM qryClientsAllReportNoDates
WHERE (((qryClientsAllReportNoDates.VisitDate) Between [Forms]![frmMenuMain]![StartDate] And [Forms]![frmMenuMain]![EndDate]))
GROUP BY qryClientsAllReportNoDates.Employment
PIVOT qryClientsAllReportNoDates.Gender;
This selects the field Employment from the Query qryClientsAllReportNoDates and counts the males, females and total clients in each employment category.
So, my brain starts to think, can the same be done for the Cross Tab queries that provide the data for the reports? Having one dynamic Cross Tab must be better than having several static queries. I had a look at the SQL for the Cross Tab query and tried referencing a Combo Box on my form that had the field names of the required fields in it but didn't get very far (in fact nowhere at all even after some considerable effort and loss of quality family time). Can this sort of "dynamic" Cross Tab query be created?
I thought I'd create just one Report template and set the datasource of the report to the required query by referencing the query name in a list in a combo box on a form.
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Forms![frmMenuMain]![ComboChooseQuery] ' Chooses the query from a list of relevant queries
Me.[Variable1].ControlSource = Forms![frmMenuMain]![ddbAdviceCategory] ' Sets name of the field in the query to report upon
Me.GroupLevel(0).ControlSource = Forms![frmMenuMain]![ddbAdviceCategory] ' Sets the Group level of the report.
End Sub
This works great, So now I have one report and several optional queries as datasources. The existing SQL for one of the the Cross Tab queries is:
PARAMETERS [Forms]![frmMenuMain]![StartDate] DateTime, [Forms]![frmMenuMain]![EndDate] DateTime;
TRANSFORM Count(*) AS CountOfClients
SELECT qryClientsAllReportNoDates.Employment, Count(qryClientsAllReportNoDates.Gender) AS CountOfGender
FROM qryClientsAllReportNoDates
WHERE (((qryClientsAllReportNoDates.VisitDate) Between [Forms]![frmMenuMain]![StartDate] And [Forms]![frmMenuMain]![EndDate]))
GROUP BY qryClientsAllReportNoDates.Employment
PIVOT qryClientsAllReportNoDates.Gender;
This selects the field Employment from the Query qryClientsAllReportNoDates and counts the males, females and total clients in each employment category.
So, my brain starts to think, can the same be done for the Cross Tab queries that provide the data for the reports? Having one dynamic Cross Tab must be better than having several static queries. I had a look at the SQL for the Cross Tab query and tried referencing a Combo Box on my form that had the field names of the required fields in it but didn't get very far (in fact nowhere at all even after some considerable effort and loss of quality family time). Can this sort of "dynamic" Cross Tab query be created?