Query to Multiply to two Measures in a table

[egiannaros]

I am trying to write a Make Table query that pulls in all the fields from another table but multiplies two meaures to create a new measure.

Below is the table I am using for this query. I want to multiply "Average NB Written Premium" by "Ultimate NB Policy Count." I know I have to use the build feature in Query Design view but I havn't had any luck yet. Any ideas?

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>zAverage Premium 2 (YE Numbers)</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Cube</TH><TH borderColor=#000000 bgColor=#c0c0c0>Version</TH><TH borderColor=#000000 bgColor=#c0c0c0>Source</TH><TH borderColor=#000000 bgColor=#c0c0c0>Term</TH><TH borderColor=#000000 bgColor=#c0c0c0>Channel</TH><TH borderColor=#000000 bgColor=#c0c0c0>Rate_Level</TH><TH borderColor=#000000 bgColor=#c0c0c0>LOB</TH><TH borderColor=#000000 bgColor=#c0c0c0>Region</TH><TH borderColor=#000000 bgColor=#c0c0c0>Measures</TH><TH borderColor=#000000 bgColor=#c0c0c0>Period</TH><TH borderColor=#000000 bgColor=#c0c0c0>SumOfValue</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>sfm_prod:Premiums</TD><TD borderColor=#c0c0c0>Scenario Plan</TD><TD borderColor=#c0c0c0>Reported</TD><TD borderColor=#c0c0c0>12 month</TD><TD borderColor=#c0c0c0>Agt Spon</TD><TD borderColor=#c0c0c0>Home Legacy</TD><TD borderColor=#c0c0c0>Homesaver</TD><TD borderColor=#c0c0c0>AL.Birmingham</TD><TD borderColor=#c0c0c0>Average NB Written Premium</TD><TD borderColor=#c0c0c0>2010</TD><TD borderColor=#c0c0c0 align=right>1410.20250404491</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>sfm_prod:Premiums</TD><TD borderColor=#c0c0c0>Scenario Plan</TD><TD borderColor=#c0c0c0>Reported</TD><TD borderColor=#c0c0c0>12 month</TD><TD borderColor=#c0c0c0>Agt Spon</TD><TD borderColor=#c0c0c0>Home Legacy</TD><TD borderColor=#c0c0c0>Homesaver</TD><TD borderColor=#c0c0c0>AL.Birmingham</TD><TD borderColor=#c0c0c0>Average NB Written Premium</TD><TD borderColor=#c0c0c0>2011</TD><TD borderColor=#c0c0c0 align=right>1785.55261301715</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>sfm_prod:Premiums</TD><TD borderColor=#c0c0c0>Scenario Plan</TD><TD borderColor=#c0c0c0>Reported</TD><TD borderColor=#c0c0c0>12 month</TD><TD borderColor=#c0c0c0>Agt Spon</TD><TD borderColor=#c0c0c0>Home Legacy</TD><TD borderColor=#c0c0c0>Homesaver</TD><TD borderColor=#c0c0c0>AL.Birmingham</TD><TD borderColor=#c0c0c0>Ultimate NB Policy Count</TD><TD borderColor=#c0c0c0>2010</TD><TD borderColor=#c0c0c0 align=right>2.14660729808804</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>sfm_prod:Premiums</TD><TD borderColor=#c0c0c0>Scenario Plan</TD><TD borderColor=#c0c0c0>Reported</TD><TD borderColor=#c0c0c0>12 month</TD><TD borderColor=#c0c0c0>Agt Spon</TD><TD borderColor=#c0c0c0>Home Legacy</TD><TD borderColor=#c0c0c0>Homesaver</TD><TD borderColor=#c0c0c0>AL.Birmingham</TD><TD borderColor=#c0c0c0>Ultimate NB Policy Count</TD><TD borderColor=#c0c0c0>2011</TD><TD borderColor=#c0c0c0 align=right>2.06602104153363</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I am using MS Access 2003.

Thanks in advance for any help. It is greatly appreciated!

 

[Norie]

The only way I can see what you want to do with the current setup would be with some crazy DLookup or DSum.

That's because you have 2 different records.

In fact why do you have this measure field and not 2 different fields for the 'measures'.

ie one field for policy count (or policy ID or something) and one field for premium.

If you had that it would be a straightforward calculation.

<TABLE style="WIDTH: 623pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=831 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=2 width=51><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 77pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=34 width=103>Cube


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=73>Version</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=51>Source</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=51>Term</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=52>Channel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=73>Rate_Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=62>LOB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=79>Region</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 32pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=43>Period</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 borderColor=#c0c0c0 width=75>Ultimate NB Policy Count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 borderColor=#c0c0c0 width=102>Average NB Written Premium</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=67>Calculation</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 borderColor=#c0c0c0>sfm_prod:Premiums</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>Scenario Plan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>Reported</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>12 month</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>Agt Spon</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>Home Legacy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>Homesaver</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0>AL.Birmingham</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0 align=right x:num>2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0 align=right x:num="4.06602104153363">4.066021042


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 borderColor=#c0c0c0 align=right x:num="3515.5526130171502">3515.552613</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num="14294.310897146268" x:fmla="=K2*J2">14294.3109</TD></TR></TBODY></TABLE>