RE: Dynamic fields
- From: KARL DEWEY <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 10 Sep 2007 08:30:07 -0700
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" wrote:
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" wrote:
Yes, post your crosstab query SQL.
--
KARL DEWEY
Build a little - Test a little
"Al" wrote:
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
- Follow-Ups:
- RE: Dynamic fields
- From: Al
- RE: Dynamic fields
- Prev by Date: RE: Append Query to a different field name
- Next by Date: Re: Dynamic fields
- Previous by thread: RE: Append Query to a different field name
- Next by thread: RE: Dynamic fields
- Index(es):
Relevant Pages
|
