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
|
|||
|
|||
Sums & Cross-Tab Query
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 |
#2
|
|||
|
|||
Sums & Cross-Tab Query
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 HeatherD25 wrote: 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 |
#3
|
|||
|
|||
Sums & Cross-Tab Query
It worked. Thanks!!
"John Spencer" wrote: 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 HeatherD25 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|