If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab
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 |
#2
|
|||
|
|||
Crosstab
Maybe because you have qry_generelOEE.week and [weeknb]. Try using the
same name. -- KARL DEWEY Build a little - Test a little "Ticotion" wrote: 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 |
#3
|
|||
|
|||
Crosstab
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" wrote in message ... 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 |
#4
|
|||
|
|||
Crosstab
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" wrote: 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" wrote in message ... 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 |
#5
|
|||
|
|||
Crosstab
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|