If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
still no dynamic crosstab report
I have posted here before about this topic but the help I got didn't work
because I work with DAO library. Following is the code that I'm using. it gets hung up in the line: Set rst = CurrentDb.OpenRecordset(Me.RecordSource) and the debug tells me that my query is the problem. Therefore, I also added the SQL for all my queries. The offending query is qryEventsReportCard_Crosstab which is based on the preceeding two queries. My user wants to see Events that occur in the time period selected, and then also see the 6 and 12 mo. average for the month preceeding the start date selected. The only way I can think of doing this is doing one query that calcualtes these averages and then another one that sets criteria for the selected time frame (which includes injuries or all) and join them by event type and unit. The crosstab runs great, but the report won't come out because it doesn't like this crosstab querie. The events, unit and averages (6 and 12 mo) would be static, while the new events need to be dynamic. I'm really getting big headaches over this, so please, someone have mercy on me. Thanks. Brigitte P. Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer ' Open a recordset for the crosstab query. Debug.Print Me.RecordSource Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail ' section, minus 4 because we don't count the first 4 fields that are row headings in the crosstab. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 1, because we don't ' count the row header field (City) intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions ' and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else ' Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub 1. qryEventsReportCard PARAMETERS [Forms]![frmSwitchboard]![txtUnit] Text ( 255 ), [Forms]![frmSwitchboard]![txtEventType] Text ( 255 ), [Forms]![frmSwitchboard]![txtInjury] Text ( 255 ), [Forms]![frmSwitchboard]![txtStartDate] DateTime, [Forms]![frmSwitchboard]![txtEndDate] DateTime; SELECT CliEventTable.EventEvent, CliEventTable.EventIllnessInjury, CliEventTable.EventDate, Format([EventDate],"yyyy mm") AS MonthNumber, CliEventTable.EventBuilding, IIf([EventDate] Between (DateAdd("m",-12,[Forms]![frmSwitchboard]![txtStartDate])) And [Forms]![frmSwitchboard]![txtStartDate],1,0) AS Twelve, IIf([EventDate] Between (DateAdd("m",-6,[Forms]![frmSwitchboard]![txtStartDate])) And [Forms]![frmSwitchboard]![txtStartDate],1,0) AS Six FROM CliEventTable WHERE (((CliEventTable.EventEvent) Like [Forms]![frmSwitchboard]![txtEventType] & "*") AND ((CliEventTable.EventIllnessInjury)"None apparent") AND ((CliEventTable.EventDate) Between (DateAdd("m",-12,[Forms]![frmSwitchboard]![txtStartDate])) And [Forms]![frmSwitchboard]![txtEndDate]) AND ((CliEventTable.EventBuilding) Like [Forms]![frmSwitchboard]![txtUnit] & "*")); 2 qryEventReportCard12_6Sum PARAMETERS [Forms]![frmSwitchboard]![txtStartDate] DateTime, [Forms]![frmSwitchboard]![txtEndDate] DateTime; SELECT qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent, Sum(qryEventsReportCard.Twelve) AS SumOfTwelve, Sum(qryEventsReportCard.Six) AS SumOfSix FROM qryEventsReportCard GROUP BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent ORDER BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent; 2. qryEventsReportCard_Crosstab (This seems to be the offending record source; it is based on the above two queries. PARAMETERS [Forms]![frmSwitchboard]![txtUnit] Text ( 255 ), [Forms]![frmSwitchboard]![txtEventType] Text ( 255 ), [Forms]![frmSwitchboard]![txtInjury] Text ( 255 ), [Forms]![frmSwitchboard]![txtStartDate] DateTime, [Forms]![frmSwitchboard]![txtEndDate] DateTime; TRANSFORM Count(qryEventsReportCard.EventEvent) AS CountOfEventEvent SELECT qryEventsReportCard.EventBuilding AS Unit, qryEventsReportCard.EventEvent AS Event, qryEventReportCard12_6Sum.SumOfSix AS [6 Mo Tot], [SumOfSix]/6 AS [6 Mo Avg], qryEventReportCard12_6Sum.SumOfTwelve AS [12 Mo Tot], [SumOfTwelve]/12 AS [12 Mo Avg] FROM qryEventsReportCard INNER JOIN qryEventReportCard12_6Sum ON (qryEventsReportCard.EventEvent = qryEventReportCard12_6Sum.EventEvent) AND (qryEventsReportCard.EventBuilding = qryEventReportCard12_6Sum.EventBuilding) WHERE (((qryEventsReportCard.EventDate) Between [Forms]![frmSwitchboard]![txtStartDate] And [Forms]![frmSwitchboard]![txtEndDate])) GROUP BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent, qryEventReportCard12_6Sum.SumOfSix, [SumOfSix]/6, qryEventReportCard12_6Sum.SumOfTwelve, [SumOfTwelve]/12 ORDER BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent PIVOT qryEventsReportCard.MonthNumber; |
Thread Tools | |
Display Modes | |
|
|