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
|