To have selected the year on combo box

[Frank Situmorang]

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.

 

[John Spencer]

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]

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 fiel
that does so is a number field, a text field, or a date field, I cannot giv
you more specific advice

John Spence
Access MVP 2002-2005, 2007-200
The Hilltop Institut
University of Maryland Baltimore Count

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/tutorial...ba-e3c3295d8277/accessing-iis-hosted-wcf.aspx

 

[John Spencer]

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