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]
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
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
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
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:
There is precisely one CheckIn and one CheckOut entry per name and date.
For each BreakOut entry there is a corresponding BreakIn entry, so that there are no unmatched pairs.
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.
CheckIn, CheckOut, BreakIn, BreakOut are the only authentication types.
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
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
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;