Dynamic fieldname

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

Guest

I do some calculations from this month and six months ahead.

In the query the fieldnames containing the result have name like Calc,
Calc+1, Calc+2 and so on...
I would like to change the fieldnames depending on which month I'm starting
with.

I have select the starting year-month in a form.

Is it possible to to automatically generate new fieldnames in a query
depending on a certain value?
 
Item_Prognoses
------------------
Period Itno Name Prognos Prognos+1 Prognos+2 Prognos+3
9 2432 RASQALL 799 803 933 880
9 2450 MCPHERN 173 173 173 141
9 2457 MCPHSON 245 227 296 400

Calendar
----------
No Year Month Year_Month
8 2006 8 200608
9 2006 9 200609
10 2006 10 200610

How would you set up the crosstab query to change the labels Prognos,
Prognos+1, Prognos+2, Prognos+3
to 200609, 200610, 200611, 200612.
Period in Item_Prognoses and No in Calendar is related.

The result is to be like this:
Itno Name 200609 200610 200611 200612
2432 RASQALL 799 803 933 880
2450 MCPHERN 173 173 173 141
2457 MCPHSON 245 227 296 400




"KARL DEWEY" skrev:
 
You realy need to revise your table structure. I used a UNION query to put
the data in the way your structure needs to be. The crosstab query uses that
to do what you want.

Item_Prognoses_1 ---
SELECT Item_Prognoses.Period, Item_Prognoses.Itno, Item_Prognoses.Name,
Item_Prognoses.Prognos
FROM Item_Prognoses
UNION SELECT Item_Prognoses.Period, Item_Prognoses.Itno,
Item_Prognoses.Name, Item_Prognoses.[Prognos+1]
FROM Item_Prognoses
UNION SELECT Item_Prognoses.Period, Item_Prognoses.Itno,
Item_Prognoses.Name, Item_Prognoses.[Prognos+2]
FROM Item_Prognoses
UNION SELECT Item_Prognoses.Period, Item_Prognoses.Itno,
Item_Prognoses.Name, Item_Prognoses.[Prognos+3]
FROM Item_Prognoses;


Item_Prognoses_1_Crosstab ---
TRANSFORM Sum(Item_Prognoses_1.Prognos) AS SumOfPrognos
SELECT Item_Prognoses_1.Itno, Item_Prognoses_1.Name
FROM Item_Prognoses_1 INNER JOIN Calendar ON Item_Prognoses_1.Period =
Calendar.[No]
GROUP BY Item_Prognoses_1.Itno, Item_Prognoses_1.Name
PIVOT Calendar.Year_Month;
 
Back
Top