Pie Chart Zero Results

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi

I am trying to create pie charts that have the data labels
and percentages visible, most of the time.

My problem is the fact that if a data value for my pie
chart is 0, the 0 shows up with its corresponding label at
the top of the pie. If more than one value for the same
record is 0, then the labels stack on top of each other.

What I would like is the 0 values and labels to not show
up at all for that record. Column (bar) charts hide values
and labels, why don't pies?

I have set the properties for the underlying queries so
that the 0 data values do not show in the queries, but
that is not following through to the pie.

I have tried IIF with unsuccessful results so any help is
huge.

My SQl for the pie is:

SELECT Null AS Expr1,
qryStaffMovementCurrentClass.SumOfintTerminationStaff,
qryStaffMovementCurrentClass.SumOfintRetireStaff,
qryStaffMovementCurrentClass.SumOfintLeaveStaff,
qryStaffMovementCurrentClass.SumOfintDisabilityStaff,
qryStaffMovementCurrentClass.SumOfintIncreaseStaff
FROM qryStaffMovementCurrentClass;


The SQL for the underlying query is:

SELECT tblClassifications.strClassification, Sum
([tblCurrentYear.intFtrStaff]+[tblCurrentYear.intPtrStaff]+
[tblCurrentYear.intNetReplaceStaff]) AS intCurrentStaff,
Sum([tblCurrentYear.sngFtrFte]+[tblCurrentYear.sngPtrFte]+
[tblCurrentYear.sngNetReplaceFte]) AS sngCurrentFte, Sum
(tblCurrentYear.intTerminationStaff) AS
SumOfintTerminationStaff, Sum
(tblCurrentYear.intRetireStaff) AS SumOfintRetireStaff, Sum
(tblCurrentYear.intLeaveStaff) AS SumOfintLeaveStaff, Sum
(tblCurrentYear.intDisabilityStaff) AS
SumOfintDisabilityStaff, Sum
(tblCurrentYear.intIncreaseStaff) AS
SumOfintIncreaseStaff, Sum
(tblCurrentYear.sngTerminationFte) AS
SumOfsngTerminationFte, Sum(tblCurrentYear.sngRetireFte)
AS SumOfsngRetireFte, Sum(tblCurrentYear.sngLeaveFte) AS
SumOfsngLeaveFte, Sum(tblCurrentYear.sngDisabilityFte) AS
SumOfsngDisabilityFte, Sum(tblCurrentYear.sngIncreaseFte)
AS SumOfsngIncreaseFte
FROM tblSites INNER JOIN (tblDepartments INNER JOIN
((((((tblClassifications INNER JOIN tblCurrentYear ON
tblClassifications.lngClassificationID =
tblCurrentYear.lngClassificationID) INNER JOIN tblFiveYear
ON tblCurrentYear.lngCurrentID = tblFiveYear.lngFiveID)
INNER JOIN tblNextYear ON tblCurrentYear.lngCurrentID =
tblNextYear.lngNextID) INNER JOIN tblTenYear ON
tblCurrentYear.lngCurrentID = tblTenYear.lngTenID) INNER
JOIN tblThreeYear ON tblCurrentYear.lngCurrentID =
tblThreeYear.lngThreeID) INNER JOIN tblTwoYear ON
tblCurrentYear.lngCurrentID = tblTwoYear.lngTwoID) ON
tblDepartments.lngDepartmentID =
tblCurrentYear.lngDepartmentID) ON tblSites.lngSiteID =
tblCurrentYear.lngSiteID
GROUP BY tblClassifications.strClassification;

THANK YOU!!!
Claire
 
Part of your issue is un-normalized tables. You are storing an employee
status as a field name ie "intRetireStaff". I think you can get around this
by creating a union query that filters out 0s.
SELECT SumOfintTerminationStaff as NumOf, "Terminated" as Status
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION
SELECT SumOfintRetireStaff, "Retired"
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION
SELECT SumOfintLeaveStaff, "Leave"
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION
Select SumOfintDisabilityStaff, "Disabled"
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION
SELECT SumOfintIncreaseStaff, "Increase"
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0;
Use this query as your Row Source.

