|
| 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
November 30th, 2009, 03:23 AM
posted to microsoft.public.access.reports
|
|||
|
|||
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
November 30th, 2009, 01:45 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 05:29 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 05:29 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 05:29 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 01:54 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 01:54 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
December 2nd, 2009, 01:54 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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 | |
|
|
Linear Mode
