Displaying a grid, with not-records

[Dirk Space]

I'm not sure if this is a Form or a Query issue; this is a change over from an Excel "database" (with all the fuzzy math it does to accomplish things).

I have a table with 10 employees, each with a Date/Time-Short Time field for Start Time1 and End Time1, and Start Time2 and End Time2 (for those with split shifts)

There are fields calculated for Hours Per Shift, Is (Day of the Week) Off, etc., so it works well because it assigns a static value of "something" for each employee. (Excel's LOOKUP was getting way too out of hand.)

The only problem I am having is it displaying a grid of how many are working that day, broken down per hour.

I need a daily snapshot. The way it is displaying in Excel is (pardon the off spacing):

.........<Entered numerical week>
..............S M T W T F S
12:00 AM 1 1 2 2 2 2 2
...1:00AM 1 2 3 3 2 2 4

I've tried various queries and reports over several days, but fear I may have taken a wrong turn and am going down the wrong rabbit hole.

:banghead:

Can any one assist?

 

[theDBguy]

Hi. Welcome to AWF!


Have you tried using a Crosstab query?

 

[CJ_London]

broken down per hour.

You need to clarify what this means

What happens if someone starts at 12:30pm and finished at 1pm - does that count as 0 for 12:00pm and 1 for 1:00pm? or something else.

also this

a Date/Time-Short Time field for Start Time1 and End Time1, and Start Time2 and End Time2 (for those with split shifts)

is it stored as short time or just formatted as short time (i.e. might include days as well but you are just not seeing it)

 

[Dirk Space]

Hi. Welcome to AWF!


Have you tried using a Crosstab query?

Thanks.

I've tried, but it wants an existing field to Crosstab with and all I have are Start Times and End Times.

If I created a BUNCH of Start Times (Sunday Start Shift1 00:00, Sunday Start Shift1 00:15, etc.), then it could per Employee but that seems excessive.

 

[Dirk Space]

You need to clarify what this means

What happens if someone starts at 12:30pm and finished at 1pm - does that count as 0 for 12:00pm and 1 for 1:00pm? or something else.

Well, actually it's per 15 minutes. I thought going by the hour would be easier in the example...

Start: 12:30 pm
End: 01:00 pm

12:00 pm 0
12:15 pm 0
12:30 pm 1
12:45 pm 1
01:00 pm 0
01:15 pm 0

And for each employee and summed, based on their set schedule.

An overview would show x-axis the Days of Week (seven) and y-axis 15 minute increments (12:00 am, 12:15am, etc.); need to see how many are working that day, during all 15 minute intervals.

is it stored as short time or just formatted as short time (i.e. might include days as well but you are just not seeing it)

Data Type: Date/Time
Format: Short Time

 

[theDBguy]

Thanks.

I've tried, but it wants an existing field to Crosstab with and all I have are Start Times and End Times.

If I created a BUNCH of Start Times (Sunday Start Shift1 00:00, Sunday Start Shift1 00:15, etc.), then it could per Employee but that seems excessive.

Hi. That's correct! A query cannot display something that doesn't exist. However, it can display something it can manufacture or calculate. So, if you can do that, then a crosstab query might work for you in the end. Otherwise, you might be looking at using code to create a temporary table. Even then, the VBA code will have to "manufacture" the non-existent values.

 

[CJ_London]

Well, actually it's per 15 minutes.

all you've done is moved the goalposts, you've not answered the question. Are you saying all employees 'clock on' or 'clock off' at exactly quarter to/past etc

Data Type: Date/Time
Format: Short Time

again, not answered the question you've already said what the format is, I'm asking what the actual value is.

Dates are stored as decimal numbers and formatted as required (same as Excel). Consequently the format hides the actual value. See this thread which illustrates the issue https://www.accessforums.net/showthread.php?t=78522

 

[CJ_London]

further to my last post - just trying to clarify the actual requirement

during all 15 minute intervals.

so you are not looking for a specific point in time

so not

12:00 pm
12:15 pm
etc

what you actually mean is
12:00-12:14
12:15-12:29
etc

so you have someone who finishes at 12:08 and someone else starts at 12:10

you would have

12:00-12:14 2
12:15-12:29 1

is that what you mean?

 

[Dirk Space]

all you've done is moved the goalposts, you've not answered the question. Are you saying all employees 'clock on' or 'clock off' at exactly quarter to/past etc

I thought it was explained properly... and I don't understand your new question.

Employees can "Clock On" and "Clock Off" in 15 minute intervals; the total hours of the shift varies.

0800-1015
0315-1230
1545-1915

again, not answered the question you've already said what the format is, I'm asking what the actual value is.

Dates are stored as decimal numbers and formatted as required (same as Excel). Consequently the format hides the actual value. See this thread which illustrates the issue

A calculated field that is looking at a "Clock On" time, uses this expression and it tells me how many hours that employee is working (result type is double) in the "AM".

IIf([S1 (Start)]=[S2 (Start)],0,IIf((#11:30:00 AM#-[S1 (Start)])*24<=0,0,IIf([S1 (End)]<#11:30:00 AM#,([S1 (End)]-[S1 (Start)])*24,(#11:30:00 AM#-[S1 (Start)])*24)))

So, I believe the actual value for starting and ending time is the Time.

 

[Dirk Space]

further to my last post - just trying to clarify the actual requirement

so you are not looking for a specific point in time

so not

12:00 pm
12:15 pm
etc

what you actually mean is
12:00-12:14
12:15-12:29
etc

so you have someone who finishes at 12:08 and someone else starts at 12:10

you would have

12:00-12:14 2
12:15-12:29 1

is that what you mean?

I guess you could say, I'm looking at a specific point in time. This example illustrates how 2 employees would show up in an overview:

Employee 1
Start: 12:00 pm
End: 01:00 pm

Employee 2
Start: 12:30 pm
End: 01:15 pm

12:00 pm 1
12:15 pm 1
12:30 pm 2
12:45 pm 2
01:00 pm 1
01:15 pm 1

Since shifts end in 15 minute increments, someone's End at 01:00 pm would not be counted at the 01:00 pm "Who is here at 1:00 pm".

 

[CJ_London]

thank you for clarifying

I suggest the basic solution you will require to do the count is as follows

1. create a table called say tblTimes. It needs a single field called say DayTime of type date
2. populate that table with the times you want to measure for e.g. 8:00am, 8:15am etc
3. I don't know the name of the table/query with your employee start/end times, so lets call it tblClockInOut
4. now create a query using this sql, change names to match your table/field names (note that use of spaces and non alphanumeric character in names can cause misleading error messages)

SELECT ClockInOut.ClockInOutDate, DayTime, Count(employeedPK) as CountWhosHere
FROM tblTimes, tblClockInOut
WHERE ttblTimes.DayTime<tblClockInOut.EndTime AND tblTimes.DayTime>=tblClockInOut.StartTime
GROUP BY ClockInOut.ClockInOutDate, DayTime

5. Once you are happy it is creating the correct results on an employee by employee basis, add additional criteria to limit to a week, then convert to a crosstab query

 

[Dirk Space]

thank you for clarifying

I suggest the basic solution you will require to do the count is as follows

1. create a table called say tblTimes. It needs a single field called say DayTime of type date
2. populate that table with the times you want to measure for e.g. 8:00am, 8:15am etc
3. I don't know the name of the table/query with your employee start/end times, so lets call it tblClockInOut
4. now create a query using this sql, change names to match your table/field names (note that use of spaces and non alphanumeric character in names can cause misleading error messages)

SELECT ClockInOut.ClockInOutDate, DayTime, Count(employeedPK) as CountWhosHere
FROM tblTimes, tblClockInOut
WHERE ttblTimes.DayTime<tblClockInOut.EndTime AND tblTimes.DayTime>=tblClockInOut.StartTime
GROUP BY ClockInOut.ClockInOutDate, DayTime

5. Once you are happy it is creating the correct results on an employee by employee basis, add additional criteria to limit to a week, then convert to a crosstab query

Thanks.

The issue is I don't have a ClockInOutDate. It's not transactionary (action taken on a specific date), nor for the purposes of logs. It's just a 6 month schedule, with varying working dates and times on days of the week.

There isn't any need to see if Janice is working on 01/25/2020 and what time; the who doesn't matter. It's more, how many are working on Saturday at 14:00.

A schedule is built (Table1) to fit a projected need, then employees (Table2) get assigned (lookup from Table1) to said schedule.


So, the fields are:
ID (auto random), Name, S1_Start, S1_End, S2_Start, S2_End, M1_Start, M1_End...

S is Sunday (and there could be 2 shifts, so S1 & S2), M is Monday, etc.


I built a mock db just to see what it would show, based on your SQL. It's similar to what I've attempted before, but will only count the "hits" and not the "misses".

In Excel, I "quickly" created something that works. It just seems unnecessary to export it the schedule and have it "do things".

7 rows of days of the week
96 rows of 15 minute increments

Each cell is:

=COUNTIFS(x1_Start,<=That 15 minute increment, x1_End,>That 15 minute increment+COUNTIFS(x2_Start,<=That 15 minute increment, x2_End,>That 15 minute increment)

And yes, that totals 672 different cell equations.

 

[Dirk Space]

Hi. That's correct! A query cannot display something that doesn't exist. However, it can display something it can manufacture or calculate. So, if you can do that, then a crosstab query might work for you in the end. Otherwise, you might be looking at using code to create a temporary table. Even then, the VBA code will have to "manufacture" the non-existent values.

How does one create a temp table or "manufacture" non-existent values?

 

[isladogs]

Dirk
You last post was a duplicate of a moderated post which I had just approved.
I've now deleted your duplicate.

 

[Dirk Space]

Dirk
You last post was a duplicate of a moderated post which I had just approved.
I've now deleted your duplicate.

Thanks. My bad.

I got too clicky.

 

[theDBguy]

How does one create a temp table or "manufacture" non-existent values?

Hi. Good question. So, just as an example of what I was talking about, but not necessarily as it applies to your situation, take a look at this short article. Hope it makes sense...

 

[CJ_London]

the issue is I don't have a ClockInOutDate.

So what is this example?

Employee 1
Start: 12:00 pm
End: 01:00 pm

Employee 2
Start: 12:30 pm
End: 01:15 pm

If they are shift start/end times (and you are not concerned about whether they actually turn up or not), then use shift start/end times

 

[Dirk Space]

So what is this example?

That would be Employee 1 with a Start Time and End Time and the same for Employee 2.

If they are shift start/end times (and you are not concerned about whether they actually turn up or not), then use shift start/end times

It doesn't seem to work with more than one Start Time and End Time field, for each employee.

So, the fields are:
ID (auto random), Name, S1_Start, S1_End, S2_Start, S2_End, M1_Start, M1_End...

S is Sunday (and there could be 2 shifts, so S1 & S2), M is Monday, etc.

There are 28 fields for each schedule (4 Times for each day (2 shifts each with a Start and End) x 7 Days).

If I were to verbalize, in some poorly written and non-functional SQL-like way, what I wanted each cell to represent:

GO THROUGH (tbl.Employee) IF (Employee) is here/exists FROM 00:00 to 00:15 ON SUNDAY COUNT(Employee) THEN Sum ELSE "0"

Cell A1

GO THROUGH (tbl.Employee) IF (Employee) is here/exists FROM 00:00 to 00:15 ON MONDAY COUNT(Employee) THEN Sum ELSE "0"

Cell A2

Etc... to A7

Cell B1 would be:

GO THROUGH (tbl.Employee) IF (Employee) is here/exists FROM 00:15 to 00:30 ON SUNDAY COUNT(Employee) THEN Sum ELSE "0"

7 columns of days of the week
96 rows of 15 minute increments

Is what I'm trying to accomplish making any sense?

 

[CJ_London]

think you need to provide details on all 28 fields with examples of how they are populated. At the moment it looks like your data is not normalised so you would need to create a union query to normalise it. Or if you want to use code it will be extremely complicated and not something I would want to even consider tackling.

 

[Dirk Space]

Hi. Good question. So, just as an example of what I was talking about, but not necessarily as it applies to your situation, take a <I can't even quote a url>. Hope it makes sense...

I've tried that, but in their example there is one field for Date Booked, per guest. And that field is the only relevant field, when looking to see if it's booked, and to then sum that count.

I've got 28 fields of start and end times, per employee, that can't take the place of one "relevant" field per employee (unlike a guest being booked or not booked on a date).