Convert annual data to monthly
in [Queries]
|
From: merry_fay on 3 Feb 2010 06:13 Hiya, Yes, I've got it now -it works!! Thankyou. The only downside is that it puts all the months as the last columns so I'm going to have to re-oder the other parts of my union query... As a further development point, for some of the other sections, I have a table (Budget TPID) which already has 12 columns for Jan-Dec. Is there a way of using my Months table to create a single calculation in the same cross-tab way rather than writing out Jan*calc, Feb*calc etc Eg. TRANSFORM Sum([Budget TPID]![(Months.Month)]*calculation) I know this doesn't work, but the concept is to use the data from the month field in the table Months to set the field to be used from the table Budget TPID. Months.Month would be the column header in the cross-tab. If not,t hat's fine, it would just be nice to cut down code & develop my knowledge further. Thanks "John W. Vinson" wrote: > On Tue, 2 Feb 2010 08:10:03 -0800, merry_fay > <merryfay(a)discussions.microsoft.com> wrote: > > >Thanks for your response, unfortunately while it may be useful in other > >situations, I need to create 12 fields rather than 12 rows > > Tom's suggestion will still work - just use the field from the 12-row month > table as the Column Header in a crosstab. > -- > > John W. Vinson [MVP] > . >
From: Duane Hookom on 3 Feb 2010 19:35
You can set the order of the derived columns in the Column Headings of the crosstab query. Do you realize if your tables were normalized, you wouldn't be having some of these issues? If you provided more information about your tables, someone might be able to assist. I'm certainly having trouble understanding where "calc" comes from. Duane Hookom MS Access MVP "merry_fay" <merryfay(a)discussions.microsoft.com> wrote in message news:C99D3DFA-1E3A-499D-94E1-D9EFADFA39D3(a)microsoft.com... > Hiya, > > Yes, I've got it now -it works!! Thankyou. The only downside is that it > puts > all the months as the last columns so I'm going to have to re-oder the > other > parts of my union query... > > As a further development point, for some of the other sections, I have a > table (Budget TPID) which already has 12 columns for Jan-Dec. Is there a > way > of using my Months table to create a single calculation in the same > cross-tab > way rather than writing out Jan*calc, Feb*calc etc > > Eg. TRANSFORM Sum([Budget TPID]![(Months.Month)]*calculation) > > I know this doesn't work, but the concept is to use the data from the > month > field in the table Months to set the field to be used from the table > Budget > TPID. > Months.Month would be the column header in the cross-tab. > > If not,t hat's fine, it would just be nice to cut down code & develop my > knowledge further. > > Thanks > > > "John W. Vinson" wrote: > >> On Tue, 2 Feb 2010 08:10:03 -0800, merry_fay >> <merryfay(a)discussions.microsoft.com> wrote: >> >> >Thanks for your response, unfortunately while it may be useful in other >> >situations, I need to create 12 fields rather than 12 rows >> >> Tom's suggestion will still work - just use the field from the 12-row >> month >> table as the Column Header in a crosstab. >> -- >> >> John W. Vinson [MVP] >> . >> |