Table Design or Crosstab Query

[Joseph]

3 tables
tblCadet:CadetID,Name,DOB, etc....with several subtables
tblDatesateID,CadetId,DtTpyeId,Date,Comments(all dates are start dates for
the DtTypeId)
tblDateTypetTypeId,Type(DoE,Lvl1,Lvl2,Lvl3,Exit), Level(for order of
type,if need to change type later)

I have a crosstab that works great to show the last date per CadetId in
columns from tblDateType!Type. Which is great if the date in each type is
more recent than the previous type. If the cadet were to get dropped back a
level, from Lvl3 to Lvl2, in the program, the date for Lvl1 stays the same,
Lvl2 changes to the current date, and there will be a new date for Lvl3, or
blank until that level is reached. Easy enough right. But I also need to
save, for records, the cadet's total progress thru the program. All the
progression and regression thru the program. And for the report, I will have
two reports, one for total progress and one for current progress. The one
for total progress is easy, a subreport of the tblDates, in accending order
of the dates, on a report based on the tblCadet. The current progress report
is the hard one. I need to have the current dates on the report. Example:
the same cadet regressed from Lvl3 to Lvl2. DoE, and Lvl1 dates stay the
same, Lvl2 has to be the new date, and nothing in Lvl3. Is there a way to
code the crosstab to only show the dates, per type, that are more recent that
the last type?

SQL for the crosstab

TRANSFORM Last([Cadets Dates].Date) AS LastOfDate
SELECT [cadet rank].[rank] & " " & [last] & ", " & [first] & " " & [middle]
AS Cadet, Cadets.Number, Phase.PhaseID, Right([SSN],4) AS [Last 4],
Last([Cadets Dates].Comments) AS Remarks
FROM Phase INNER JOIN ([Cadet Rank] INNER JOIN (SpecificDates INNER JOIN
([Cadets Dates] INNER JOIN Cadets ON [Cadets Dates].CadetID = Cadets.CadetID)
ON SpecificDates.SDID = [Cadets Dates].SDID) ON [Cadet Rank].CRId =
Cadets.CRId) ON Phase.PhaseID = Cadets.PhaseID
WHERE (((Phase.PhaseID)<>5 And (Phase.PhaseID)<>6 And ((Phase.PhaseID)<>5 Or
(Phase.PhaseID)<>6)))
GROUP BY [cadet rank].[rank] & " " & [last] & ", " & [first] & " " &
[middle], Cadets.Number, Phase.PhaseID, Right([SSN],4), Cadets.Dorm,
Cadets.Number
PIVOT SpecificDates.Classification In ("Date of Entry","Level 1","Level
2","Level 3","Exited");