Reports based on Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a crosstab query (SQL below). My problem is that I
do not want my report to show fields (which are employees) when the employee
has no time, meaning when the field is null. When I remove the employee from
the column heading properties of the query, I am prompted for that employee
when I run the report (if the employee does have time). In summary, I am
wanting the employee to show up as a field heading when the employee does
have time, but not show up when the employee does not have time (when field
is null). Any help would be greatly appreciated.





PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY] DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");
 
The report itself is static that is why it does this. The only way I am
aware of accomplishing what you want is to build a new report each time.
Could be unworthy of the results. I would look at modifying my query (NOT
USING A CROSSTAB) to accomplish what you want.

What precisely are you trying to achieve in the report. You may have to use
some type Visible property on off to turn on only those that are not null at
the time.

Not sure without more information.
 
Thanks for your reply. I may be able to do what I need by modifying the
visible property in the report.

Hansford cornett said:
The report itself is static that is why it does this. The only way I am
aware of accomplishing what you want is to build a new report each time.
Could be unworthy of the results. I would look at modifying my query (NOT
USING A CROSSTAB) to accomplish what you want.

What precisely are you trying to achieve in the report. You may have to use
some type Visible property on off to turn on only those that are not null at
the time.

Not sure without more information.



Amanda said:
I have a report based on a crosstab query (SQL below). My problem is that I
do not want my report to show fields (which are employees) when the employee
has no time, meaning when the field is null. When I remove the employee from
the column heading properties of the query, I am prompted for that employee
when I run the report (if the employee does have time). In summary, I am
wanting the employee to show up as a field heading when the employee does
have time, but not show up when the employee does not have time (when field
is null). Any help would be greatly appreciated.





PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY] DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");
Click to expand...
Click to expand...
 
Thanks. The reports in the database don't really accomplish the same thing I
am trying to do.

Duane Hookom said:
There are several samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The crosstab.mdb has a
report that will only include the "used" columns.

--
Duane Hookom
MS Access MVP
--

Amanda said:
I have a report based on a crosstab query (SQL below). My problem is that
I
do not want my report to show fields (which are employees) when the
employee
has no time, meaning when the field is null. When I remove the employee
from
the column heading properties of the query, I am prompted for that
employee
when I run the report (if the employee does have time). In summary, I am
wanting the employee to show up as a field heading when the employee does
have time, but not show up when the employee does not have time (when
field
is null). Any help would be greatly appreciated.





PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY]
DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");
Click to expand...
Click to expand...
 
Then, you didn't adequately describe your requirements.

--
Duane Hookom
MS Access MVP


Amanda said:
Thanks. The reports in the database don't really accomplish the same
thing I
am trying to do.

Duane Hookom said:
There are several samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The crosstab.mdb has
a
report that will only include the "used" columns.

--
Duane Hookom
MS Access MVP
--

Amanda said:
I have a report based on a crosstab query (SQL below). My problem is
that
I
do not want my report to show fields (which are employees) when the
employee
has no time, meaning when the field is null. When I remove the
employee
from
the column heading properties of the query, I am prompted for that
employee
when I run the report (if the employee does have time). In summary, I
am
wanting the employee to show up as a field heading when the employee
does
have time, but not show up when the employee does not have time (when
field
is null). Any help would be greatly appreciated.





PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY]
DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top