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!

 

[ajetrumpet]

yOU'LL have to clarify the following before you'll get any help I would assume:

I want to multiply "Average NB Written Premium" by "Ultimate NB Policy Count."

are you describing what you want AFTER you've done initial calculations on the table in your head? If so, please tell us what you've already done, because the "lingo" is not understood by most that are not in the industry.

 

[egiannaros]

Sorry for the confusion.

I have not done any calcutions yet. All I currently have is that table you are looking at in my first post.

From that table I want to do a Make Table Query that multiplies the numberical values (the SumofValue Field) for "Average NB Written Premium" by "Ultimate NB Policy Count."

"Average NB Written Premium" * "Ultimate NB Policy Count" = "NB Written Premium Total"

"NB Written Premium Total" is what I'm trying to create in the new table.

Let me know if you need further clarification. Thanks.

 

[ajetrumpet]

Sorry for the confusion.

I have not done any calcutions yet. All I currently have is that table you are looking at in my first post.

From that table I want to do a Make Table Query that multiplies the numberical values (the SumofValue Field) for "Average NB Written Premium" by "Ultimate NB Policy Count."

"Average NB Written Premium" * "Ultimate NB Policy Count" = "NB Written Premium Total"

"NB Written Premium Total" is what I'm trying to create in the new table.

Let me know if you need further clarification. Thanks.

Once again, you're saying that one of the multipliers is "Ultimate NB Policy Count". But .... that is not a field in your table...????

 

[egiannaros]

Haha sorry. I hopefully I get it right this time.

"Average NB Written Premium" is in the "Measures" field.
"Ultimate NB Policy Count" is in the "Measures" field.

So I want to multiply the numerical values from the "SumofValue" field for the records that have those measures AND then do a Group By on all other fields in the table. Then in the new table that value will be given the name "NB Written Premium Total" for the "Measures" field.

I'm really hopping this makes sense now.

 

[Norie]

So the 'Measures' field is text and identifies what the sum refers to?

Is the data you've posted for a query or is it an actual table?

One thing you should note is that generally it's not a good idea to do this sort of thing, the make table part in particular.

Calculations should be done in queries but the results shouldn't be stored in tables.

Perhaps if you can give an example(s) of the result you want/expect it might clarify things?

 

[ajetrumpet]

try this out and see if it gives you any more explanation ideas:

SELECT Measures, 

SUM([sumofvalue]) As RecordTotal FROM table

GROUP BY [measures]

So I want to multiply the numerical values from the "SumofValue" field for the records that have those measures AND then do a Group By on all other fields in the table..

I don't think this is possible. It might be easier to see the entire table actually.

 

[egiannaros]

So the 'Measures' field is text and identifies what the sum refers to?

Yes.

Is the data you've posted for a query or is it an actual table?

That is the only 4 records of a table. I am only showing 4 records because the table is enormous.

One thing you should note is that generally it's not a good idea to do this sort of thing, the make table part in particular.

Calculations should be done in queries but the results shouldn't be stored in tables.

Are you saying use a Select Query instead of a Make Table Query? I'm still kind of new to Access.

All I need is for a query to multiply those two measures (where the records match in all other fields).

Below is my futile attempt to build the calculation into the "SumofValue" field in design view of a query i built:

SumOfValue: Sum(IIf([zAverage Premium 2 (YE Numbers)]![Measures]="Average NB Written Premium",[zAverage Premium 2 (YE Numbers)]![SumOfValue])*IIf([zAverage Premium 2 (YE Numbers)]![Measures]="Ultimate NB Policy Count",[zAverage Premium 2 (YE Numbers)]![SumOfValue]))

zAverage Premium 2 (YE Numbers) = The table i pasted into my first post. This is the source data for this query.

The query runs but the "SumofValue" field is blank for all records. I thought this would be a relatively routine task in Access but I guess I have a lot to learn. Any feedback guys??

 

[Norie]

I'm sorry but this still isn't clear.

How can you multiply text?

Also why are you multiplying the Iifs, and why are they returning the same field?



Wouldn't the value of a policy type be, no of policies of that type x average price?

Or even just no of policies x price?

 

[egiannaros]

How can you multiply text?

Also why are you multiplying the Iifs, and why are they returning the same field?

I'm not trying to multiply text. I want the corresponding SumofValue field's number to be what is multiplied.

Here is an example below:
<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>2011</TD><TD borderColor=#c0c0c0 align=right>3515.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>2011</TD><TD borderColor=#c0c0c0 align=right>4.06602104153363</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Ok. See how the first record has the measure name "Average NB Written Premium" and the second record has the measure name "Ultimate NB Policy Count"?

All I want to do is (3515.55261301715)*(4.06602104153363) = 14294.3108

So I want this calculation done for each matching record. What I mean by matching record is that the following fields would be identical for the calculation to occure: Cube, Version, Source, Term, Channel, Rate Level, LOB, Region, Period

With that said...what I currently have is that table "zAverage Premium 2 (YE Numbers)" with all the data. So I was hoping I could run a query to do the calculation that i just explained.

Hopefully I broke the communication barrier this time