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
|
|||
|
|||
To have selected the year on combo box
Hello,
The following crosstab query can produce the name of our church offices sideway ( year to year comparison), but how can we make it to seledt the year on combo, and then the report will produce it for the last 5 years: since my landscape report can only accomodate for 5 years while my data is already 10 years These is my query: TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM PelayanJemaatbyYear GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan PIVOT PelayanJemaatbyYear.TahunPel; Thanks for any help. -- Frank Situmorang |
#2
|
|||
|
|||
To have selected the year on combo box
You would need to define parameters in the query and insert a where clause.
Since I don't now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Hello, The following crosstab query can produce the name of our church offices sideway ( year to year comparison), but how can we make it to seledt the year on combo, and then the report will produce it for the last 5 years: since my landscape report can only accomodate for 5 years while my data is already 10 years These is my query: TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM PelayanJemaatbyYear GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan PIVOT PelayanJemaatbyYear.TahunPel; Thanks for any help. |
#3
|
|||
|
|||
Sorry John for my query is in Indonesian
The name of the fied that contains the year is : TahunPel
Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
#4
|
|||
|
|||
Sorry John for my query is in Indonesian
The name of the fied that contains the year is : TahunPel
Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
#5
|
|||
|
|||
Sorry John for my query is in Indonesian
The name of the fied that contains the year is : TahunPel
Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
#6
|
|||
|
|||
Sorry John for my query is in Indonesian
PARAMETERS Forms![NameOfTheForm]![ComboTheYear] Long;
TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM PelayanJemaatbyYear WHERE TahunPel Between Forms![NameOfTheForm]![ComboTheYear]-4 and Forms![NameOfTheForm]![ComboTheYear] GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan PIVOT "YR" & (Forms![NameOfTheForm]![ComboTheYear] - PelayanJemaatbyYear.TahunPel) in ("YR1","YR2","YR3","YR4","YR5") You will have to use relative years since you want to use this information in a report. You can change the labels in the report by using an expression like Forms![NameOfTheForm]![ComboTheYear]-4 to get the Year for YR5 John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: The name of the fied that contains the year is : TahunPel Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
#7
|
|||
|
|||
Sorry John for my query is in Indonesian
PARAMETERS Forms![NameOfTheForm]![ComboTheYear] Long;
TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM PelayanJemaatbyYear WHERE TahunPel Between Forms![NameOfTheForm]![ComboTheYear]-4 and Forms![NameOfTheForm]![ComboTheYear] GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan PIVOT "YR" & (Forms![NameOfTheForm]![ComboTheYear] - PelayanJemaatbyYear.TahunPel) in ("YR1","YR2","YR3","YR4","YR5") You will have to use relative years since you want to use this information in a report. You can change the labels in the report by using an expression like Forms![NameOfTheForm]![ComboTheYear]-4 to get the Year for YR5 John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: The name of the fied that contains the year is : TahunPel Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
#8
|
|||
|
|||
Sorry John for my query is in Indonesian
PARAMETERS Forms![NameOfTheForm]![ComboTheYear] Long;
TRANSFORM First(PelayanJemaatbyYear.NamaPel) AS FirstOfNamaPel SELECT PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan, First(PelayanJemaatbyYear.NamaPel) AS [Total Of NamaPel] FROM PelayanJemaatbyYear WHERE TahunPel Between Forms![NameOfTheForm]![ComboTheYear]-4 and Forms![NameOfTheForm]![ComboTheYear] GROUP BY PelayanJemaatbyYear.Nurut, PelayanJemaatbyYear.BidangPelayanan, PelayanJemaatbyYear.CountOfBidangPelayanan PIVOT "YR" & (Forms![NameOfTheForm]![ComboTheYear] - PelayanJemaatbyYear.TahunPel) in ("YR1","YR2","YR3","YR4","YR5") You will have to use relative years since you want to use this information in a report. You can change the labels in the report by using an expression like Forms![NameOfTheForm]![ComboTheYear]-4 to get the Year for YR5 John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: The name of the fied that contains the year is : TahunPel Which is a number, could you help me? Thanks in advance. Frank John Spencer wrote: You would need to define parameters in the query and insert a where clause. 30-Nov-09 You would need to define parameters in the query and insert a where clause. Since I do not now which field contains the year (or a date) and if the field that does so is a number field, a text field, or a date field, I cannot give you more specific advice. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Frank Situmorang wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Accessing IIS Hosted WCF Services from PHP http://www.eggheadcafe.com/tutorials...osted-wcf.aspx |
Thread Tools | |
Display Modes | |
|
|