I have a large dataset consisting of start- and endtimes of personel shifts. 
I'd like to make a query that calculates the number of shifts per time period.


I used to perform this task in Excel using sumpruduct or array formulas quite
easily, but I can't figure out a way to to the same in Access. 

Is it possible?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

Post sample data with table and field names plus datatype.
-- 
Build a little, test a little.


"bertusavius via AccessMonster.com" wrote:

> I have a large dataset consisting of start- and endtimes of personel shifts. 
> I'd like to make a query that calculates the number of shifts per time period.
> 
> 
> I used to perform this task in Excel using sumpruduct or array formulas quite
> easily, but I can't figure out a way to to the same in Access. 
> 
> Is it possible?
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
> 
> .
> 

One table, three columns: 

id (autonr) , start time(time/date), end time(time/date)

The preferred ouput would be something like this:

time periode, nr of shifts

ie:
07:00-08:00, 5
08:00;09:00, 8
etc

-- 
Message posted via http://www.accessmonster.com

I do not follow your logic.  A shift is only one hour long?  

Are do you want all start to end time periods broken down into one hour 
increments and then total that?

Post sample data and then example of what that data should produce.

-- 
Build a little, test a little.


"bertusavius via AccessMonster.com" wrote:

> One table, three columns: 
> 
> id (autonr) , start time(time/date), end time(time/date)
> 
> The preferred ouput would be something like this:
> 
> time periode, nr of shifts
> 
> ie:
> 07:00-08:00, 5
> 08:00;09:00, 8
> etc
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 

table of shifts:

id, shiftstart, shiftend
1, 10:00, 19:00
2, 11:00, 20:00
3, 13:00, 15:00


desired query output

timeframe, nr of active shifts
1100-1200, 2
1200-1300, 2
1300-1400, 3

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

Based on your input data this is the results --
1100 - 1200	1200 - 1300	1300 - 1400
2	3	3
All 3 include 1300 as 13:00, 15:00 also encompasses 1300.

SELECT Sum(IIf([shiftstart]<=#12/30/1899 12:0:0# And [shiftend]>=#12/30/1899 
11:0:0#,1,0)) AS [1100 - 1200], Sum(IIf([shiftstart]<=#12/30/1899 13:0:0# And 
[shiftend]>=#12/30/1899#,1,0)) AS [1200 - 1300], 
Sum(IIf([shiftstart]<=#12/30/1899 14:0:0# And [shiftend]>=#12/30/1899 
13:0:0#,1,0)) AS [1300 - 1400]
FROM Shifts;

-- 
Build a little, test a little.


"bertusavius via AccessMonster.com" wrote:

> table of shifts:
> 
> id, shiftstart, shiftend
> 1, 10:00, 19:00
> 2, 11:00, 20:00
> 3, 13:00, 15:00
> 
> 
> desired query output
> 
> timeframe, nr of active shifts
> 1100-1200, 2
> 1200-1300, 2
> 1300-1400, 3
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
> 
> .
> 

That's spot on. 

Of course you are right about the different results. 

Many thanks!

-- 
Message posted via http://www.accessmonster.com

I would like to add another dimension to this query:


my table table now has an added colulumn DATE:

id, shiftstart, shiftend, DATE
1, 10:00, 19:00, 1-1-2010
2, 11:00, 20:00, 1-1-2010
3, 13:00, 15:00, 2-1-2010

Karl Dewey's query result looks like this (without the extra column):

1100 - 1200    1200 - 1300    1300 - 1400
2                         3                       3

I'd like to archieve a result  like this (or similar):

              1100 - 1200    1200 - 1300    1300 - 1400
1-1-2010     2                   3                    3
2-1-2010     0                   1                    1


Is this possible?

-- 
Message posted via http://www.accessmonster.com

First, rename your field. Date is a reserved word, and you should never use 
reserved words for your own purposes. For a comprehensive list of names to 
avoid (as well as a link to a free utility to check your application for 
compliance), check what Allen Browne has at 
http://www.allenbrowne.com/AppIssueBadWord.html

Assuming you rename it to shiftdate, try

SELECT shiftdate, Sum(IIf([shiftstart]<=#12/30/1899 12:0:0# And 
[shiftend]>=#12/30/1899
11:0:0#,1,0)) AS [1100 - 1200], Sum(IIf([shiftstart]<=#12/30/1899 13:0:0# 
And
[shiftend]>=#12/30/1899#,1,0)) AS [1200 - 1300],
Sum(IIf([shiftstart]<=#12/30/1899 14:0:0# And [shiftend]>=#12/30/1899
13:0:0#,1,0)) AS [1300 - 1400]
FROM Shifts
GROUP BY shiftdate

Realistically, though, you should be storing both date and time for 
shiftstart and shiftend. In that case, you'd use

SELECT DateValue(shiftstart), Sum(IIf(TimeValue([shiftstart])<=#12/30/1899 
12:0:0# And TimeValue([shiftend])>=#12/30/1899
11:0:0#,1,0)) AS [1100 - 1200], Sum(IIf(TimeValue([shiftstart])<=#12/30/1899 
13:0:0# And
TimeValue([shiftend])>=#12/30/1899#,1,0)) AS [1200 - 1300],
Sum(IIf(TimeValue([shiftstart])<=#12/30/1899 14:0:0# And 
TimeValue([shiftend])>=#12/30/1899
13:0:0#,1,0)) AS [1300 - 1400]
FROM Shifts
GROUP BY DateValue(shiftstart)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"bertusavius via AccessMonster.com" <u55174@uwe> wrote in message 
news:a357d0492d63c@uwe...
>I would like to add another dimension to this query:
>
>
> my table table now has an added colulumn DATE:
>
> id, shiftstart, shiftend, DATE
> 1, 10:00, 19:00, 1-1-2010
> 2, 11:00, 20:00, 1-1-2010
> 3, 13:00, 15:00, 2-1-2010
>
> Karl Dewey's query result looks like this (without the extra column):
>
> 1100 - 1200    1200 - 1300    1300 - 1400
> 2                         3                       3
>
> I'd like to archieve a result  like this (or similar):
>
>              1100 - 1200    1200 - 1300    1300 - 1400
> 1-1-2010     2                   3                    3
> 2-1-2010     0                   1                    1
>
>
> Is this possible?
>
> -- 
> Message posted via http://www.accessmonster.com
> 

Exactly the solution I was looking for.
Seems like I really need to learn a bit more about using expresions in
queries.

Really appreciate it. 

Thanks!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1