Dynamic fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a crosstab query that show annual cost per category.as follow:

Category 06 07

a.Roads $324.00 $356.00
b.Buildings $277.00 $305.00

The crosstab always will show the current year and the last year. My problem
is that I want to build another query based on this crosstab, in doing so I
will be faced with a dilemma of having to change the field name next year
since they are going to be "07" and "08". Is there a way to have the field
name change automatically based on the year that we are in. this way the user
does not have to go in the query design to reslect the columns?
thanks
Al
 
The crosstab query:
TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT qryAnnualRenewalCost_1.FY;
*********************************************
The query that I need built on the top of the crosstab is:
SELECT Sum(qryAnnualRenewalCost_2.[06]) AS Lastyr,
Sum(qryAnnualRenewalCost_2.[07]) AS Currentyr,
(([Currentyr]-[Lastyr])/[Lastyr]) AS Percent_Increase
FROM qryAnnualRenewalCost_2;
*********************************************
as you can see, I would rather have the columns named Currentyr and Lastyr
due to the fact that next year the crosstab would show 07 and 08 instead of
06 and 07. As it stands now, I would have to go in the design to change the
expression to match the Crosstab and what I am looking for is a dynamic way
to do the change automatically every year. I hope that clarifies the picture.
thanks
Al
 
Use these two queries ---
Al_X ---
SELECT Max(qryAnnualRenewalCost_1.FY) AS Max_FY
FROM qryAnnualRenewalCost_1;

TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1, Al_X
WHERE (((qryAnnualRenewalCost_1.FY)=[Max_FY] Or
(qryAnnualRenewalCost_1.FY)=[Max_FY]-1))
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT IIf([FY]=[Max_FY],"Current FY","Last FY");

--
KARL DEWEY
Build a little - Test a little


Al said:
The crosstab query:
TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT qryAnnualRenewalCost_1.FY;
*********************************************
The query that I need built on the top of the crosstab is:
SELECT Sum(qryAnnualRenewalCost_2.[06]) AS Lastyr,
Sum(qryAnnualRenewalCost_2.[07]) AS Currentyr,
(([Currentyr]-[Lastyr])/[Lastyr]) AS Percent_Increase
FROM qryAnnualRenewalCost_2;
*********************************************
as you can see, I would rather have the columns named Currentyr and Lastyr
due to the fact that next year the crosstab would show 07 and 08 instead of
06 and 07. As it stands now, I would have to go in the design to change the
expression to match the Crosstab and what I am looking for is a dynamic way
to do the change automatically every year. I hope that clarifies the picture.
thanks
Al


KARL DEWEY said:
Yes, post your crosstab query SQL.
Click to expand...
Click to expand...
 
You probably need to go back to qryAnnualRenewalCost_1.

If that query always has two years and if you are using a parameter query
then you could do something like

Field: FY: IIF(FYYear = Forms!FormName!TxtYear, "CurrentYr","LastYr")

Or if things are based on the current year

IIF(Year(Date()) = Year(SomeField),"CurrentYr", "LastYr")

Or perhaps in the crosstab, you could modify the Pivot Clause

PIVOT IIF(FY MOD 100 = Year(Date()) Mod 100, "CurrentYr","PriorYr")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Al said:
The crosstab query:
TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT qryAnnualRenewalCost_1.FY;
*********************************************
The query that I need built on the top of the crosstab is:
SELECT Sum(qryAnnualRenewalCost_2.[06]) AS Lastyr,
Sum(qryAnnualRenewalCost_2.[07]) AS Currentyr,
(([Currentyr]-[Lastyr])/[Lastyr]) AS Percent_Increase
FROM qryAnnualRenewalCost_2;
*********************************************
as you can see, I would rather have the columns named Currentyr and Lastyr
due to the fact that next year the crosstab would show 07 and 08 instead
of
06 and 07. As it stands now, I would have to go in the design to change
the
expression to match the Crosstab and what I am looking for is a dynamic
way
to do the change automatically every year. I hope that clarifies the
picture.
thanks
Al


KARL DEWEY said:
Yes, post your crosstab query SQL.
Click to expand...
Click to expand...
 
Thank you very much Karl/John. This helps a lot. I made a little change,
however, that would eleminate the need for the first query, using the format
Date() here it is:
************************************************
TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1
WHERE (((qryAnnualRenewalCost_1.FY)=Format(Date(),"yy") Or
(qryAnnualRenewalCost_1.FY)=Format(DateAdd("yyyy",-1,Date()),"yy")))
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT IIf([FY]=Format(Date(),"yy"),"Current FY","LastFY");
*************************************************
thanks again. Please, let me know if you think that this would not work for
next year automatically. I will still check the replies.
thanks
Al

KARL DEWEY said:
Use these two queries ---
Al_X ---
SELECT Max(qryAnnualRenewalCost_1.FY) AS Max_FY
FROM qryAnnualRenewalCost_1;

TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1, Al_X
WHERE (((qryAnnualRenewalCost_1.FY)=[Max_FY] Or
(qryAnnualRenewalCost_1.FY)=[Max_FY]-1))
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT IIf([FY]=[Max_FY],"Current FY","Last FY");

--
KARL DEWEY
Build a little - Test a little


Al said:
The crosstab query:
TRANSFORM Sum(qryAnnualRenewalCost_1.SumOfCost) AS SubTotal
SELECT qryAnnualRenewalCost_1.Category
FROM qryAnnualRenewalCost_1
GROUP BY qryAnnualRenewalCost_1.Category
PIVOT qryAnnualRenewalCost_1.FY;
*********************************************
The query that I need built on the top of the crosstab is:
SELECT Sum(qryAnnualRenewalCost_2.[06]) AS Lastyr,
Sum(qryAnnualRenewalCost_2.[07]) AS Currentyr,
(([Currentyr]-[Lastyr])/[Lastyr]) AS Percent_Increase
FROM qryAnnualRenewalCost_2;
*********************************************
as you can see, I would rather have the columns named Currentyr and Lastyr
due to the fact that next year the crosstab would show 07 and 08 instead of
06 and 07. As it stands now, I would have to go in the design to change the
expression to match the Crosstab and what I am looking for is a dynamic way
to do the change automatically every year. I hope that clarifies the picture.
thanks
Al


KARL DEWEY said:
Yes, post your crosstab query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that show annual cost per category.as follow:

Category 06 07

a.Roads $324.00 $356.00
b.Buildings $277.00 $305.00

The crosstab always will show the current year and the last year. My problem
is that I want to build another query based on this crosstab, in doing so I
will be faced with a dilemma of having to change the field name next year
since they are going to be "07" and "08". Is there a way to have the field
name change automatically based on the year that we are in. this way the user
does not have to go in the query design to reslect the columns?
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Back
Top