Calculating a ratio based on letters in a field

[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,

 

[KARL DEWEY]

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");

 

[Chuck W]

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

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.

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,

 

[John W. Vinson]

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

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?

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.

 

[Chuck W]

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

 

[KARL DEWEY]

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.

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

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.

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,