View Single Post
  #4   Report Post  
Ron Coderre
 
Posts: n/a
Default

A reciprocal apology for the delay in responding: I was away on vacation for
a week.

OK...Here goes:
I set up a worksheet with a range named rngPickTimes.
That range has 4 columns:
A1: UserID
B1: OrderID
C1: StartTime
D1: EndTime

Under those column headings I created fictitious picker names, order IDs,
and pick start/end times. To test satisfaction of your request, I entered the
same pickers for the same order IDs for varying start and end times.

I then saved the workbook as PickTime.XLS and closed it.

In a new workbook I selected
DataImport External DataNew Database Query
Data Source: Excel Files (I navigated to the PickTime.xls and selected the
rngPickTimes range name)

Then I selected all columns, accepted all defaults, and opted for "View data
or edit query in Microsoft Query".

I clicked the [SQL] button to view the SQL code and edited it to be the
following:
SELECT
TheData.UserID,
TheData.OrderID,
sum(EndTime-StartTime) AS 'Duration'
FROM
`C:\Analysis\ForumHelp\PickTime`.rngPickTimes TheData
GROUP BY TheData.OrderID, TheData.UserID
ORDER BY TheData.OrderID, TheData.UserID

That returned Total Pick Time by UserID for each Order

Does that help?
------
Regards,
Ron