Query: Billable Hours vs. Non-Billable Hours

[Schmidtnikov]

Looking for a quick formula that I cannot seem to figure out. I have a
database of my time, on a week ending basis.

Table includes:
RecordID
Week Ending
Project#
WBS#
Hours
Notes

I am trying to create a query that will show me a week ending summary of
billable time vs. non-billable.

All non-billable time is identified by the project #88123, all other project
#'s are billable.

What do I need to do, so my query will display results looking like:

Week Ending Non-Billable Billable
2/11/05 38.5 1.5
2/18/05 35 5

Thanks for your help

 

[Duane Hookom]

Try a query with this SQL view:

SELECT [Week Ending], Sum(Hours * Abs([Project#]=88123)) as NonBillable,
Sum(Hours * Abs([Project#]<>88123)) as Billable
FROM [includes]
GROUP BY [Week Ending];

 

[Billy B]

When I try to run it I get a 'not part of an aggregate function' message.
TSeconds is a calculated (sum) expression in the query if that makes any
difference.

Try a query with this SQL view:

SELECT [Week Ending], Sum(Hours * Abs([Project#]=88123)) as NonBillable,
Sum(Hours * Abs([Project#]<>88123)) as Billable
FROM [includes]
GROUP BY [Week Ending];


--
Duane Hookom
MS Access MVP

Looking for a quick formula that I cannot seem to figure out. I have a
database of my time, on a week ending basis.

Table includes:
RecordID
Week Ending
Project#
WBS#
Hours
Notes

I am trying to create a query that will show me a week ending summary of
billable time vs. non-billable.

All non-billable time is identified by the project #88123, all other
project
#'s are billable.

What do I need to do, so my query will display results looking like:

Week Ending Non-Billable Billable
2/11/05 38.5 1.5
2/18/05 35 5

Thanks for your help

 

[Duane Hookom]

Please post the complete SQL that you used.

--
Duane Hookom
MS Access MVP

When I try to run it I get a 'not part of an aggregate function' message.
TSeconds is a calculated (sum) expression in the query if that makes any
difference.

Try a query with this SQL view:

SELECT [Week Ending], Sum(Hours * Abs([Project#]=88123)) as NonBillable,
Sum(Hours * Abs([Project#]<>88123)) as Billable
FROM [includes]
GROUP BY [Week Ending];


--
Duane Hookom
MS Access MVP

Looking for a quick formula that I cannot seem to figure out. I have a
database of my time, on a week ending basis.

Table includes:
RecordID
Week Ending
Project#
WBS#
Hours
Notes

I am trying to create a query that will show me a week ending summary
of
billable time vs. non-billable.

All non-billable time is identified by the project #88123, all other
project
#'s are billable.

What do I need to do, so my query will display results looking like:

Week Ending Non-Billable Billable
2/11/05 38.5 1.5
2/18/05 35 5

Thanks for your help