Okay, I got all this to unpivot... except for one chunk in 2022 (well, it unpivots, but the End Dates are mostly missing). This is a really good example of why I'm not a fan of doing data entry in Excel... it lets you do whatever you want, and that's not always a good thing (like leaving columns empty, entering any value you want...)
One thing in your data that should just be calculated on the fly in a query, the "Nights" column... It's just a number of days between the StartDate and the EndDate. Just use DATEDIFF() for that.
Code:
SELECT [Rally Count All].Start, [Rally Count All].End, [Rally Count All].Nights, [Rally Count All].Site, [Rally Count All].Marshals, DateDiff("d",[Start],[End]) AS NightsCalc
FROM [Rally Count All];
Not 100% sure I got all the data right, but maybe it's because I'm not carefully eyeballing every table. But a bunch of the 2022 data is messed up (the dates don't come through... I'll retry it and check back later.)
Even so, doing this in PowerQuery is infinitely easier in PowerQuery than in VBA. Absolutely no way I'd even try this in Access. maybe because I'm stubborn. (I suppose you could create all your columns in Access as text, and then validate, but in PowerQuery all that stuff is pretty much built in... oh, and PowerQuery writes the query for you... ) =)