Calculating a ratio based on letters in a field

  • Thread starter Thread starter Chuck W
  • Start date Start date
C

Chuck W

Hi,
I have a table that has Customer Number, TrxDate, and then ten fields:
AMI1, AMI2, AMI3.......AMI10. These fields all have letters in them which
are either B, D, E, X or Y. I am trying to calculate a ratio for a given
month. The numerator of the ratio is the count of E and the denominator of
the ratio is the count of D+E. So if there are 3 Es and 3Ds then the ratio
is .5. B, X and Y don't count. I want to write a query or queries that will
give me the ratio of all ten AMI fields for the month. Can someone help?

Thanks,
 
A ratio is expressed using two numbers like this --
Apples Oranges Ratio
2 2 1 : 1
2 4 1 : 2
4 6 2 : 3
5 45 1 : 9
6 8 3 : 4

Try this --
SELECT Format([TrxDate], "mmmm yyyy"), (Sum(IIF([AMI1] = "E", 1, 0)) /
Sum(IIF([AMI1] = "D", 1, 0))) AS AMI1_Month, (Sum(IIF([AMI2] = "E", 1, 0)) /
Sum(IIF([AMI2] = "D", 1, 0))) AS AMI2_Month, ....
FROM YourTable
GROUP BY Format([TrxDate], "mmmm yyyy")
ORDER BY Format([TrxDate], "yyyymm");
 
Karl,
Thanks for your help. This worked. One follow up questions. A couple of my
calculations have 0 as a denominator causing a #Error message. If I want to
supress this or just show it as 0, how would I include this into the sql?
Thanks, Chuck

KARL DEWEY said:
A ratio is expressed using two numbers like this --
Apples Oranges Ratio
2 2 1 : 1
2 4 1 : 2
4 6 2 : 3
5 45 1 : 9
6 8 3 : 4

Try this --
SELECT Format([TrxDate], "mmmm yyyy"), (Sum(IIF([AMI1] = "E", 1, 0)) /
Sum(IIF([AMI1] = "D", 1, 0))) AS AMI1_Month, (Sum(IIF([AMI2] = "E", 1, 0)) /
Sum(IIF([AMI2] = "D", 1, 0))) AS AMI2_Month, ....
FROM YourTable
GROUP BY Format([TrxDate], "mmmm yyyy")
ORDER BY Format([TrxDate], "yyyymm");

--
Build a little, test a little.


Chuck W said:
Hi,
I have a table that has Customer Number, TrxDate, and then ten fields:
AMI1, AMI2, AMI3.......AMI10. These fields all have letters in them which
are either B, D, E, X or Y. I am trying to calculate a ratio for a given
month. The numerator of the ratio is the count of E and the denominator of
the ratio is the count of D+E. So if there are 3 Es and 3Ds then the ratio
is .5. B, X and Y don't count. I want to write a query or queries that will
give me the ratio of all ten AMI fields for the month. Can someone help?

Thanks,
Click to expand...
Click to expand...
 
Hi,
I have a table that has Customer Number, TrxDate, and then ten fields:
AMI1, AMI2, AMI3.......AMI10.
Click to expand...

That sounds like... a spreadsheet!

If you have a one (customer) to many (AMI's) relationship, you should really
consider having a one to many relationship to an AMI table, with ten ROWS
rather than ten fields.
These fields all have letters in them which
are either B, D, E, X or Y. I am trying to calculate a ratio for a given
month. The numerator of the ratio is the count of E and the denominator of
the ratio is the count of D+E. So if there are 3 Es and 3Ds then the ratio
is .5. B, X and Y don't count. I want to write a query or queries that will
give me the ratio of all ten AMI fields for the month. Can someone help?
Click to expand...

Still a bit tricky with a proper normalized design (you could use a Totals
query with a calculated field), but for your current design you'll need either
a monstrously complicated query or a VBA function to parse out these letters
into counts.
 
John,
It is an export from a web based reporting tool that I import in. Not
something I designed. I wanted to substitute a zero for the #Error.
Chuck
 
Try this --
SELECT Format([TrxDate], "mmmm yyyy"), iif(Sum(IIF([AMI1] = "D", 1, 0)= 0,
0,(Sum(IIF([AMI1] = "E", 1, 0)) / Sum(IIF([AMI1] = "D", 1, 0)))) AS
AMI1_Month, ....

--
Build a little, test a little.


Chuck W said:
Karl,
Thanks for your help. This worked. One follow up questions. A couple of my
calculations have 0 as a denominator causing a #Error message. If I want to
supress this or just show it as 0, how would I include this into the sql?
Thanks, Chuck

KARL DEWEY said:
A ratio is expressed using two numbers like this --
Apples Oranges Ratio
2 2 1 : 1
2 4 1 : 2
4 6 2 : 3
5 45 1 : 9
6 8 3 : 4

Try this --
SELECT Format([TrxDate], "mmmm yyyy"), (Sum(IIF([AMI1] = "E", 1, 0)) /
Sum(IIF([AMI1] = "D", 1, 0))) AS AMI1_Month, (Sum(IIF([AMI2] = "E", 1, 0)) /
Sum(IIF([AMI2] = "D", 1, 0))) AS AMI2_Month, ....
FROM YourTable
GROUP BY Format([TrxDate], "mmmm yyyy")
ORDER BY Format([TrxDate], "yyyymm");

--
Build a little, test a little.


Chuck W said:
Hi,
I have a table that has Customer Number, TrxDate, and then ten fields:
AMI1, AMI2, AMI3.......AMI10. These fields all have letters in them which
are either B, D, E, X or Y. I am trying to calculate a ratio for a given
month. The numerator of the ratio is the count of E and the denominator of
the ratio is the count of D+E. So if there are 3 Es and 3Ds then the ratio
is .5. B, X and Y don't count. I want to write a query or queries that will
give me the ratio of all ten AMI fields for the month. Can someone help?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top