Prompted for Values while Running a Query.

  • Thread starter Thread starter comish4lif
  • Start date Start date
C

comish4lif

I am writing a query to determine value of baseball players based on
past and projected performance. One of the statistical categories that
I wish to measure is called TB (Total Bases). In the Query, TB is
calculated using this formula:

TB: [H]+[2B]+2*[3B]+3*
. So far, so good.

Next in the query, I have a query that formulates the value of [TB] in
a constrained budget using [TB] in the formula.The formula looks like
this:

TB_VALUE:
IIf(0.47+0.49*(([TB]-160)/77)<0,0,0.47+0.49*(([TB]-160)/77))

I have several formulas like this in the query, when I run the query, I
am prompted for the value of [TB] and the others. Why is this? Can I
change the calculation order? How do I make it stop?

This is going to be a bigger problem later when I build forms and
reports and have to clear the prompt each time I reload the form and
rerun the query. Any help?
 
To follow up, maybe another way to ask this is:

Do I really have to create a query to Calculate TB, etc. And then
create a second query to calculate TBVALUE, etc. And then a third query
to calculate TOTALVALUE - which SUM TBValue and other calculated
values?

Really? Isn't that asking for a lot of processing each time I run a
query - it'll rerun 3 queries. Right, because it is bad format to store
something that you can calculate....
 
I am writing a query to determine value of baseball players based on
past and projected performance. One of the statistical categories that
I wish to measure is called TB (Total Bases). In the Query, TB is
calculated using this formula:

TB: [H]+[2B]+2*[3B]+3*
. So far, so good.

Next in the query, I have a query that formulates the value of [TB] in
a constrained budget using [TB] in the formula.The formula looks like
this:

TB_VALUE:
IIf(0.47+0.49*(([TB]-160)/77)<0,0,0.47+0.49*(([TB]-160)/77))

I have several formulas like this in the query, when I run the query, I
am prompted for the value of [TB] and the others. Why is this?
Click to expand...



I don't see that problem. Maybe you're doing something else
with those calculated fields??

Note that you can not use the name of a calculated field in
any other clause (e.g. Where, Group By, Order By). If
that's the issuek, you need to either duplicate the
calculation in the other clause(s), or base another query on
this query.
 
If TB=[H]+[2B]+2*[3B]+3*


then

IIf(0.47+0.49*(([TB]-160)/77)<0,0,0.47+0.49*(([TB]-160)/77))
could be rewritten as

IIf(0.47+0.49*((([H]+[2B]+2*[3B]+3*
)-160)/77)<0,0,0.47+0.49*((([H]+[2B]+2*[3B]+3*
)-160)/77))

I think you are mistating the problem. You are not running a query
which calcualte TB, then another query which calculates TB_VALUE,

But a single query wherein 1 Field is TB, as defined, then another FIELD
in the same query is defined as TB_Value. The problem is you are
referencing TB, when there is no TB in the data source. You might be
able to set up TB as a parameter, but I think changing your formula as
above should work pretty well.
 
I think that I agree with you on this - I shouldn't be referencing a
Field that is not in the data source. Stated that way, it makes sense.

Your suggested formula is longer, but not big enough to be a problem.

For anyone who is interested, I also spent soe time yesterday creating
the cascading queries. That is, define [TB] in query1. Define
[TB_Value] in query2, using query1 as the data source, then define
[Total_Value] in query3, using query2 as the data source. I am dealing
with about 600 records, and didn't notice any performance issues.

Thanks for the always solid help in here.
 
Back
Top