Crosstab

[Ticotion]

Hi

I need your help once again

I have a query that is a crosstab showing using the following SQL

TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;

I use this as a datasource for a report. This then gives me a report where I
have weeks on tob and downwards have department (and also year). I then want
to use a filter som that I only recieve one row for each the choosen year and
weeks. I use the following code:

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" & Me.combo7&
" And [weeknb] >= " & Me.combo5& " And [yearnb] <= " & Me.combo9& " And
[weeknb] <= " & Me.combo11

I then recieve and error because week is not part of my SQL. If I add week
to my SQL I get week downwards and on top of the report.

How can I solve this so I get an report that users can filter showing like
below?


Weekno 1 2 3 4 5 6
Department
dpt 1
dpt2

Please help

regards Ticotion

 

[KARL DEWEY]

Maybe because you have qry_generelOEE.week and [weeknb]. Try using the
same name.

 

[Michel Walsh]

The easiest way is to change the crosstab query:

PARAMETERS FORMS!formNameHere!Combo11 LONG,
FORMS!formNameHere!Combo5 LONG ; ' <----
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
WHERE week<= FORMS!formNameHere!Combo11
AND week>= FORMS!formNameHere!Combo5 ' <----
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;



and to only use


DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" & Me.combo7
&
" And [yearnb] <= " & Me.combo9


(note that is it highly preferable to insert space BEFORE each & )

or

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=
FORMS!formNameHere!combo7
And [yearnb] <= FORMS!formNaneHere!combo9 "



Filtering inside the crosstab keep out the undesired week numbers.

Your crosstab generated FIELD names 1, 2, 3, ... they are not values
anymore, but field names, in the crosstab RESULT.
You cannot filter out 'fields name' with a where condition, only the VALUES
under a (one) given field name can be filtered. That is why you have to do
it before using the crosstab result.


Vanderghast, Access MVP

 

[Ticotion]

Hi Michel

Thank you for your input. It help me almost to what I want. Only one thing I
dont understand. Id the user input week 1 to week 12 I get a error message
saying that the microsoft database jet engine does not recognize " as a valid
filed name. If I choose week 1 to week 53, there are no problems. In the SQL
query there are no problems either. Any ideas?

I use the following code:

Private Sub Kommandoknap20_Click()

DoCmd.OpenReport "test1", acPreview, , "[yearnb] >=" & Me.combo7 & " And
[yearnb] <= " & Me.combo9

End Sub


Ticotion

The easiest way is to change the crosstab query:

PARAMETERS FORMS!formNameHere!Combo11 LONG,
FORMS!formNameHere!Combo5 LONG ; ' <----
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
WHERE week<= FORMS!formNameHere!Combo11
AND week>= FORMS!formNameHere!Combo5 ' <----
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;



and to only use


DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" & Me.combo7
&
" And [yearnb] <= " & Me.combo9


(note that is it highly preferable to insert space BEFORE each & )

or

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=
FORMS!formNameHere!combo7
And [yearnb] <= FORMS!formNaneHere!combo9 "



Filtering inside the crosstab keep out the undesired week numbers.

Your crosstab generated FIELD names 1, 2, 3, ... they are not values
anymore, but field names, in the crosstab RESULT.
You cannot filter out 'fields name' with a where condition, only the VALUES
under a (one) given field name can be filtered. That is why you have to do
it before using the crosstab result.


Vanderghast, Access MVP

Hi

I need your help once again

I have a query that is a crosstab showing using the following SQL

TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;

I use this as a datasource for a report. This then gives me a report where
I
have weeks on tob and downwards have department (and also year). I then
want
to use a filter som that I only recieve one row for each the choosen year
and
weeks. I use the following code:

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" &
Me.combo7&
" And [weeknb] >= " & Me.combo5& " And [yearnb] <= " & Me.combo9& " And
[weeknb] <= " & Me.combo11

I then recieve and error because week is not part of my SQL. If I add week
to my SQL I get week downwards and on top of the report.

How can I solve this so I get an report that users can filter showing like
below?


Weekno 1 2 3 4 5 6
Department
dpt 1
dpt2

Please help

regards Ticotion

 

[Michel Walsh]

You say that if your two parameters are 1 and 53 (for starting and ending
week number), then your query works fine but if you use 1 and 12, you got an
error? In the crosstab? or further on? If it is further on, it *may be*
because your code / your report makes a reference to, say week number 13
which does not exists anymore in the second case.


Vanderghast,Access MVP

Hi Michel

Thank you for your input. It help me almost to what I want. Only one thing
I
dont understand. Id the user input week 1 to week 12 I get a error message
saying that the microsoft database jet engine does not recognize " as a
valid
filed name. If I choose week 1 to week 53, there are no problems. In the
SQL
query there are no problems either. Any ideas?

I use the following code:

Private Sub Kommandoknap20_Click()

DoCmd.OpenReport "test1", acPreview, , "[yearnb] >=" & Me.combo7 & " And
[yearnb] <= " & Me.combo9

End Sub


Ticotion

The easiest way is to change the crosstab query:

PARAMETERS FORMS!formNameHere!Combo11 LONG,
FORMS!formNameHere!Combo5 LONG ; ' <----
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
WHERE week<= FORMS!formNameHere!Combo11
AND week>= FORMS!formNameHere!Combo5 ' <----
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;



and to only use


DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" &
Me.combo7
&
" And [yearnb] <= " & Me.combo9


(note that is it highly preferable to insert space BEFORE each & )

or

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=
FORMS!formNameHere!combo7
And [yearnb] <= FORMS!formNaneHere!combo9 "



Filtering inside the crosstab keep out the undesired week numbers.

Your crosstab generated FIELD names 1, 2, 3, ... they are not values
anymore, but field names, in the crosstab RESULT.
You cannot filter out 'fields name' with a where condition, only the
VALUES
under a (one) given field name can be filtered. That is why you have to
do
it before using the crosstab result.


Vanderghast, Access MVP

Hi

I need your help once again

I have a query that is a crosstab showing using the following SQL

TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.dpt, qry_generelOEE.yearnb
FROM qry_generelOEE
GROUP BY qry_generelOEE.dpt, qry_generelOEE.yearnb
PIVOT qry_generelOEE.week;

I use this as a datasource for a report. This then gives me a report
where
I
have weeks on tob and downwards have department (and also year). I then
want
to use a filter som that I only recieve one row for each the choosen
year
and
weeks. I use the following code:

DoCmd.OpenReport "Rpt_fabriksOEEny", acPreview, , "[yearnb] >=" &
Me.combo7&
" And [weeknb] >= " & Me.combo5& " And [yearnb] <= " & Me.combo9& " And
[weeknb] <= " & Me.combo11

I then recieve and error because week is not part of my SQL. If I add
week
to my SQL I get week downwards and on top of the report.

How can I solve this so I get an report that users can filter showing
like
below?


Weekno 1 2 3 4 5 6
Department
dpt 1
dpt2

Please help

regards Ticotion