More efficient way of calculating my percentages?!

[Guest]

I am trying to find an efficient way of calculating percentages in either a
query or a report.
My database holds information about requests for support. It has a table
‘support’ which lists all the requests which come in, the date they were
received, the date the request was actioned etc. I use the workingdays
function from the Access Web to calculate how many working days it takes to
provide support.
I have queries set up which calculate the number of requests which were
actioned within the target time (<4 days) and those which didn’t meet the
target (>3). More queries count the totals of both these queries. I then have
ANOTHER (!) query which brings them all together to calculate the total
number of requests we received and how many requests met or didn’t meet the
target, for the purposes of calculating a percentage.
The sql (though I can’t write sql) finally looks like this:
SELECT ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests] AS [% Support requests met target],
ColinSupportNotMetCount.Failledtomeet, [failledtomeet]/[allrequests] AS [%
Support requests failed targets]
FROM ColinSupportAllCount, ColinSupportMetCount, ColinSupportNotMetCount,
ColinSupportAll
GROUP BY ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests], ColinSupportNotMetCount.Failledtomeet,
[failledtomeet]/[allrequests];
My question is this: Is there a more efficient way of doing this, either in
a query or a perhaps using calculations in a report? I don’t really want to
have to build all these separate queries. I’m sure there must be a better
way. My ignorance of Access (or perhaps maths!) may be at fault. Any advice
would be much appreciated.

 

[kingston via AccessMonster.com]

Try using the function DCount():

DCount("*","[DataSet]","[Days2Process]<4")
DCount("*","[DataSet]","[Days2Process]>3")
DCount("*","[DataSet]")

This should return the numbers for cases that met target, those that didn't
meet the target, and all cases.

I am trying to find an efficient way of calculating percentages in either a
query or a report.
My database holds information about requests for support. It has a table
‘support’ which lists all the requests which come in, the date they were
received, the date the request was actioned etc. I use the workingdays
function from the Access Web to calculate how many working days it takes to
provide support.
I have queries set up which calculate the number of requests which were
actioned within the target time (<4 days) and those which didn’t meet the
target (>3). More queries count the totals of both these queries. I then have
ANOTHER (!) query which brings them all together to calculate the total
number of requests we received and how many requests met or didn’t meet the
target, for the purposes of calculating a percentage.
The sql (though I can’t write sql) finally looks like this:
SELECT ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests] AS [% Support requests met target],
ColinSupportNotMetCount.Failledtomeet, [failledtomeet]/[allrequests] AS [%
Support requests failed targets]
FROM ColinSupportAllCount, ColinSupportMetCount, ColinSupportNotMetCount,
ColinSupportAll
GROUP BY ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests], ColinSupportNotMetCount.Failledtomeet,
[failledtomeet]/[allrequests];
My question is this: Is there a more efficient way of doing this, either in
a query or a perhaps using calculations in a report? I don’t really want to
have to build all these separate queries. I’m sure there must be a better
way. My ignorance of Access (or perhaps maths!) may be at fault. Any advice
would be much appreciated.

 

[Guest]

