Convert annual data to monthly

[merry_fay]

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

 

[Duane Hookom]

"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

 

[Tom van Stiphout]

On Tue, 2 Feb 2010 03:44:01 -0800, merry_fay

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

 

[merry_fay]

Hi Duane,

Here's a sample of 2 sections of my union query:

UNION ALL SELECT TSL, 'XXX', 'XXX', TPID, null, Co, [Value]/12, [Value]/12,
[Value]/12, [Value]/12, [Value]/12, [Value]/12, [Value]/12, [Value]/12,
[Value]/12, [Value]/12, [Value]/12, [Value]/12, 'E', [Forms]![frm New
Budget]![lstUPVsn], Forms![frm New Budget]![txtCol1], Forms![frm New
Budget]![txtYr], Forms![frm New Budget]![txtUnique]
FROM Projects

UNION ALL SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [BStoTPID].TPID, [1- CostingData].Project, [Budget
TPID].Co,
Sum([jan]*[charge]*[BStoTPID].[allocation]/100),
Sum([feb]*[charge]*[BStoTPID].[allocation]/100),
Sum([mar]*[charge]*[BStoTPID].[allocation]/100),
Sum([apr]*[charge]*[BStoTPID].[allocation]/100),
Sum([may]*[charge]*[BStoTPID].[allocation]/100),
Sum([jun]*[charge]*[BStoTPID].[allocation]/100),
Sum([jul]*[charge]*[BStoTPID].[allocation]/100),
Sum([aug]*[charge]*[BStoTPID].[allocation]/100),
Sum([sep]*[charge]*[BStoTPID].[allocation]/100),
Sum([oct]*[charge]*[BStoTPID].[allocation]/100),
Sum([nov]*[charge]*[BStoTPID].[allocation]/100),
Sum([dec]*[charge]*[BStoTPID].[allocation]/100),
Classification, [Budget TPID].Year, etc etc


Thanks

 

[merry_fay]

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

 

[Duane Hookom]

Fields with names of months scare me. This looks quite unnormalized.

However, consider creating a table [tblNums] with a single numeric field
[Num] and adding records with values of 1 -12 (or more).

Then create a query with SQL like:

TRANSFORM First([Value]/12) AS Expr2
SELECT Projects.TSL, Projects.TPID, Projects.Co, Sum(Projects.Value) AS
SumOfValue
FROM Projects, tblNums
WHERE (((tblNums.Num) Between 1 And 12))
GROUP BY Projects.TSL, Projects.TPID, Projects.Co
PIVOT "Mth" & Format([Num],"00");

This should create 12 columns with value/12 in each of the 12 columns.
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Here's a sample of 2 sections of my union query:

UNION ALL SELECT TSL, 'XXX', 'XXX', TPID, null, Co, [Value]/12, [Value]/12,
[Value]/12, [Value]/12, [Value]/12, [Value]/12, [Value]/12, [Value]/12,
[Value]/12, [Value]/12, [Value]/12, [Value]/12, 'E', [Forms]![frm New
Budget]![lstUPVsn], Forms![frm New Budget]![txtCol1], Forms![frm New
Budget]![txtYr], Forms![frm New Budget]![txtUnique]
FROM Projects

UNION ALL SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [BStoTPID].TPID, [1- CostingData].Project, [Budget
TPID].Co,
Sum([jan]*[charge]*[BStoTPID].[allocation]/100),
Sum([feb]*[charge]*[BStoTPID].[allocation]/100),
Sum([mar]*[charge]*[BStoTPID].[allocation]/100),
Sum([apr]*[charge]*[BStoTPID].[allocation]/100),
Sum([may]*[charge]*[BStoTPID].[allocation]/100),
Sum([jun]*[charge]*[BStoTPID].[allocation]/100),
Sum([jul]*[charge]*[BStoTPID].[allocation]/100),
Sum([aug]*[charge]*[BStoTPID].[allocation]/100),
Sum([sep]*[charge]*[BStoTPID].[allocation]/100),
Sum([oct]*[charge]*[BStoTPID].[allocation]/100),
Sum([nov]*[charge]*[BStoTPID].[allocation]/100),
Sum([dec]*[charge]*[BStoTPID].[allocation]/100),
Classification, [Budget TPID].Year, etc etc


Thanks

"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

 

[John W. Vinson]

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.

 

[merry_fay]

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

 

[Duane Hookom]

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

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

Tom's suggestion will still work - just use the field from the 12-row
month
table as the Column Header in a crosstab.