Extracting Billable and NonBillable Total Hours in Report

[kg3]

I've been trying for awhile to acquire the correct information and have not been successful. I will start with the basics and I am hoping you all will be able to help!

I have 6 fields in my table:
[SO] Service Orders
[RepName] Last name of engineer
[DateofServ] Date
[TimelogReason] 4 options (Labor,Network)- which is billable...(PreSales,NoBill) which are nonbillable
[LogHours] Hours used
[LogMinutes] Minutes used.

In my report I have to Group by Last name of the engineer and report from the timelogs how many total hours he had for the week that were billable and how many were nonbillable.

Example:

Smith
12-12-16 Labor 4.5 hours
12-12-16 Presales 3.5 hours
12-13-16 Network 2 hours
12-13-16 NoBill 1 hour
12-13-16 Presales 5 hours



Total Nonbillable Time - 9.5 hours
Total Billable Time - 6.5 hours

I can get everything to display in my report correctly with a query and by Grouping on [RepName]. However, I am having issues totalling on Billable and Nonbillable time in my footer.

Can you help my with a an iif statement that will accomplish this?
I appreciate any help you can give me.
Thank you!

[ranman256]

You don't need IIF,
in the table of groups,you need a 2nd field BILLABLE (true/false)
Labor, true
Presales,false


Join this to your data table on group,this will sort the billables.

[Bulzie]

In your TotalNonbillable field footer, could you just do = [NoBill] + [Presales]

Or in your query, create a new column for TotalBillable and add the calculations you used to get your values for Labor and Network and so same for the 2 NonBillible fields?

[kg3]

Thank you for your help!
ranman256 - I'm relatively new to access and am confused...
How do I make a 2nd field BILLABLE with true/false?
Do I do this in my query?

[Bulzie]

Maybe few more ways:

1. using DSum as control source in Totals field.= DSum("[NoBill] + [Presales]", "tblHours", "[RepName] = '" & me.RepName "'")

2. You said your query works to give you the total hours by those groups, so you could create a 2nd query, pull in the first query as its source, add all the fields from query1 and add 2 new fields where you add the NoBill and Presales fields into NonBillableTotal and do same for the billable ones.

[kg3]

Thank you all for your advice and help. The 2nd query worked!