Convert annual data to monthly
in [Queries]
|
From: merry_fay on 2 Feb 2010 06:44 Hi, In some of my queries, I'm having to take an annual number & divide it by 12 to get monthly information. This is being joined in a union query to other data which has specific monthly phasing. Is there a way I can get the field to repeat 12 time instead of having to use 'value/12, value/12, value/12 etc? Thanks
From: Duane Hookom on 2 Feb 2010 08:41 "repeat 12 time" how? Across or down? It seems that you could have easily provide some sample records and how you would like them to appear in your query. It would remove much of the guessing. Duane Hookom MS Access MVP "merry_fay" <merryfay(a)discussions.microsoft.com> wrote in message news:B88BB416-E258-41D7-8A16-311C8B1EE000(a)microsoft.com... > Hi, > > In some of my queries, I'm having to take an annual number & divide it by > 12 > to get monthly information. > This is being joined in a union query to other data which has specific > monthly phasing. > > Is there a way I can get the field to repeat 12 time instead of having to > use > 'value/12, value/12, value/12 etc? > > Thanks
From: Tom van Stiphout on 2 Feb 2010 08:49 On Tue, 2 Feb 2010 03:44:01 -0800, merry_fay <merryfay(a)discussions.microsoft.com> wrote: Yes. You could have a new table with the 12 month records in it, and then add this to your query without any joins. This would create a "carthesian product" and each row in your main table will now be duplicated 12 times. -Tom. Microsoft Access MVP >Hi, > >In some of my queries, I'm having to take an annual number & divide it by 12 >to get monthly information. >This is being joined in a union query to other data which has specific >monthly phasing. > >Is there a way I can get the field to repeat 12 time instead of having to use >'value/12, value/12, value/12 etc? > >Thanks
From: merry_fay on 2 Feb 2010 11:10 Hi Tom, Thanks for your response, unfortunately while it may be useful in other situations, I need to create 12 fields rather than 12 rows Regards "Tom van Stiphout" wrote: > On Tue, 2 Feb 2010 03:44:01 -0800, merry_fay > <merryfay(a)discussions.microsoft.com> wrote: > > Yes. You could have a new table with the 12 month records in it, and > then add this to your query without any joins. This would create a > "carthesian product" and each row in your main table will now be > duplicated 12 times. > > -Tom. > Microsoft Access MVP > > > >Hi, > > > >In some of my queries, I'm having to take an annual number & divide it by 12 > >to get monthly information. > >This is being joined in a union query to other data which has specific > >monthly phasing. > > > >Is there a way I can get the field to repeat 12 time instead of having to use > >'value/12, value/12, value/12 etc? > > > >Thanks > . >
From: John W. Vinson on 2 Feb 2010 13:08
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] |