--
Duane Hookom
MS Access MVP


Claire said:
Hi

I am trying to create pie charts that have the data labels
and percentages visible, most of the time.

My problem is the fact that if a data value for my pie
chart is 0, the 0 shows up with its corresponding label at
the top of the pie. If more than one value for the same
record is 0, then the labels stack on top of each other.

What I would like is the 0 values and labels to not show
up at all for that record. Column (bar) charts hide values
and labels, why don't pies?

I have set the properties for the underlying queries so
that the 0 data values do not show in the queries, but
that is not following through to the pie.

I have tried IIF with unsuccessful results so any help is
huge.

My SQl for the pie is:

SELECT Null AS Expr1,
qryStaffMovementCurrentClass.SumOfintTerminationStaff,
qryStaffMovementCurrentClass.SumOfintRetireStaff,
qryStaffMovementCurrentClass.SumOfintLeaveStaff,
qryStaffMovementCurrentClass.SumOfintDisabilityStaff,
qryStaffMovementCurrentClass.SumOfintIncreaseStaff
FROM qryStaffMovementCurrentClass;


The SQL for the underlying query is:

SELECT tblClassifications.strClassification, Sum
([tblCurrentYear.intFtrStaff]+[tblCurrentYear.intPtrStaff]+
[tblCurrentYear.intNetReplaceStaff]) AS intCurrentStaff,
Sum([tblCurrentYear.sngFtrFte]+[tblCurrentYear.sngPtrFte]+
[tblCurrentYear.sngNetReplaceFte]) AS sngCurrentFte, Sum
(tblCurrentYear.intTerminationStaff) AS
SumOfintTerminationStaff, Sum
(tblCurrentYear.intRetireStaff) AS SumOfintRetireStaff, Sum
(tblCurrentYear.intLeaveStaff) AS SumOfintLeaveStaff, Sum
(tblCurrentYear.intDisabilityStaff) AS
SumOfintDisabilityStaff, Sum
(tblCurrentYear.intIncreaseStaff) AS
SumOfintIncreaseStaff, Sum
(tblCurrentYear.sngTerminationFte) AS
SumOfsngTerminationFte, Sum(tblCurrentYear.sngRetireFte)
AS SumOfsngRetireFte, Sum(tblCurrentYear.sngLeaveFte) AS
SumOfsngLeaveFte, Sum(tblCurrentYear.sngDisabilityFte) AS
SumOfsngDisabilityFte, Sum(tblCurrentYear.sngIncreaseFte)
AS SumOfsngIncreaseFte
FROM tblSites INNER JOIN (tblDepartments INNER JOIN
((((((tblClassifications INNER JOIN tblCurrentYear ON
tblClassifications.lngClassificationID =
tblCurrentYear.lngClassificationID) INNER JOIN tblFiveYear
ON tblCurrentYear.lngCurrentID = tblFiveYear.lngFiveID)
INNER JOIN tblNextYear ON tblCurrentYear.lngCurrentID =
tblNextYear.lngNextID) INNER JOIN tblTenYear ON
tblCurrentYear.lngCurrentID = tblTenYear.lngTenID) INNER
JOIN tblThreeYear ON tblCurrentYear.lngCurrentID =
tblThreeYear.lngThreeID) INNER JOIN tblTwoYear ON
tblCurrentYear.lngCurrentID = tblTwoYear.lngTwoID) ON
tblDepartments.lngDepartmentID =
tblCurrentYear.lngDepartmentID) ON tblSites.lngSiteID =
tblCurrentYear.lngSiteID
GROUP BY tblClassifications.strClassification;

THANK YOU!!!
Claire
Click to expand...
 
Hi Duane:

I think my normalization is OK. intRetireStaff is actually
the number of employees that are planning to retire at the
end of the year. I ran the Performance Analyzer anyway and
it was happy with everything.

Thanks very much for the code, I have been playing with it
for the last couple of days to make sure that it was
pulling the correct data. It does exactly what I needed,
but I had to change it a little bit:

