Crosstab

T

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
 
K

KARL DEWEY

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

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
 
T

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


Michel Walsh said:
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 said:
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
Click to expand...
Click to expand...
 
M

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


Ticotion said:
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


Michel Walsh said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

grafs and filters 10
Excel and filter 1
Query question 3

Top