query

[Guest]

I ask this question earlier and I didnt make myself clear. Hope you can
understant me this time.
[nmlo]<2 with this expression i am trying to count the number of records in
nmlo who are below 2. The numbers in there will range from 0 to 100.---
xfin I would like to count the number of records in xfin. All the numbers
in there will be ones,
but i need there total. Then I would like to divide the number of records in
nmlo by the the number of records in xfin.
What im trying to do in my query is determine the percentage of horses who
nmlo are below 2. xfin is what i choose to determine the total number of
records in this query. I didnt know how else to determine the total number of
records in this query.
So if there are 100 records in this query and 20 have nmlo<2, Manually I
would divide 20/100= 20% I do this for about 15 similar records. It becomes
quite time consuming. I sure appreciate any help ypu you give me. If I still
didnt express myself clearly please dont give up. just say so and Ill try
again.
thanks ed

 

[David S via AccessMonster.com]

It would help a lot if you could post your tables and the fields in them.

[nmlo]<2 with this expression i am trying to count the number of records in
nmlo who are below 2. The numbers in there will range from 0 to 100.---

So, assuming that the table name is something like "Horses", the query
SpecificHorseQuery to get the number of records would be:
SELECT Count([nmlo]) as SpecificHorseCount FROM Horses WHERE [nmlo] < 2

xfin I would like to count the number of records in xfin. All the numbers
in there will be ones,
but i need there total. Then I would like to divide the number of records in
nmlo by the the number of records in xfin.
What im trying to do in my query is determine the percentage of horses who
nmlo are below 2. xfin is what i choose to determine the total number of
records in this query. I didnt know how else to determine the total number of
records in this query.

If all you want is the total number of horses, you don't need this column at
all - you can just get the total number of horses with a similar query to
above (let's call it AllHorsesQuery):
SELECT Count([nmlo]) as AllHorseCount FROM Horses

So if there are 100 records in this query and 20 have nmlo<2, Manually I
would divide 20/100= 20% I do this for about 15 similar records.

You can then use the two earlier queries to calculate the percentage and
display it for each record:
SELECT Horses.*, [SpecificHorseCount]/[AllHorseCount] AS PercentageOfHorses
FROM Horses, SpecificHorseQuery, AllHorseQuery
WHERE (((Horses.nmlo)<2));

We get away with not having any joins at all because SpecificHorseQuery and
AllHorseQuery both return just the one record. The down side of this
particular example above is that the cut off points are hard coded - I think
you could parameterise it by replacing the 2 with [Enter nmlo cut off] or
something like that. Alternatively, you could store the cut off value in a
table, but then we'd need to write some joins to get to it.

Good luck with this, but I don't think it's going to work... I'm sure bigger
and better financed people have tried writing programs to figure out which
horse is going to win a given race, but I think the number of variables is
just too great to accurately predict it. It seems a very good example to me
of Chaos Theory, where a small difference in one of those variables can
produce a widely divergent outcomess...