Can't calculate an Average in a query 3

[MattGreer]

Related to a recent post, I'm investigating the "billability" of my employees. I have five different folks that turn in timesheets with a few categories on them where they allocate their time. I have a query that works just fine that calculates, on a weekly basis, what their billability is. The query is:

[tt]SELECT tblPeople.FirstName, IIf(Billable=0,Format(0,"Percent"),Format([Billable]/([Billable]+[Overhead]),"Percent")) AS BillRate
FROM tblPeople INNER JOIN tblHours ON tblPeople.ID = tblHours.PersonID
ORDER BY tblPeople.FirstName;
[/tt]

The IIF statement is there in case they were on vacation the whole week to return a zero, otherwise I get a #Num! and I can't perform any additional calculations. Or at least, that's what I think.

What I want to do is average the billability for each person. I keep trying to figure out a way to get Access 2010 to average the percentages but Access throws up the error "data type mismatch". Not sure what to do.

Thanks!!


Matt

 

[SkipVought]

hi,

Does EVERY row have a numeric amount of BOTH [Billable] and [Overhead]?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

but Access throws up the error "data type mismatch".
On which statement ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

IIf(Billable=0,Format(0,"Percent"),Format([Billable]/([Billable]+[Overhead]),"Percent"))

The format function returns a string. Cannot average a string.

 

[MajP]

apply formatting at the form, or datasheet level.

 

[MattGreer]

Skip:

Every employee has several categories for the record for a given week. I set the table up such that it pre-populates the entries as zero. So every record will have a value. The problem is dividing by zero, of course. That being said, not every row will have a value greater than zero for [Billable] and [Overhead]. There's another field for time off called [PTO] which is not included in this equation (don't want to penalize employees for taking time off, they should!) which may be 40, meaning they took the entire week off.


PHV:

It throws up the error on the statement for 'Billrate'. Looks like MajP explained it, and when I copied and pasted the information into Excel, Excel told me that the numbers were stored as text. I removed the formatting and it fixed the issue, and now I'm getting averages.

MajP:

Thanks! You fixed the issue! Now I need to go figure out how to apply the formatting after the fact in a report.

Thanks!!


Matt

 

[MajP]

If the field is numeric then in the control properties go to "format" tab and choose percent.

 

[SkipVought]

If you aer doing the report in Excel, then your query could be...

SELECT tblPeople.FirstName, [highlight #FCE94F]IIf(Billable=0,0,[Billable]/([Billable]+[Overhead])[/highlight] AS BillRate
FROM tblPeople INNER JOIN tblHours ON tblPeople.ID = tblHours.PersonID
ORDER BY tblPeople.FirstName;

...ASSUMING that Billable is NUMERIC and Overhead is NUMERIC.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MattGreer]

I'm trying to decide whether or not to pull this stuff into Excel. I kind of like to keep everything in one platform, but connecting Excel to Access is really, really easy. And the graphs/charts are much better also. Regardless, yep, Skip, that's what I did, thank you for that verification!

Thanks!!


Matt