Query help please

[Bill N]

I’m a newcomer to HTR and Access. I’ve been reading through the posts here, reading a book I bought and trying out some simple queries. So far so good but I have a few questions.

First, I am trying a simple query that uses nDS, rFR1, rKrat and nAFIN. I’ve set rKrat and rAFIN for Avg and am setting my criteria for distance and rFR1.

If I use rFR1-1, rFR2, etc., one ranking at a time, it works fine. But when I use rFR1 <=3 it returns the values by FR1 ranking: a row for each ranking 1, 2, 3. How do I get those numbers consolidated into one row? Lumping 1, 2 and 3 together.

In the same query I’m dividing the Avg Of rKrat/Avg Of nAFIN. When I run the query a message box pops up and asks me to “Enter parameter value” for Avg Of rKrat, then nAFIN. If I just leave the boxes empty and hit ok the query will run but I have the same thing come up every time I run the query. If I try to enter the heading: Avg Of rKrat, etc. I get a (!) message box telling me the parameters are wrong or that that it is too complex to evaluate.

Last problem is a bug in IE. When I try to use the Access help files (those that call for IE) I get a message that says (!) An error has occurred in the script on this page. Etc, etc. I searched the MS website and the fix is to disable script debugging and uncheck ‘display a notification about every script error’. Didn't fix the problem. I can't dismiss the warning, keeps popping back up until I close the help files. Tried reinstalling Access, updating to the latest SP, etc. Anyone else have this problem? Access 2000, XP Home, IE6.

Thanks, Bill

[MikeDee]

using the math functions has limits as to what you can do in the same query when you are doing things like average, sum, etc.

I would do this in 3 steps building a query on a query.

first query

In the first query I would use the 4 fields you want but just set the rfr1 to <=3 and save it.

Note: 0 is a vaild result in this query and you may want to say >=1 and <=3 here.


second query

only put "query 1" in the design grid
put the 3 fields of nds rkrat and nafn and do your average of k and fin. You already know that you are only averaging the (<=3) fr1

this will give you one row per nds value when you run it.


third query

access will not let you divide the ave k and ave fin in the query above as this does not conform the math functions type query.

make a 3rd query using only the second query above in the design
put the 3 fields in the design of the query and save it.

now put the following the a 1st empty field box
K/A: [AvgOfrKrat]/[AvgOfnAFIN]

You can name it what ever you want access will name it expr1

nDS AvgOfrKrat AvgOfnAFIN K/A
1 3.99 4.72 0.85
2 3.61 4.38 0.82
3 4.66 4.37 1.07
4 5.23 5.14 1.02

I also used the properities of the criteria to set the decimal places to 2 to make the result easier on the eye.

no clue here on the the IE and access help problem

[Victor]

I knew this was WAY over my head! That's why I didn't tackle this "simple" question. :)

[Bill N]

Thanks Mike, I'll give this a try. I would never have figured that out on my own, it didn't occur to me to query a query. Opens up all sorts of possibilities!

Bill

[hurrikane]

you could reduce it to 2 queries by putting the Field rFr1, putting the functions on 'where', and criteria <=3

This makes the field not visible but will run your calcs only against horses less than or equal to fr1 = 3.
when you ran it Bill it was on 'Group By' which is why it broke them out into categories.

you can also use 'between 1 and 3' instead of >0 and <=3. I find it easier but you be the judge....