MS Access – How to Create a Pivot Table with Multiple Same Values as Columns

ms accesspivot

First post ever, thanks for having me!

Currently we have an Access database connected to two access control devices tracking hours for different statuses: Check In, Check Out, Break Out, Break In, Overtime In, Overtime Out.

How our devices manage this though, is that they record each time independently, so in the Access database we have different rows telling the username, date and time, status, device and other interesting stuff that sadly don't help with the problem. What I want to focus on though is time, which I want to put in separate columns using Access' CrossTab mode, with TRANSFORM and PIVOT and what else.

The main issue is that the employees have many breaks during the day and sometimes many overtime schedules and I have no way to tell which is the first or the second break/overtime but the time when those happened.

Here is an example of what I have with Dave:

Name    | Date       | Auth     | Time  |
------------------------------------------------------------------
Dave    | 21.01.2019 | CheckOut | 18:00 |
Dave    | 21.01.2019 | BreakIn  | 16:45 |
Dave    | 21.01.2019 | BreakOut | 16:30 |
Dave    | 21.01.2019 | BreakIn  | 14:00 |
Dave    | 21.01.2019 | BreakOut | 13:00 |
Dave    | 21.01.2019 | BreakIn  | 10:00 |
Dave    | 21.01.2019 | BreakOut | 09:45 |
Dave    | 21.01.2019 | CheckIn  | 07:30 |

And here is what I'm trying to achieve with that:

Name    | Date       | CheckIn | BreakOut1 | BreakIn1 | BreakOut2 | BreakIn2 | BreakOut3 | BreakIn3 | CheckOut |
----------------------------------------------------------------------------------------------------------------
Dave    | 21.01.2019 |  07:30  |   09:45   |   10:00  |   13:00   |   14:00  |   16:30   |   16:45  |  18:00   |

Right now I managed to obtain somewhat what I planned to have, but I was only able to output one column for each Auth. If I manage to get the data that way I might be able to calculate exactly how many hours an employee has worked.

Do you think that using a CrossTab is a good idea to do such? If so could you please help me reach the objective? Not really looking for snippets of instant solutions, rather many ways to think on how to achieve it. I have a standard expertise with Access and database in general so one can never stop learning!

Thanks in advance, have a great day.

Leo

Best Answer

It's apparent that you intended to create a single Crosstab, but I couldn't help but notice the actual final goal: "I might be able to calculate exactly how many hours". Indeed, if you had the final data as you showed, it would be possible to get total times, but there are other normalized forms that allow for sums without special code for handling multiple breaks and/or differing number of breaks. Below is a sequence of queries that gives you total times. If you still prefer your Crosstab format, the first few queries provide a form which allows you to pivot on different break times as desired.

This set of queries may seem like overkill, but each has its purpose and this style of query coding is compatible with treating the data as a set. This is simply a sequence of operations on the set. A desire to do all things in a single query is an artificial requirement. Some of these could be combined as nested queries, but that removes some convenience within Access Query Designer, and Access tends to throw fits with nested queries for TRANSFORMS as I've discovered.

Named query [BreakSeq]. This same query could produce combined labels like "BreakOut1" with small modifications:

SELECT Data.Naem, Data.Daet, Data.Time, 
    IIf([Auth]='BreakIn','In','Out') AS InOut, 
    1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND [Daet]=#" & [Daet] 
    & "# AND [Auth]='" & [Auth] & "' AND [Time] < #" & [Time] & "#") AS [Number]
FROM Data
WHERE (Data.Auth Like "Break*")
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [BreakNorm]. Transform requires an aggregate function, but the assumptions along with the groupings guarantee unique values, so FIRST() will return such unique values. Also, had your table been normalized with this form, subsequent queries would have been easier to produce:

TRANSFORM First(BS.Time) AS FirstOfTime
SELECT BS.Naem, BS.Daet, BS.Number, "Break" AS Type
FROM BreakSeq AS BS
GROUP BY BS.Naem, BS.Daet, BS.Number
ORDER BY BS.Naem, BS.Daet, BS.Number
PIVOT BS.InOut;

Named query [BreakDetail]. This produce negative minutes useful for adding to overall CheckIn and Out spans:

SELECT BN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM BreakNorm AS BN;

Named query [BreakSum]:

SELECT BD.Naem, BD.Daet, BD.Type, Sum(BD.Minutes) AS BreakMinutes
FROM BreakDetail AS BD
GROUP BY BD.Naem, BD.Daet, BD.Type;

Now for the CheckIn and CheckOut... Named query [CheckSeq]:

SELECT Data.Naem, Data.Daet, IIf([Auth]='CheckIn','In','Out') AS InOut, Data.Time
FROM Data
WHERE (Data.Auth Like "Check*");

Named query [CheckNorm]:

TRANSFORM First(CS.Time) AS FirstOfTime
SELECT CS.Naem, CS.Daet, "Check" AS Type
FROM CheckSeq AS CS
GROUP BY CS.Naem, CS.Daet
ORDER BY CS.Naem, CS.Daet
PIVOT CS.InOut;

Named query [CheckDetail]:

SELECT CN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM CheckNorm AS CN;

Now combine the results with a total sum of minutes worked per name and date:

SELECT BS.Naem, BS.Daet, [CD].[Minutes]+[BS].[BreakMinutes] AS WorkingMinutes
FROM BreakSum AS BS INNER JOIN CheckDetail AS CD 
    ON (BS.Daet = CD.Daet) AND (BS.Naem = CD.Naem)
ORDER BY BS.Naem, BS.Daet;

This solution assumes the following. If any of these assumptions are not guaranteed, exceptions should be identified and resolved, otherwise you'll need to code special logic to avoid errors and bogus numbers:

  1. There is precisely one CheckIn and one CheckOut entry per name and date.

  2. For each BreakOut entry there is a corresponding BreakIn entry, so that there are no unmatched pairs.

  3. The in and out times are proper, so that the CheckOut time is after the CheckIn time and BreakOut times precede corresponding BreakIn times and that there are no overlapping break times.

  4. CheckIn, CheckOut, BreakIn, BreakOut are the only authentication types.

  5. Text columns [Naem] and [Auth] don't contain apostrophes ('). Otherwise, special care must be taken to properly delimit those values in some queries.

If you insist on the final form you showed, here's a solution with fewer queries. Sorry if I made it too instant.

Named query [AllSeq]:

SELECT Data.Naem, Data.Daet, Data.Time, Data.Auth, 
   IIf([Auth] Like "Break*","Break","Check") AS Type, 
   IIf([Auth] Like '*In','In','Out') AS InOut, 
   1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND Daet=#" 
      & [Daet] & "# AND Auth='" & [Auth] & "' AND Time < #" & [Time] 
      & "#") AS [Number], 
   IIf([Auth] Like "Break*",[Auth] & [Number],[Auth]) AS AuthNum
FROM Data
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [AllCross]:

TRANSFORM First(AZ.Time) AS FirstOfTime
SELECT AZ.Naem, AZ.Daet
FROM AllSeq AS AZ
GROUP BY AZ.Naem, AZ.Daet
PIVOT AZ.AuthNum;

Named query [AllCrossSorted]:

SELECT AC.Naem, AC.Daet, AC.CheckIn, AC.BreakOut1, AC.BreakIn1,
       AC.BreakOut2, AC.BreakIn2, AC.BreakOut3, AC.BreakIn3, AC.CheckOut
FROM AllCross AS AC;
Related Question