SELECT SumOfintRetireStaff, "Retire", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION SELECT SumOfintTerminationStaff, "Termination",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION SELECT SumOfintLeaveStaff, "Leave", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION SELECT SumOfintDisabilityStaff, "Disability",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION SELECT SumOfintIncreaseStaff, "Increase", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0
ORDER BY strClass;

My only other concern is this. When I added the SQL query
to the database the size of my database blew up from 200
MB to 300 MB! This is the only thing I have played with
and compacting has no effect. I need to recreate this
query for Department and Site data and then for staff
projections for the next 10 years, this database will be
massive!!!!!

Any idea why the size went through the roof?!?!

Thanks for all of your help Duane
Claire
-----Original Message-----
Part of your issue is un-normalized tables. You are storing an employee
status as a field name ie "intRetireStaff". I think you can get around this
by creating a union query that filters out 0s.
SELECT SumOfintTerminationStaff as NumOf, "Terminated" as Status
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION
SELECT SumOfintRetireStaff, "Retired"
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION
SELECT SumOfintLeaveStaff, "Leave"
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION
Select SumOfintDisabilityStaff, "Disabled"
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION
SELECT SumOfintIncreaseStaff, "Increase"
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0;
Use this query as your Row Source.

--
Duane Hookom
MS Access MVP


Hi

I am trying to create pie charts that have the data labels
and percentages visible, most of the time.

My problem is the fact that if a data value for my pie
chart is 0, the 0 shows up with its corresponding label at
the top of the pie. If more than one value for the same
record is 0, then the labels stack on top of each other.

What I would like is the 0 values and labels to not show
up at all for that record. Column (bar) charts hide values
and labels, why don't pies?

I have set the properties for the underlying queries so
that the 0 data values do not show in the queries, but
that is not following through to the pie.

I have tried IIF with unsuccessful results so any help is
huge.

My SQl for the pie is:

SELECT Null AS Expr1,
qryStaffMovementCurrentClass.SumOfintTerminationStaff,
qryStaffMovementCurrentClass.SumOfintRetireStaff,
qryStaffMovementCurrentClass.SumOfintLeaveStaff,
qryStaffMovementCurrentClass.SumOfintDisabilityStaff,
qryStaffMovementCurrentClass.SumOfintIncreaseStaff
FROM qryStaffMovementCurrentClass;


The SQL for the underlying query is:

SELECT tblClassifications.strClassification, Sum
([tblCurrentYear.intFtrStaff]+ [tblCurrentYear.intPtrStaff]+
[tblCurrentYear.intNetReplaceStaff]) AS intCurrentStaff,
Sum([tblCurrentYear.sngFtrFte]+ [tblCurrentYear.sngPtrFte]+
[tblCurrentYear.sngNetReplaceFte]) AS sngCurrentFte, Sum
(tblCurrentYear.intTerminationStaff) AS
SumOfintTerminationStaff, Sum
(tblCurrentYear.intRetireStaff) AS SumOfintRetireStaff, Sum
(tblCurrentYear.intLeaveStaff) AS SumOfintLeaveStaff, Sum
(tblCurrentYear.intDisabilityStaff) AS
SumOfintDisabilityStaff, Sum
(tblCurrentYear.intIncreaseStaff) AS
SumOfintIncreaseStaff, Sum
(tblCurrentYear.sngTerminationFte) AS
SumOfsngTerminationFte, Sum(tblCurrentYear.sngRetireFte)
AS SumOfsngRetireFte, Sum(tblCurrentYear.sngLeaveFte) AS
SumOfsngLeaveFte, Sum(tblCurrentYear.sngDisabilityFte) AS
SumOfsngDisabilityFte, Sum (tblCurrentYear.sngIncreaseFte)
AS SumOfsngIncreaseFte
FROM tblSites INNER JOIN (tblDepartments INNER JOIN
((((((tblClassifications INNER JOIN tblCurrentYear ON
tblClassifications.lngClassificationID =
tblCurrentYear.lngClassificationID) INNER JOIN tblFiveYear
ON tblCurrentYear.lngCurrentID = tblFiveYear.lngFiveID)
INNER JOIN tblNextYear ON tblCurrentYear.lngCurrentID =
tblNextYear.lngNextID) INNER JOIN tblTenYear ON
tblCurrentYear.lngCurrentID = tblTenYear.lngTenID) INNER
JOIN tblThreeYear ON tblCurrentYear.lngCurrentID =
tblThreeYear.lngThreeID) INNER JOIN tblTwoYear ON
tblCurrentYear.lngCurrentID = tblTwoYear.lngTwoID) ON
tblDepartments.lngDepartmentID =
tblCurrentYear.lngDepartmentID) ON tblSites.lngSiteID =
tblCurrentYear.lngSiteID
GROUP BY tblClassifications.strClassification;

