MS Query grouping
 

Pick start time = 20/05/05 06:00:00
Pick endtime = 20/05/05 06:01:15
Order number = 123456
User id = DS123654

Each item picked has its Task start time and end time recorded on the DB (as
above). I need to get the starttime and Endtime for the whole order (Min &
Max) but by Order and User.
Is this possible in MSQuery and how?

Dean

How would you want to handle the situation where UserID DS123654 picks some
items from 8:00 AM to 8:05 AM, then picks another item from 10:00 AM to 10:01
AM? Wouldn't it look like that user spent from 8:00 AM to 10:01 AM picking
items?

Or are you really looking to see cummulative time by user spent on the order?
8:05 less 8:00 = 5 minutes
10:01 less 10:00 = 1 minute
Cummulative = 6 minutes

--
Regards,
Ron

Ron,

Sorry for the delay. Yes I want a cummulative answer!
Dean

"Ron Coderre" wrote:

How would you want to handle the situation where UserID DS123654 picks some
items from 8:00 AM to 8:05 AM, then picks another item from 10:00 AM to 10:01
AM? Wouldn't it look like that user spent from 8:00 AM to 10:01 AM picking
items?

Or are you really looking to see cummulative time by user spent on the order?
8:05 less 8:00 = 5 minutes
10:01 less 10:00 = 1 minute
Cummulative = 6 minutes

--
Regards,
Ron

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

Ron,

I'll recripicate your reciprocal apology as I'm now on holiday. Looks sound
though. Will give it a go when I get back but until then, Thanks
Dean



"Ron Coderre" wrote:

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