calculate headcount

[bertusavius via AccessMonster.com]

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?

 

[KARL DEWEY]

Post sample data with table and field names plus datatype.

 

[bertusavius via AccessMonster.com]

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

 

[KARL DEWEY]

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.

 

[bertusavius via 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

 

[KARL DEWEY]

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;

 

[bertusavius via AccessMonster.com]

That's spot on.

Of course you are right about the different results.

Many thanks!

 

[bertusavius via 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?

 

[Douglas J. Steele]

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)

 

[bertusavius via 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!