Try this ----
SELECT Count(Support.Received) AS Allrequests,
Sum(IIf(DateDiff("d",[Received],[Actioned])<4,1,0))/Count([Received])*100 AS
Met_Target,
Sum(IIf(DateDiff("d",[Received],[Actioned])>=4,1,0))/Count([Received])*100 AS
Failed_to_meet_Target
FROM Support
WHERE (((Support.Received) Between #6/1/2007# And #6/30/2007#));

 

[C Tate]

I am not sure how this works. Could you explain a bit more fully?

Try using the function DCount():

DCount("*","[DataSet]","[Days2Process]<4")
DCount("*","[DataSet]","[Days2Process]>3")
DCount("*","[DataSet]")

This should return the numbers for cases that met target, those that
didn't
meet the target, and all cases.

I am trying to find an efficient way of calculating percentages in either
a
query or a report.
My database holds information about requests for support. It has a table
'support' which lists all the requests which come in, the date they were
received, the date the request was actioned etc. I use the workingdays
function from the Access Web to calculate how many working days it takes
to
provide support.
I have queries set up which calculate the number of requests which were
actioned within the target time (<4 days) and those which didn't meet the
target (>3). More queries count the totals of both these queries. I then
have
ANOTHER (!) query which brings them all together to calculate the total
number of requests we received and how many requests met or didn't meet
the
target, for the purposes of calculating a percentage.
The sql (though I can't write sql) finally looks like this:
SELECT ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests] AS [% Support requests met target],
ColinSupportNotMetCount.Failledtomeet, [failledtomeet]/[allrequests] AS [%
Support requests failed targets]
FROM ColinSupportAllCount, ColinSupportMetCount, ColinSupportNotMetCount,
ColinSupportAll
GROUP BY ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests], ColinSupportNotMetCount.Failledtomeet,
[failledtomeet]/[allrequests];
My question is this: Is there a more efficient way of doing this, either
in
a query or a perhaps using calculations in a report? I don't really want
to
have to build all these separate queries. I'm sure there must be a better
way. My ignorance of Access (or perhaps maths!) may be at fault. Any
advice
would be much appreciated.

 

[C Tate]

Truly amazing. I got this to work with a bit of fiddling though there is no
way I could replicate this myself. I don't understand all that sum/iif
stuff! The only thing is I think it seems to use the datediff function which
counts all days. I don't want to count weekends which is why I used the
workingdays function. Anyway, much appreciated.

Try this ----
SELECT Count(Support.Received) AS Allrequests,
Sum(IIf(DateDiff("d",[Received],[Actioned])<4,1,0))/Count([Received])*100
AS
Met_Target,
Sum(IIf(DateDiff("d",[Received],[Actioned])>=4,1,0))/Count([Received])*100
AS
Failed_to_meet_Target
FROM Support
WHERE (((Support.Received) Between #6/1/2007# And #6/30/2007#));

--
KARL DEWEY
Build a little - Test a little

I am trying to find an efficient way of calculating percentages in either
a
query or a report.
My database holds information about requests for support. It has a table
'support' which lists all the requests which come in, the date they were
received, the date the request was actioned etc. I use the workingdays
function from the Access Web to calculate how many working days it takes
to
provide support.
I have queries set up which calculate the number of requests which were
actioned within the target time (<4 days) and those which didn't meet the
target (>3). More queries count the totals of both these queries. I then
have
ANOTHER (!) query which brings them all together to calculate the total
number of requests we received and how many requests met or didn't meet
the
target, for the purposes of calculating a percentage.
The sql (though I can't write sql) finally looks like this:
SELECT ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests] AS [% Support requests met target],
ColinSupportNotMetCount.Failledtomeet, [failledtomeet]/[allrequests] AS
[%
Support requests failed targets]
FROM ColinSupportAllCount, ColinSupportMetCount, ColinSupportNotMetCount,
ColinSupportAll
GROUP BY ColinSupportAllCount.Allrequests,
ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests], ColinSupportNotMetCount.Failledtomeet,
[failledtomeet]/[allrequests];
My question is this: Is there a more efficient way of doing this, either
in
a query or a perhaps using calculations in a report? I don't really want
to
have to build all these separate queries. I'm sure there must be a better
way. My ignorance of Access (or perhaps maths!) may be at fault. Any
advice
would be much appreciated.

 

[Guest]

Create a table named CountNumber with integer field named CountNUM containing
0 (zero) throught your maximum number of calendar days. Use these three
queries to get your answer.

C_Tate_X --
SELECT Support.Item, Support.Received, DateAdd("d",[CountNUM],[Received]) AS
Work_day, Support.Actioned
FROM Support, CountNumber
WHERE (((DateAdd("d",[CountNUM],[Received]))<=[Actioned]) AND
((Weekday(DateAdd("d",[CountNUM],[Received]))) Between 2 And 6))
ORDER BY Support.Item, Support.Received, DateAdd("d",[CountNUM],[Received]);

C_Tate_Y ---
SELECT C_Tate_X.Item, Count(C_Tate_X.Work_day) AS Workingdays
FROM C_Tate_X
GROUP BY C_Tate_X.Item;

SELECT Sum(IIf([Workingdays]<4,1,0))/Count([Item])*100 AS Met_Target,
Sum(IIf([Workingdays]>=4,1,0))/Count([Item])*100 AS Failed_to_meet_Target
FROM C_Tate_Y;

--
KARL DEWEY
Build a little - Test a little

Truly amazing. I got this to work with a bit of fiddling though there is no
way I could replicate this myself. I don't understand all that sum/iif
stuff! The only thing is I think it seems to use the datediff function which
counts all days. I don't want to count weekends which is why I used the
workingdays function. Anyway, much appreciated.

Try this ----
SELECT Count(Support.Received) AS Allrequests,
Sum(IIf(DateDiff("d",[Received],[Actioned])<4,1,0))/Count([Received])*100
AS
Met_Target,
Sum(IIf(DateDiff("d",[Received],[Actioned])>=4,1,0))/Count([Received])*100
AS
Failed_to_meet_Target
FROM Support
WHERE (((Support.Received) Between #6/1/2007# And #6/30/2007#));

--
KARL DEWEY
Build a little - Test a little

I am trying to find an efficient way of calculating percentages in either
a
query or a report.
My database holds information about requests for support. It has a table
'support' which lists all the requests which come in, the date they were
received, the date the request was actioned etc. I use the workingdays
function from the Access Web to calculate how many working days it takes
to
provide support.
I have queries set up which calculate the number of requests which were
actioned within the target time (<4 days) and those which didn't meet the
target (>3). More queries count the totals of both these queries. I then
have
ANOTHER (!) query which brings them all together to calculate the total
number of requests we received and how many requests met or didn't meet
the
target, for the purposes of calculating a percentage.
The sql (though I can't write sql) finally looks like this:
SELECT ColinSupportAllCount.Allrequests, ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests] AS [% Support requests met target],
ColinSupportNotMetCount.Failledtomeet, [failledtomeet]/[allrequests] AS
[%
Support requests failed targets]
FROM ColinSupportAllCount, ColinSupportMetCount, ColinSupportNotMetCount,
ColinSupportAll
GROUP BY ColinSupportAllCount.Allrequests,
ColinSupportMetCount.Mettarget,
[mettarget]/[allrequests], ColinSupportNotMetCount.Failledtomeet,
[failledtomeet]/[allrequests];
My question is this: Is there a more efficient way of doing this, either
in
a query or a perhaps using calculations in a report? I don't really want
to
have to build all these separate queries. I'm sure there must be a better
way. My ignorance of Access (or perhaps maths!) may be at fault. Any
advice
would be much appreciated.