First of all, Hello everyone!

I have been attempting to create a simple SQL query that gives me a list of Jobs and the amount of time spent on that job.

What I have so far is....

SELECT TimeTracking.CustomerRefFullName, TimeTracking.DurationMinutes
FROM Customer Customer, TimeTracking TimeTracking
WHERE Customer.ListID = TimeTracking.CustomerRefListID AND ((TimeTracking.ItemServiceRefFullName Like '%/REG%') AND (Customer.JobStatus='InProgress') AND (TimeTracking.BillableStatus='Billable'))
ORDER BY TimeTracking.CustomerRefFullName

The results look like... (truncated version, my list is 295 records.)

CHOICE/RAINBOW CITY:CH6478 105
CHOICE/RAINBOW CITY:CH6478 480
CHOICE/RAINBOW CITY:CH6478 480
CHOICE/RAINBOW CITY:CH6577 300
CHOICE/RAINBOW CITY:CH6577 180
CHOICE/RAINBOW CITY:CH6577 300
CHOICE/RAINBOW CITY:CH6577 420

I need this list to show each unique job and the total (sum) of time in hours.

That would look like this... (if  my math is correct.)

CHOICE/RAINBOW CITY:CH6478 17.75
CHOICE/RAINBOW CITY:CH6577 20.00

Can you help?

Paul

Just use GROUP BY instead of ORDER BY and sum the minutes like this:

SELECT TimeTracking.CustomerRefFullName, sum(TimeTracking.DurationMinutes) as "Total Minutes"
FROM Customer Customer, TimeTracking TimeTracking
WHERE Customer.ListID = TimeTracking.CustomerRefListID AND ((TimeTracking.ItemServiceRefFullName Like '%/REG%') AND (Customer.JobStatus='InProgress') AND (TimeTracking.BillableStatus='Billable'))
GROUP BY TimeTracking.CustomerRefFullName

or for hours:

SELECT TimeTracking.CustomerRefFullName, sum(TimeTracking.DurationMinutes /60) as "Total Hours"
FROM Customer Customer, TimeTracking TimeTracking
WHERE Customer.ListID = TimeTracking.CustomerRefListID AND ((TimeTracking.ItemServiceRefFullName Like '%/REG%') AND (Customer.JobStatus='InProgress') AND (TimeTracking.BillableStatus='Billable'))
GROUP BY TimeTracking.CustomerRefFullName

Uh oh... got an error message with that one.

Expected lexical element not found: <Identifier>

I'm using Microsoft Query (11.510.8132) from within Excel 2003

Thanks for your help!

That works in QReportBuilder... but not in MSQuery

Additionally I'm going to need to add another few colums after the Total Hours, which was based on... "(TimeTracking.ItemServiceRefFullName Like '%/REG%')"

One of these additional columns would be for "(TimeTracking.ItemServiceRefFullName Like '%/OT%')"

If I can see how you do one... I'm sure I can do the rest!

Thanks so much for helping me with these simple basic queries that I just am too new to know how to do on my own.

Forgive me...

Sorry, I wasn't clear

The next column would aso be a.....      sum(TimeTracking.DurationMinutes) as "Overtime"

but the where clause would change by... "(TimeTracking.ItemServiceRefFullName Like '%/OT%')"

So how do I do that?

Thanks again.

The simple approach would be to run the overtime as a seperate query.

SELECT TimeTracking.CustomerRefFullName, sum(TimeTracking.DurationMinutes /60) as "Overtime Hours"
FROM Customer Customer, TimeTracking TimeTracking
WHERE Customer.ListID = TimeTracking.CustomerRefListID AND ((TimeTracking.ItemServiceRefFullName Like '%/OT%') AND (Customer.JobStatus='InProgress') AND (TimeTracking.BillableStatus='Billable'))
GROUP BY TimeTracking.CustomerRefFullName

Actually it turns out that combining the two queries is very simple by inserting another group by layer. This allows you to see both Regular (/REG) and Overtime (/OT} hours:-

SELECT TimeTracking.CustomerRefFullName, TimeTracking.ItemServiceRefFullName,
sum(TimeTracking.DurationMinutes /60) as "Total Hours"
FROM Customer Customer, TimeTracking TimeTracking
WHERE Customer.ListID = TimeTracking.CustomerRefListID AND
((TimeTracking.ItemServiceRefFullName Like '%/REG%'
OR (TimeTracking.ItemServiceRefFullName Like '%/OT%')) AND (Customer.JobStatus='InProgress')
AND (TimeTracking.BillableStatus='Billable'))
GROUP BY TimeTracking.CustomerRefFullName, TimeTracking.ItemServiceRefFullName

Hello All,

The ultimate solution to this problem using MS Excel 2003 was to create seperate sheets for each query and then use Excel's VLOOKUP() on a sheet to gather all the data together in the correct column format.

Thanks for your help.