Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
Database Query -- Data Type Mismatch | Excel Discussion (Misc queries) | |||
Excel2000 ODBC query oddity | Excel Discussion (Misc queries) | |||
Query of External Data | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |