Dynamic Crosstab Query Report 1

[cfuser10]

Hello,

I would like to make a dynamic report from a crosstab query that will have a different date range everytime it is run. It will be computing a quarterly compliance percentage. The crosstab query is based on a table that was created using two queries to compute values.

The table (tblDeptSummary) has the following fields:
Dept (text)
MthYr (which is mm/yyyy format)
Compliance (number)

My crosstab query is as follows:
TRANSFORM Avg(tblDeptSummary.compliance) AS AvgOfcompliance
SELECT tblDeptSummary.Dept, Round(Avg([compliance])) AS [Total Of compliance]
FROM tblDeptSummary
GROUP BY tblDeptSummary.Dept
PIVOT tblDeptSummary.MthYr;

When I create report, I would like to have a form pop up asking for the date range parameter - begindate & enddate.

I tried to put the parameter in the crosstab query, but not sure how to get it to work when the date is in month/year format. The next thing I'm not sure how to do is make the report column headings dynamic. The departments will be the rows and I'd like the 3 months in the quarter as the column headings.

I've looked at various dynamic crosstab query report examples, but can't seem to figure out how to get an example to work for me.

I appreciate any suggestions. Thank you.

 

[SkipVought]

hi,

First, you will have a problem colating a STRING in TRUE mm/yyyy sequence. You will need a yyyy/mm STRING in order to get the ORDER that you expect.

something like...

TRANSFORM Avg(tblDeptSummary.compliance) AS AvgOfcompliance
SELECT tblDeptSummary.Dept, Round(Avg([compliance])) AS [Total Of compliance]
FROM tblDeptSummary
[b]
where MthYr Between format(dateserial(year(date()),month(date()))-4,1),'yyyy/mm') and format(dateserial(year(date()),month(date()))-1,0)-1,'yyyy/mm')
[/b]
GROUP BY tblDeptSummary.Dept
PIVOT tblDeptSummary.MthYr;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

cfuser10,
Did you check the FAQs first faq703-5466?

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

I played around with my queries and found I was able to do all the calculations in one crosstab query and didn't need to make a table. So my dates are in date format now, not text.

Duane, I looked at the FAQ you suggested, but I'm still having some trouble applying it to my query. I'm still not sure how to make my column headings dynamic with a dynamic begin and end date. I need to choose a date range to show only 3 months in the columns of a crosstab report. My updated crosstab query is below:

PARAMETERS [Forms]![frmEnterDates]![BeginDate] DateTime, [Forms]![frmEnterDates]![EndDate] DateTime;
TRANSFORM Round(((Sum(Abs([status]='complete')))/((Count([status]))-(Sum(Abs([status]="NA")))))*100) AS compliance
SELECT tblDept.Dept, Round(Avg([compliance])) AS [Total Of compliance]
FROM tblHistory INNER JOIN tblDept ON tblHistory.Dept = tblDept.DeptNo
WHERE ((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]))
GROUP BY tblDept.Dept, Year([ReviewDate])
PIVOT Month([ReviewDate]);

Thank you for any suggestions.

 

[cfuser10]

After more experimenting, I have this almost working according to the FAQ. The only thing I'm having a problem with is getting the column labels in the report to show the month name. I get a value of "#NAME" showing when I use the control DateAdd control source as shown.

Here is my updated crosstab query:
PARAMETERS [Forms]![frmEnterDates]![BeginDate] DateTime, [Forms]![frmEnterDates]![EndDate] DateTime;
TRANSFORM Round(((Sum(Abs([status]='complete')))/((Count([status]))-(Sum(Abs([status]="NA")))))*100) AS compliance
SELECT tblDept.Dept, Round(Avg([compliance])) AS [Total Of compliance]
FROM tblHistory INNER JOIN tblDept ON tblHistory.Dept = tblDept.DeptNo
WHERE ((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]))
GROUP BY tblDept.Dept
PIVOT "Mth" & DateDiff("m",[ReviewDate],[Forms]![frmEnterDates]![EndDate]) In ("Mth0","Mth1","Mth2");

 

[dhookom]

What expressions are you using for the column labels in the report?

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

I have the following expression for the column label which is an text box:

=DateAdd("m",0,[Forms]![frmEnterDates]![EndDate])

 

[cfuser10]

I would like to show the Month Name and the Year in the column headings.

 

[dhookom]

Is the form still open as the report is run?
You might also try return the Enddate as a column in the query.

PARAMETERS [Forms]![frmEnterDates]![BeginDate] DateTime, [Forms]![frmEnterDates]![EndDate] DateTime;
TRANSFORM Round(((Sum(Abs([status]='complete')))/((Count([status]))-(Sum(Abs([status]="NA")))))*100) AS compliance
SELECT tblDept.Dept, Round(Avg([compliance])) AS [Total Of compliance], [Forms]![frmEnterDates]![EndDate] as EndDate
FROM tblHistory INNER JOIN tblDept ON tblHistory.Dept = tblDept.DeptNo
WHERE ((tblHistory.ReviewDate) Between [Forms]![frmEnterDates]![BeginDate] And [Forms]![frmEnterDates]![EndDate]))
GROUP BY tblDept.Dept, [Forms]![frmEnterDates]![EndDate]
PIVOT "Mth" & DateDiff("m",[ReviewDate],[Forms]![frmEnterDates]![EndDate]) In ("Mth0","Mth1","Mth2");

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

If you want to show the month name, you can simply change the format of the text box to: mmmm-yyyy (or similar)

Duane
Hook'D on Access
MS Access MVP

 

[cfuser10]

Duane,
Right before you responded, I figured out that I was not keeping the form open, so that is working now. Also got the date format figured out.

Thank you!