Some kind of crosstab query: part4

[FL0XN0X]

Hi,



I have following working crosstab query:

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.maand
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
WHERE (((Kalender.jaar)=2018))
GROUP BY Kalender.maand
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","-");

which result in this overview:



What I would like to add to this query is a group by Klantenbestand.Naam, so that per month the numbers are shown per Klantenbestand.Naam.

The DB relations look like:



Can someone assist please?
Thank you.

[June7]

Don't see a Naam field in the table.

Did you try adding field as another RowHeader in the query design grid?

[FL0XN0X]

Hi,

My bad. I added Klantenbestand.[Naam] where it should be Klantenbestand.[Naam kind] :-S

This is the query:


TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.maand,Klantenbestand.[Naam kind],Klantenbestand.[Voornaam kind]
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN ((Kalender LEFT JOIN [Vakantieplanning Moppendot] ON (Kalender.Datum>=[Vakantieplanning Moppendot].StartDatum) AND (Kalender.Datum<=[Vakantieplanning Moppendot].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
WHERE kalender.jaar=2018
GROUP BY Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind]
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","-")

OK. I need to add 1 JOIN and then the project is ready, thanks to June7 AND Ridders52
Hopefully, the JOIN works out well, if not, "one final post"

[FL0XN0X]

Hi,

I have played with the Where statement because I want the Select statement to get 3 parameters via a form.

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind]
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
WHERE (((Kalender.jaar)=2018) AND ((Klantenbestand.[Naam kind])="Verhoeven") AND ((Kalender.maand)="januari"))
GROUP BY Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind]
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 results in :

maand	Naam kind	Voornaam kind	VM	NM	D	X VM	X NM	X D	Z VM	Z NM	Z D	W VM	W NM	W D	A	NR
januari	Verhoeven	Theo	0	0	10	0	0	0	0	0	0	0	0	0	0	0

However, the query should be extended to hold "ContractType" from the table Contracttype. As soon as I join this table using the Designer, the query result is empty :-(

Any idea why ?
Thank you.
Kind regards,
Bart




TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind]
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
WHERE (((Kalender.jaar)=2018) AND ((Klantenbestand.[Naam kind])="Verhoeven") AND ((Kalender.maand)="januari"))
GROUP BY Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind]
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]

Joined it how? Would have to include [Opvang weekprofiel] table.

[FL0XN0X]

Hi,

I do not succeed in adding the [Opvang weekprofiel] using the Query Designer (cfr. screenshot).
Can you please advise what I should add in the Designer (group by, ...) .
FYI, the Contractype is added in [Bezetting per dag] via ContractTypeId, but since it is the same each day per month (thus it might only change on a month base) I only need the first value for a particular month.
Here is my (failed) attempt:

[June7]

Ooops, did not notice the field in [Bezetting per dag].

Either don't include link between [Bezetting per dag] and [Contracttype] or don't include [Opvang weekprofiel].

Then I expect the Contracttype field would be a Row Heading.

Really should not use the same name for multiple objects. Name the table ContractTypes and the field ContractType.

[FL0XN0X]

...........................

[FL0XN0X]

Hi,

I have got a mainform, with a combo that populates with Klantenbestand.[Naam kind]. Based on this combobox, the subform is filtered.
I want to filter the subform on the combination of 2 comboboxes:

1) the one which already works
2) the second where I can choose the year

Subform must be filtered using the combination of both comboboxes, where the second combobox is "optional": if not filled in, then only use the first as filter.

Can someone advise please?
Thank you in advance.
Kind regards,
Bart



The Subform data source is the Crosstab query build in previous posts,i.e.:

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN (Contracttype INNER JOIN [Bezetting per dag] ON Contracttype.Id = [Bezetting per dag].ContracttypeId) ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Kalender.maand, Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id
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]

Rather unusual to use a CROSSTAB in a form as data cannot be edited.

Must declare PARAMETERS with CROSSTAB for dynamic filtering. Review http://allenbrowne.com/ser-67.html#Param

[FL0XN0X]

Hi,

I don't want to edit data, what I want to do is to filter the Crosstab query in the Subform by 3 comboboxes in the mainform. Using only 1 combobox, I know how to do this, but how to do this with 3 comboboxes?

1) First combobox: Will filter the subform based on the Klantenbestand.[Naam Kind]. By using the Link Master and Child properties, this is working.
2) The first combobox results in a list of a certain "child info", but for all years and all months.. Thus, I want to filter the results of combobox 1 , with the year
3) I want the result om combo 1+ combo 2 to filter with the month name (i.E; combo 3)

[June7]

Did you try declaring parameters and adding WHERE clause?

[FL0XN0X]

Hi,

Yes, but the parameters are provided within the main form by means of comboboxes , but the query in the subform is already loaded when opening the mainform. At load time of the main form, no selection is yet made in the comboboxes ..... so how to tackle that?

[June7]

Show your CROSSTAB query statement that has the PARAMETERS and WHERE clauses.

[FL0XN0X]

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id, Kalender.Maand, Kalender.Jaar
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN (Jaartabel INNER JOIN (Contracttype INNER JOIN [Bezetting per dag] ON Contracttype.Id = [Bezetting per dag].ContracttypeId) ON Jaartabel.Id = Contracttype.JaartalId) ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
WHERE (((Klantenbestand.Id)=[Forms]![Overzicht facturatie]![cboKind]) AND ((Kalender.Maand)=[Forms]![Overzicht facturatie]![cboMaand]) AND ((Kalender.Jaar)=[Forms]![Overzicht facturatie]![cboJaartal]))
GROUP BY Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id, Kalender.Maand, Kalender.Jaar
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","-");

However,

Meanwhile tried to get it working with following Crosstab Query without form parameters:

TRANSFORM Nz(Count([Bezetting per dag].[KindgegevensId]),0) AS CountOfCustID
SELECT Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id, Kalender.Maand, Kalender.Jaar
FROM OpvangCodes INNER JOIN (Klantenbestand INNER JOIN (Kalender INNER JOIN (Jaartabel INNER JOIN (Contracttype INNER JOIN [Bezetting per dag] ON Contracttype.Id = [Bezetting per dag].ContracttypeId) ON Jaartabel.Id = Contracttype.JaartalId) ON Kalender.ID = [Bezetting per dag].KalenderId) ON Klantenbestand.Id = [Bezetting per dag].KindgegevensId) ON OpvangCodes.ID = [Bezetting per dag].OpvangCodesId
GROUP BY Klantenbestand.[Naam kind], Klantenbestand.[Voornaam kind], Contracttype.Contracttype, Contracttype.[Maandprijs contracttype], Klantenbestand.Id, Kalender.Maand, Kalender.Jaar
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","-");

Then I added following properties for the Subform:

Link Master Fields: cboKind; cboMaand
Link Child Fields: Id; Maand

This works GREAT for the 2 comboboxes .... but when adding the last one, it does not work ??????

Thus, tdding the last combobox:
Link Master Fields: cboKind; cboMaand, cboJaartal
Link Child Fields: Id; Maand; Jaar

But hten the query does not result anything :-(