Left Join with Crosstab query

[FL0XN0X]

Dear,

I have got a working query that looks like :

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.Datum
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN [Bezetting per dag] ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.Datum
PIVOT OpvangCodes.OpvangCode In ("VM","NM","D","X VM","X NM","X D","Z VM","Z NM","Z D","W VM","W NM","W D","A","NR","-");

This Crosstab query results in an overview per Date (row) where each column represents the TypeOfReservation and the Values are the number that the TYpeOfReservation occurs. The result looks like the screenshot below.

Question
The desired result is that the crosstab query (screenshot below) gets one extra column holding a '0' when the date corresponds with a holiday, and '1' when it does not correspond with a holiday.
Holidays are defined in a Holiday Table, with StartDatum and StopDatum per record.
I was thinking of a LEFT JOIN like this:

LEFT JOIN [Holiday] on (Kalender.Datum>=[Holiday].[StartDatum] AND Kalender.Datum<= [Holiday].[StopDatum]

But how to get it in the Crostab query syntax?

[isladogs]

Make a query where you join the holiday table to your calendar table first.
Then base your crosstab query on that.

[June7]

Does JOIN using a range work? Never tried. Customary structure for a Holidays table is a record for each date that is a holiday, not start and end dates. That's what I am using.
I am using table structure and sample data indicated in prior threads. I'll let you do the language translation.

Consider:

TRANSFORM Count(Reservations.CusID_FK) AS CountOfCusID_FK
SELECT Calendar.ResDate, IIf([ResDate]=[Holiday],0,1) AS IsHoliday
FROM ResTypes INNER JOIN ((Holidays RIGHT JOIN Calendar ON Holidays.Holiday = Calendar.ResDate) INNER JOIN Reservations ON Calendar.CalID = Reservations.CalID_FK) ON ResTypes.ResTypeID = Reservations.ResType_FK
GROUP BY Calendar.ResDate, IIf([ResDate]=[Holiday],0,1)
PIVOT ResTypes.ResType In ("AM","PM","D");

[FL0XN0X]

Hi June7,

Thank you for the reply. Let me try to get this working. Keep you posted...

[FL0XN0X]

Hi June7,

Close but no cigare: I have got the LEFT JOIN working within the Crosstab Query:


TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.Datum
FROM [Vakantieplanning Moppendot], OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN [Bezetting per dag] ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.Datum
PIVOT OpvangCodes.OpvangCode In ("VM","NM","D","X VM","X NM","X D","Z VM","Z NM","Z D","W VM","W NM","W D","A","NR","-");


The only thing that I need to add is the extra column. But as soon as I add this, Access gives me an error.
In my case, the Holiday table is making use of a start and stopdate per record. When I compare it with your post, the only thing I need to add then is

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.Datum, IIF(Kalender.Datum>=[Vakantieplanning Moppendot].[Startdatum] AND Kalender.Datum<=[Vakantieplanning Moppendot].[Stopdatum],0,1) AS HOLIDAYFACTOR
FROM [Vakantieplanning Moppendot], OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN [Bezetting per dag] ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.Datum, HOLIDAYFACTOR
PIVOT OpvangCodes.OpvangCode In ("VM","NM","D","X VM","X NM","X D","Z VM","Z NM","Z D","W VM","W NM","W D","A","NR","-");

I have no clue. Error is that there is a syntax error for the expression IIF(Kalender.Datum>=[Vakantieplanning Moppendot].[Startdatum] AND Kalender.Datum<=[Vakantieplanning Moppendot].[Stopdatum],0,1) AS HOLIDAYFACTOR

[isladogs]

You seem to have ignored my suggestion in post 2. Worth trying?

[June7]

Ridders, OP is using a structure for Holidays table that does not allow JOIN, AFAIK.

Okay, changed my table to use start and stop. No error. But the IsHoliday calculation result is wrong.

TRANSFORM Count(Reservations.CusID_FK) AS CountOfCusID_FK
SELECT Calendar.ResDate, IIf([ResDate] Between [StartDatum] And [StopDatum],0,1) AS IsHoliday
FROM Holidays, ResTypes INNER JOIN (Calendar INNER JOIN Reservations ON Calendar.CalID = Reservations.CalID_FK) ON ResTypes.ResTypeID = Reservations.ResType_FK
GROUP BY Calendar.ResDate, IIf([ResDate] Between [StartDatum] And [StopDatum],0,1)
PIVOT ResTypes.ResType IN ("AM","PM","D");

[FL0XN0X]

June7,

Got it working thanks to you. The working Query below. It did use a Left Join (not Right Join)

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.Datum, IIf(Kalender.Datum Between Vakantieplanning.Startdatum And Vakantieplanning.Stopdatum,0,1) AS Vakantiefactor
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN ((Kalender LEFT JOIN Vakantieplanning ON (Kalender.Datum >= Vakantieplanning.StartDatum) AND (Kalender.Datum <= Vakantieplanning.StopDatum)) INNER JOIN [Bezetting per dag] ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.Datum, IIf(Kalender.Datum Between Vakantieplanning.Startdatum And Vakantieplanning.Stopdatum,0,1)
PIVOT OpvangCodes.OpvangCode In ("VM","NM","D","X VM","X NM","X D","Z VM","Z NM","Z D","W VM","W NM","W D","A","NR","-");

[FL0XN0X]

mmm, further analysis showed that the join is resulting in double dates :-( ..... thus, close but no cigare

[June7]

That's what I meant earlier. Only solution I see is Holidays table that has a record for each date that is a holiday, not a date range. This allows for a JOIN instead of Cartesian relationship.

[FL0XN0X]

June7,

LEFT JOIN working this time without 'doubles':

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.Datum, IIf(Kalender.Datum Between Vakantieplanning.Startdatum And Vakantieplanning.Stopdatum,0,1) AS Vakantiefactor
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN ((Kalender LEFT JOIN Vakantieplanning ON (Kalender.Datum >= Vakantieplanning.StartDatum) AND (Kalender.Datum <= Vakantieplanning.StopDatum)) INNER JOIN [Bezetting per dag] ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.Datum, IIf(Kalender.Datum Between Vakantieplanning.Startdatum And Vakantieplanning.Stopdatum,0,1)
PIVOT OpvangCodes.OpvangCode In ("VM","NM","D","X VM","X NM","X D","Z VM","Z NM","Z D","W VM","W NM","W D","A","NR","-");

[June7]

But it's the same SQL.

[FL0XN0X]

Hi June7,

Indeed, same SQL, but the doubles were caused by the fact that I added the 'Holiday Description field' as an extra column of the Crosstab. When I omit this extra column, no doubles are generated (the description of the holiday was a 'nice to have', but not really required.)

Just FYI, the 'little' project is for my sister who owns a kindergarden, It's a 'planning tool'/administration tool. I'm almost there, only thing I have to build is montly invoicing .... Anyway, thank you for your many helps until now.
Kind regards,
Bart

[June7]

Glad it works but I still don't understand how. My query did not have a description field and I got the duplicate dates as well, still do.

The two SQL statements you posted are identical. I don't see Description field in either.

But managed to get the translation figured out and the English version is:

TRANSFORM Count(Reservations.CusID_FK) AS CountOfCusID
SELECT Calendar.ResDate, IIf([Calendar].[ResDate] Between [Holidays].[Startdate] And [Holidays].[Stopdate],0,1) AS VF
FROM ResTypes INNER JOIN (Customers INNER JOIN ((Calendar LEFT JOIN Holidays ON (Calendar.CalID>=Holidays.StartDate) AND (Calendar.ResDate<=Holidays.StopDate)) INNER JOIN
Reservations ON Calendar.CalId = Reservations.CalID_FK) ON Customers.CusId = Reservations.CusID_FK) ON ResTypes.ResTypeID = Reservations.ResType_FK
GROUP BY Calendar.ResDate, IIf([Calendar].[ResDate] Between [Holidays].[Startdate] And [Holidays].[Stopdate],0,1)
PIVOT ResTypes.ResType In ("AM","PM","D");

No duplicate dates but the VF calculated value is wrong. 1/1/2018 should show as a holiday but it does not.