Sums & Cross-Tab Query

[Guest]

I am trying to create an underlying query for a report. Here is the data I
have:

- I created a cross-tab query that takes employee timecard information and
summarizes it by:
Employee name, Total Hours, and then breaks it out by the type of
hours...and sums together those types of hours for each employee (there are 3
main types of hours but sub-types within those 3).

An example of the data I would get
Name Hours Development Client Training Internal Sick Leave
Heather 80 20 20 20 10 10

The cross-tab is displaying and summing the data how I would like.

Then... I want to add more data to the report than what I can display in a
cross-tab, so I created another query that has the cross-tab as one of it's
sources as well as my employee table. I also wanted to create a SUM of the
hours for each of my 3 main types - CLIENT (Client), INTERNAL (Development +
Internal), OTHER (Training + Sick Leave). This is where I'm having a
problem. In the NEW query I created, I added a field that says INT:
[Development]+[Internal]. When I run this query, it doesn't SUM unless both
of the fields have something in them. I can't figure out why?? If I create
a field that does division, like DevPercentage: [Development]/[Hours] it
works just fine.

Any ideas?

Thanks!
Heather

 

[John Spencer]

The easiest method to fix this would be to modify the crosstab query to
return zero if there is no hours for one of the hour types.

Your query probably starts off with something that looks like the
following (in SQL View - Menu: View: SQL)


TRANSFORM Sum([HoursWorked]) as SumHours
SELECT ...
FROM ...
PIVOT TimeType In ("Development","Client","Training","Internal", "Sick
Leave")

Change that to
TRANSFORM CDbl(NZ(Sum([HoursWorked]),0)) as SumHours
SELECT ...

Also for efficiency, you probably would be best to define the column
names for the pivot (if you have not done so).



John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Guest]

It worked. Thanks!!

The easiest method to fix this would be to modify the crosstab query to
return zero if there is no hours for one of the hour types.

Your query probably starts off with something that looks like the
following (in SQL View - Menu: View: SQL)


TRANSFORM Sum([HoursWorked]) as SumHours
SELECT ...
FROM ...
PIVOT TimeType In ("Development","Client","Training","Internal", "Sick
Leave")

Change that to
TRANSFORM CDbl(NZ(Sum([HoursWorked]),0)) as SumHours
SELECT ...

Also for efficiency, you probably would be best to define the column
names for the pivot (if you have not done so).



John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

I am trying to create an underlying query for a report. Here is the data I
have:

- I created a cross-tab query that takes employee timecard information and
summarizes it by:
Employee name, Total Hours, and then breaks it out by the type of
hours...and sums together those types of hours for each employee (there are 3
main types of hours but sub-types within those 3).

An example of the data I would get
Name Hours Development Client Training Internal Sick Leave
Heather 80 20 20 20 10 10

The cross-tab is displaying and summing the data how I would like.

Then... I want to add more data to the report than what I can display in a
cross-tab, so I created another query that has the cross-tab as one of it's
sources as well as my employee table. I also wanted to create a SUM of the
hours for each of my 3 main types - CLIENT (Client), INTERNAL (Development +
Internal), OTHER (Training + Sick Leave). This is where I'm having a
problem. In the NEW query I created, I added a field that says INT:
[Development]+[Internal]. When I run this query, it doesn't SUM unless both
of the fields have something in them. I can't figure out why?? If I create
a field that does division, like DevPercentage: [Development]/[Hours] it
works just fine.

Any ideas?

Thanks!
Heather