Should this be done as a subquery or how else should i do this? 2

[Rmck87]

I have a table with these columns in it that i need:

Time_Entry
Hours_Actual Billable_Flag Date_Start
4.0 0 7/30/2002
.25 -1 12/11/2002
8.0 -1 12/30/2002
1.5 -1 1/11/2002
6.75 0 1/11/2002
3.0 0 4/30/2002
3.25 -1 6/11/2002
8.0 0 8/30/2002
8.0 0 8/30/2002
8.0 -1 8/30/2002


Random hours, and a billable flag. The flag is based on a yes/no data type. What i want to do is:

1. Get the total amount of hours for each time period, (whenever the date_start fields are the same add up the number of actual_hours)

2. Whenever the billable flag is -1 or yes (Billable), i want to add up the hours for that, and place them on the same line as the criteria for #1.

3. Whenever the billable flag is 0 or no (NonBillable), i want to add up the hours for that, and place them on the same line as the criteria for #1, and #2.

So i should get something like this.....

Actual_Hours Billable NonBillable Date_Start
4.0 0.0 4.0 7/30/2002
0.25 0.25 0.0 12/11/2002
8.0 8.0 0.0 12/30/2002
8.25 1.5 6.75 1/11/2002
3.0 0.0 3.0 4/30/2002
3.25 3.25 0.0 6/11/2002
24.0 8.0 16.0 8/30/2002


Thanks in advance!!!

-Ryan




One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

I appologize for the double post.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Norris68]

SELECT SUM(Hours_Actual) AS Hours_Actual, SUM(IIF(Billable_Flag, Hours_Actual, 0)) AS Billable, SUM(IIF(Billable_Flag, 0, Hours_Actual)) AS NonBillable, Date_Start
FROM Time_Entry
GROUP BY Date_Start

... should do the trick.

 

[Rmck87]

YES!!! That works. Could i ask you to do a favor for me? Could you explain what this does? Because i have never delt with this before and it would be great if i could understand this. So i can change things around if need be! Thanks anyways!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[lespaul]

Add the Hours_Actual:
SUM(Hours_Actual) AS Hours_Actual
Add Hours_Actual if Billable_Flag is 0:
SUM(IIF(Billable_Flag, Hours_Actual, 0)) AS Billable
Add Hours_Actual if Billable_Flag is 1:
SUM(IIF(Billable_Flag, 0, Hours_Actual)) AS NonBillable
Get the Date of Hours:
Date_Start

FROM Time_Entry

Arrange results by Date: GROUP BY Date_Start

(The AS in each field names the column in the result set)

HTH

Leslie

 

[Rmck87]

What do the zeros ( 0 ) mean in the IIF clause's ? Also how do you know that it is saying if the billable flag is 1 or 0 ? Because it doesn't specifically say 0 or 1 does it?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Norris68]

The IIF function tests a boolean condition. This could be in the form IIF(A=1,True,False), but as you have a yes/no (i.e. boolean) field I have tested on that. The syntax if the IIF function is IIF(boolean_statement,value_if_true,value_if_false), returning the appropriate value based on the boolean statement. In your case I have tested the yes/no Billable_Flag field and used the Hours_Taken if flagged as billable, zero if not; then summed the result. I have done similar for the non-billable time, swapping the value_if_true and value_if_false values.

In effect I am testing IIF(Billable_Flag = True, etc. however this would involve an extra and unnecessary step in the calculation. When performing boolean evaluations Access will take a zero to be false, all other values to be true.

As lespaul says, I then aliased the columns to give them the column names used in your example. By default, Access would have probably called the columns Expr1, Expr2, etc.

A further point to note. The query is an aggregate query - we are using aggregate functions (SUM, MAX, AVG, etc.) and a GROUP BY clause. Any field in the SELECT list that does not use an aggregate function must appear in the GROUP BY clause. You may, however, use columns within the WHERE clause without having to GROUP on it too, even when not using an aggregate function.

Thanks for the star

 

[Rmck87]

Well, thank you very much for all the help. I appreciate it. And thanks to you two, i understand those IIF statements now. Thanks alot.

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.