THANK YOU!!!
Claire
Click to expand...


.
Click to expand...
 
I don't have any idea why the query would cause bloat. Try create a new
blank mdb and import all the objects into it.

--
Duane Hookom
MS Access MVP


Hi Duane:

I think my normalization is OK. intRetireStaff is actually
the number of employees that are planning to retire at the
end of the year. I ran the Performance Analyzer anyway and
it was happy with everything.

Thanks very much for the code, I have been playing with it
for the last couple of days to make sure that it was
pulling the correct data. It does exactly what I needed,
but I had to change it a little bit:

SELECT SumOfintRetireStaff, "Retire", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION SELECT SumOfintTerminationStaff, "Termination",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION SELECT SumOfintLeaveStaff, "Leave", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION SELECT SumOfintDisabilityStaff, "Disability",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION SELECT SumOfintIncreaseStaff, "Increase", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0
ORDER BY strClass;

My only other concern is this. When I added the SQL query
to the database the size of my database blew up from 200
MB to 300 MB! This is the only thing I have played with
and compacting has no effect. I need to recreate this
query for Department and Site data and then for staff
projections for the next 10 years, this database will be
massive!!!!!

Any idea why the size went through the roof?!?!

Thanks for all of your help Duane
Claire
-----Original Message-----
Part of your issue is un-normalized tables. You are storing an employee
status as a field name ie "intRetireStaff". I think you can get around this
by creating a union query that filters out 0s.
SELECT SumOfintTerminationStaff as NumOf, "Terminated" as Status
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION
SELECT SumOfintRetireStaff, "Retired"
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION
SELECT SumOfintLeaveStaff, "Leave"
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION
Select SumOfintDisabilityStaff, "Disabled"
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION
SELECT SumOfintIncreaseStaff, "Increase"
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0;
Use this query as your Row Source.

--
Duane Hookom
MS Access MVP


Hi

I am trying to create pie charts that have the data labels
and percentages visible, most of the time.

My problem is the fact that if a data value for my pie
chart is 0, the 0 shows up with its corresponding label at
the top of the pie. If more than one value for the same
record is 0, then the labels stack on top of each other.

What I would like is the 0 values and labels to not show
up at all for that record. Column (bar) charts hide values
and labels, why don't pies?

I have set the properties for the underlying queries so
that the 0 data values do not show in the queries, but
that is not following through to the pie.

I have tried IIF with unsuccessful results so any help is
huge.

My SQl for the pie is:

SELECT Null AS Expr1,
qryStaffMovementCurrentClass.SumOfintTerminationStaff,
qryStaffMovementCurrentClass.SumOfintRetireStaff,
qryStaffMovementCurrentClass.SumOfintLeaveStaff,
qryStaffMovementCurrentClass.SumOfintDisabilityStaff,
qryStaffMovementCurrentClass.SumOfintIncreaseStaff
FROM qryStaffMovementCurrentClass;


The SQL for the underlying query is:

