Grouping Problem in Report Based on Crosstab 1

[kmkland]

I am trying to group in a report based on date in "mmmm yyyy" format. The original date format is "mm/dd/yyyy". I created a crosstab query to show many times each code occurred during each month (dates are determined by parameters, so the codes are the column headings and the dates are the row headings).

The problem I'm having is that if code 21 occurred 1 time in july 05 and code 22 occurred 2 times in july 05, then there will be 3 rows for july 05.

Here is the statement for my crosstab query:

PARAMETERS [Forms]![frmParameters]![StartDate] DateTime;
TRANSFORM Count(qryTtlAbs_Count.[EmpFile#]) AS [CountOfEmpFile#]
SELECT qryTtlAbs_Count.Date_of_Absence, Count(qryTtlAbs_Count.[EmpFile#]) AS [Total Of EmpFile#]
FROM qryTtlAbs_Count
WHERE (((qryTtlAbs_Count.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And Date()))
GROUP BY qryTtlAbs_Count.Date_of_Absence
ORDER BY qryTtlAbs_Count.Date_of_Absence
PIVOT qryTtlAbs_Count.[Absence Code];

In my report in Sorting and Grouping, I have the date grouped on month, but yet, there are still multiple rows for each month.

Could this possibly be a problem with my join in the query that the crosstab query is based upon? Here is the statement for that query:

SELECT tblEmpInfo.[EmpFile#], tblAbsences.Date_of_Absence, [tblAbsences]![Code] & ' ' & [tblReasonsForAbsences]![Reasons_for_Absences] AS [Absence Code]
FROM tblEmpInfo INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblAbsences.Code)="21" Or (tblAbsences.Code)="22" Or (tblAbsences.Code)="23"))
ORDER BY tblAbsences.Date_of_Absence;

Please, any help is appreciated!!!

Rgds,
Kmkland

 

[lameid]

PARAMETERS [Forms]![frmParameters]![StartDate] DateTime;
TRANSFORM Count(qryTtlAbs_Count.[EmpFile#]) AS [CountOfEmpFile#]
SELECT qryTtlAbs_Count.Date_of_Absence
FROM qryTtlAbs_Count
WHERE (((qryTtlAbs_Count.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And Date()))
GROUP BY qryTtlAbs_Count.Date_of_Absence
ORDER BY qryTtlAbs_Count.Date_of_Absence
PIVOT qryTtlAbs_Count.[Absence Code] IN ("21","22","23");

The major thing I did was to remove <, Count(qryTtlAbs_Count.[EmpFile#]) AS [Total Of EmpFile#]>
from your select clause. Beyond that, I also added an in Statement to your Pivot clause so that even if the data doesn't have a value for one of these, it will appear on your report. If you were to right click your 'Column Heading' field in the QBE and select properties, this would be the column headings property.

 

[kmkland]

Lameid,

It is still giving me the same results...multiple rows for 1 month.

And I also noticed that 1 of my July dates is showing up as June 2005 on my report!

Thanks again!!!
Kmkland

 

[lameid]

Hmmm... I don't look at too many crosstab SQL statements. How many columns are supposed to be in the crosstab and what are they?

In the QBE which columns are row headings and wich is the column heading and value?

I think I'm just overlooking something.

 

[kmkland]

Ok.

Date_of_Absence = Row Heading (Group By)
Code = Column Heading (Group By)
EmpFile# = Value (Count)

There are 3 columns: 21, 22, 23.

Rgds,
Kmkland

 

[lameid]

Date_of_Absence must contain values OTHER than just the month does't it? In other words 7/1/2005, 7/5/2005 or different days of the month.

Try this for "qryTtlAbs_Count"

SELECT tblEmpInfo.[EmpFile#], Cdate(Format(tblAbsences.Date_of_Absence,"mm/1/yyyy")) as Date_of_Absence, [tblAbsences].

 & ' ' & [tblReasonsForAbsences].[Reasons_for_Absences] AS [Absence Code]
FROM tblEmpInfo INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblAbsences.Code)="21" Or (tblAbsences.Code)="22" Or (tblAbsences.Code)="23"))
ORDER BY tblAbsences.Date_of_Absence;

 

[kmkland]

Brilliant!!!

Worked like a charm!!!
Thanks so much for your help!!!!!!

Kmkland