CrossTab Query empty places

Question

I have a table where is specified ID, length, WorkHours, Day

I need to create a Crosstab Query, where There will be for every day during the length of the work written number of WorkHours done during that day. If there is a blank space I need to have number zero there. However after the length of the work I need to have blank space there.

Here is my solution:

TRANSFORM Nz(Sum([SampleTable]![WorkHours]))+0 AS [Sum]
SELECT SampleTable.ID, SampleTable.Length
FROM SampleTable
WHERE (((SampleTable.Day)>0 And (SampleTable.Day)<200))
GROUP BY SampleTable.ID, SampleTable.Length
ORDER BY SampleTable.ID, SampleTable.Length
PIVOT SampleTable.Day;

I need to get rid of the zeros after the Length of the inspection and have a null values instead. My solution is creating zeroes everywhere instead of only in the interval between start to length.

When I try this solution it still doesn't make anything good. Only a few blank spaces in wrong places.

TRANSFORM IIf([sampleTable].[length]>[sampleTable].[day],Null,Nz(Sum([SampleTable]![WorkHours]))+0) AS [Sum]
SELECT SampleTable.ID, SampleTable.Length
FROM SampleTable
WHERE (((SampleTable.Day)>0 And (SampleTable.Day)<200))
GROUP BY SampleTable.ID, SampleTable.Length
ORDER BY SampleTable.ID, SampleTable.Length
PIVOT SampleTable.Day;

Answer 1

Essentially, you are facing the gaps and islands problem where you need to fill in the missing gaps and discontinuous islands of days with zero work hours and conditionally nullify values by ID. For your nuanced needs, consider the following underlying steps.

1. Create a cross join table (using INTO clause) of all possible ID, Day, and Length with WorkHours filled with zero:

   SELECT i.ID, d.Day, i.Length, 0 AS WorkHours
   INTO myCrossJoinTable
   FROM
     (SELECT DISTINCT [ID], [Length] FROM LengthWorkHoursDay) i,
     (SELECT DISTINCT [Day] FROM LengthWorkHoursDay) d
   WHERE d.Day IS NOT NULL
   

   To fill in for up to the 100 days (or max Length in table), use VBA to build sequential days:

   Sub AppendData()
      Dim i As Long, sql As String
   
      CurrentDb.Execute "DELETE FROM SampleTable"
   
      For i = 1 to DMax("[Length]", "SampleTable")
          sql = "INSERT INTO myCrossJoinTable ([ID], [Day], [Length], [WorkHours]) " _
                 & "SELECT DISTINCT [ID], " & i & ", [Length], 0 " _
                 & "FROM SampleTable"
   
          CurrentDb.Execute sql
      Next i
   End Sub
   

2. Create a separate query to join original table with cross join table to fill in gaps and discontinuity in sequential data with work hours of zero.

   SELECT d.ID
          , d.Day
          , t.Day
          , d.Length
          , IIF(t.WorkHours IS NULL, d.WorkHours, t.WorkHours) AS WHours
   FROM myCrossJoinTable As d
   LEFT JOIN SampleTable AS t
      ON t.ID = d.ID 
      AND t.Day = d.Day
   WHERE (d.Day > 0 And d.Day < 200)
   

3. Run final crosstab query based on above base query with conditional SUM to nullify values:

   TRANSFORM SUM(IIF(q.Day > q.Length, NULL, q.WHours)) AS [Sum]
   SELECT q.ID, q.Length
   FROM mySavedQuery q
   GROUP BY q.ID, q.Length 
   ORDER BY q.ID, q.Length
   PIVOT q.Day
   

   ID	Length	1	2	3	4	5	6	7	8	9	10	11	12	13	14	100
   1	4	10	5	0	137
   2	6	25	0	0	0	67	0
   3	10	6	0	0	0	0	0	0	4	0	0
   4	100	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
   5	10	2	1	2	1	2	1	2	1	2	1