SELECT tblClassifications.strClassification, Sum
([tblCurrentYear.intFtrStaff]+ [tblCurrentYear.intPtrStaff]+
[tblCurrentYear.intNetReplaceStaff]) AS intCurrentStaff,
Sum([tblCurrentYear.sngFtrFte]+ [tblCurrentYear.sngPtrFte]+
[tblCurrentYear.sngNetReplaceFte]) AS sngCurrentFte, Sum
(tblCurrentYear.intTerminationStaff) AS
SumOfintTerminationStaff, Sum
(tblCurrentYear.intRetireStaff) AS SumOfintRetireStaff, Sum
(tblCurrentYear.intLeaveStaff) AS SumOfintLeaveStaff, Sum
(tblCurrentYear.intDisabilityStaff) AS
SumOfintDisabilityStaff, Sum
(tblCurrentYear.intIncreaseStaff) AS
SumOfintIncreaseStaff, Sum
(tblCurrentYear.sngTerminationFte) AS
SumOfsngTerminationFte, Sum(tblCurrentYear.sngRetireFte)
AS SumOfsngRetireFte, Sum(tblCurrentYear.sngLeaveFte) AS
SumOfsngLeaveFte, Sum(tblCurrentYear.sngDisabilityFte) AS
SumOfsngDisabilityFte, Sum (tblCurrentYear.sngIncreaseFte)
AS SumOfsngIncreaseFte
FROM tblSites INNER JOIN (tblDepartments INNER JOIN
((((((tblClassifications INNER JOIN tblCurrentYear ON
tblClassifications.lngClassificationID =
tblCurrentYear.lngClassificationID) INNER JOIN tblFiveYear
ON tblCurrentYear.lngCurrentID = tblFiveYear.lngFiveID)
INNER JOIN tblNextYear ON tblCurrentYear.lngCurrentID =
tblNextYear.lngNextID) INNER JOIN tblTenYear ON
tblCurrentYear.lngCurrentID = tblTenYear.lngTenID) INNER
JOIN tblThreeYear ON tblCurrentYear.lngCurrentID =
tblThreeYear.lngThreeID) INNER JOIN tblTwoYear ON
tblCurrentYear.lngCurrentID = tblTwoYear.lngTwoID) ON
tblDepartments.lngDepartmentID =
tblCurrentYear.lngDepartmentID) ON tblSites.lngSiteID =
tblCurrentYear.lngSiteID
GROUP BY tblClassifications.strClassification;

THANK YOU!!!
Claire
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Duane:

Thanks for all of your help. I will give that a try.

C.
-----Original Message-----
I don't have any idea why the query would cause bloat. Try create a new
blank mdb and import all the objects into it.

--
Duane Hookom
MS Access MVP


Hi Duane:

I think my normalization is OK. intRetireStaff is actually
the number of employees that are planning to retire at the
end of the year. I ran the Performance Analyzer anyway and
it was happy with everything.

Thanks very much for the code, I have been playing with it
for the last couple of days to make sure that it was
pulling the correct data. It does exactly what I needed,
but I had to change it a little bit:

SELECT SumOfintRetireStaff, "Retire", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION SELECT SumOfintTerminationStaff, "Termination",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION SELECT SumOfintLeaveStaff, "Leave", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION SELECT SumOfintDisabilityStaff, "Disability",
strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION SELECT SumOfintIncreaseStaff, "Increase", strClass
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0
ORDER BY strClass;

My only other concern is this. When I added the SQL query
to the database the size of my database blew up from 200
MB to 300 MB! This is the only thing I have played with
and compacting has no effect. I need to recreate this
query for Department and Site data and then for staff
projections for the next 10 years, this database will be
massive!!!!!

Any idea why the size went through the roof?!?!

Thanks for all of your help Duane
Claire
-----Original Message-----
Part of your issue is un-normalized tables. You are storing an employee
status as a field name ie "intRetireStaff". I think you can get around this
by creating a union query that filters out 0s.
SELECT SumOfintTerminationStaff as NumOf, "Terminated" as Status
FROM qryStaffMovementCurrentClass
WHERE SumOfintTerminationStaff >0
UNION
SELECT SumOfintRetireStaff, "Retired"
FROM qryStaffMovementCurrentClass
WHERE SumOfintRetireStaff >0
UNION
SELECT SumOfintLeaveStaff, "Leave"
FROM qryStaffMovementCurrentClass
WHERE SumOfintLeaveStaff >0
UNION
Select SumOfintDisabilityStaff, "Disabled"
FROM qryStaffMovementCurrentClass
WHERE SumOfintDisabilityStaff > 0
UNION
SELECT SumOfintIncreaseStaff, "Increase"
FROM qryStaffMovementCurrentClass
WHERE SumOfintIncreaseStaff >0;
Use this query as your Row Source.

