need help with a multi Select query 1

[DougP]

I need help with a query where I have billable and non billable hours.
I would like the query to return a field with the total of billable hours says 35 and then the total hours say is 40 and the percent of billable which is 40/35 * 100 or 87.5

Name, Week End Date, Billable Hrs, Total Hours, Percent of Billable
Doug 1/25/08 35 40 87.5

SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].WeekEndDate, Sum([TimeSheet Details].Hours) AS SumOfHours
FROM [TimeSheet Details]
GROUP BY [TimeSheet Details].[Employee Name], [TimeSheet Details].WeekEndDate
HAVING ((([TimeSheet Details].[Employee Name])="xxxx") AND (([TimeSheet Details].WeekEndDate)=#1/25/2008#));

TIA


DougP

 

[HarleyQuinn]

Where is the Billable Hours field?

This would work with a set number:

SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].WeekEndDate, "35" as [Billable Hours], (35/(Sum([TimeSheet Details].Hours))*100) AS [Total Hours], Sum([TimeSheet Details].Hours) AS SumOfHours

You casn then just sub in the field name for the 35's in the SELECT.

I've done it like this as I don't know the structure of your table(s) and where your required field comes from.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[DougP]

Sorry I had the wrong SQL statement. here is a better one.
The Billable hours is a yes/no field.
Add all the billable hours, then add all the hours both billable and not for Total Hours, then divide billable by total for a Percent of Billable. I would like that all to happen in one SQL statement if possible.

SELECT [Employee Name], WeekEndDate, Sum(Hours) AS [Billable Hrs], Billable 
FROM [TimeSheet Details]
GROUP BY [Employee Name], WeekEndDate, Billable
HAVING ((([Employee Name])=[Forms]![frmReviewTimeSheets]![TimeSheet Details subform].[Form]![Employee Name]) AND ((WeekEndDate)=[Forms]![frmReviewTimeSheets]![TimeSheet Details subform].[Form]![WeekEndDate]) AND ((Billable)=True));

DougP

 

[dhookom]

Try:

SELECT [Employee Name], WeekEndDate, Sum(Hours) AS HrsTotal, Sum(Abs(Billable) * Hours) AS  HrsBillable,
Sum(Abs(Billable) * Hours)/Sum(Hours) As PctBillable
FROM [TimeSheet Details]
WHERE [Employee Name]=[Forms]![frmReviewTimeSheets]![TimeSheet Details subform].[Form]![Employee Name] AND WeekEndDate=[Forms]![frmReviewTimeSheets]![TimeSheet Details subform].[Form]![WeekEndDate]
GROUP BY [Employee Name], WeekEndDate;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[DougP]

OMG it works!!!!
Have a star!!!!
Thanks Duane

BTW how did you get the Access MVP? is that taking certifications tests?
Which ones?

DougP

 

[dhookom]

DougP,
Microsoft awards MVP status to people who have provided support of MS products. There is a difference between Tek-Tips MVPs and Microsoft MVPs with some of us being recognized as both.

You can read about Microsoft MVPs at

http://mvp.support.microsoft.com/default.aspx?lc=1033.

Duane MS Access MVP
Now help me support United Cerebral Palsy