--
Duane Hookom
MS Access MVP


Hi

I am trying to create pie charts that have the data labels
and percentages visible, most of the time.

My problem is the fact that if a data value for my pie
chart is 0, the 0 shows up with its corresponding
Click to expand...
label
at
the top of the pie. If more than one value for the same
record is 0, then the labels stack on top of each other.

What I would like is the 0 values and labels to not show
up at all for that record. Column (bar) charts hide values
and labels, why don't pies?

I have set the properties for the underlying queries so
that the 0 data values do not show in the queries, but
that is not following through to the pie.

I have tried IIF with unsuccessful results so any
Click to expand...
help
is
huge.

My SQl for the pie is:

SELECT Null AS Expr1,
qryStaffMovementCurrentClass.SumOfintTerminationStaff,
qryStaffMovementCurrentClass.SumOfintRetireStaff,
qryStaffMovementCurrentClass.SumOfintLeaveStaff,
qryStaffMovementCurrentClass.SumOfintDisabilityStaff,
qryStaffMovementCurrentClass.SumOfintIncreaseStaff
FROM qryStaffMovementCurrentClass;


The SQL for the underlying query is:

SELECT tblClassifications.strClassification, Sum
([tblCurrentYear.intFtrStaff]+ [tblCurrentYear.intPtrStaff]+
[tblCurrentYear.intNetReplaceStaff]) AS intCurrentStaff,
Sum([tblCurrentYear.sngFtrFte]+ [tblCurrentYear.sngPtrFte]+
[tblCurrentYear.sngNetReplaceFte]) AS sngCurrentFte, Sum
(tblCurrentYear.intTerminationStaff) AS
SumOfintTerminationStaff, Sum
(tblCurrentYear.intRetireStaff) AS
Click to expand...
SumOfintRetireStaff,
Sum
(tblCurrentYear.intLeaveStaff) AS SumOfintLeaveStaff, Sum
(tblCurrentYear.intDisabilityStaff) AS
SumOfintDisabilityStaff, Sum
(tblCurrentYear.intIncreaseStaff) AS
SumOfintIncreaseStaff, Sum
(tblCurrentYear.sngTerminationFte) AS
SumOfsngTerminationFte, Sum (tblCurrentYear.sngRetireFte)
AS SumOfsngRetireFte, Sum (tblCurrentYear.sngLeaveFte) AS
SumOfsngLeaveFte, Sum
Click to expand...
(tblCurrentYear.sngDisabilityFte)
AS
SumOfsngDisabilityFte, Sum (tblCurrentYear.sngIncreaseFte)
AS SumOfsngIncreaseFte
FROM tblSites INNER JOIN (tblDepartments INNER JOIN
((((((tblClassifications INNER JOIN tblCurrentYear ON
tblClassifications.lngClassificationID =
tblCurrentYear.lngClassificationID) INNER JOIN tblFiveYear
ON tblCurrentYear.lngCurrentID = tblFiveYear.lngFiveID)
INNER JOIN tblNextYear ON tblCurrentYear.lngCurrentID =
tblNextYear.lngNextID) INNER JOIN tblTenYear ON
tblCurrentYear.lngCurrentID = tblTenYear.lngTenID) INNER
JOIN tblThreeYear ON tblCurrentYear.lngCurrentID =
tblThreeYear.lngThreeID) INNER JOIN tblTwoYear ON
tblCurrentYear.lngCurrentID = tblTwoYear.lngTwoID) ON
tblDepartments.lngDepartmentID =
tblCurrentYear.lngDepartmentID) ON tblSites.lngSiteID =
tblCurrentYear.lngSiteID
GROUP BY tblClassifications.strClassification;

THANK YOU!!!
Claire



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top