Where Criteria using a text box

[sps2]

Beginer Guy again

I have a text box on my form and on my report with this in it;

=Sum(nz([KeysReceived])-nz([KeysShrinkage])-nz([KeysIssued])
+nz([KeysReturned]))

Works great, but because it's not a field in any table I can't figure
out how to do a where clause on the results of this formula in my
report.

The results of this formula give me the total number of keys on hand.
I want my report to show me only the results that are below 2

When I put the textbox in the query builder it shows it as Expr1:
[keysonhand] and when I run the report it ask for the parameter
value. I was tying to use the where criteria to only show >=1, but it
isn't working.

Thanks for any help.

 

[Guest]

BG,

If you read the Help for the Nz function it says that the function, when
used in a query, always returns a zero length string when the tested value is
null unless you specify otherwise. So even if you did get the query working
you would sooner or later run into trouble. Thus it is better to specify each
of your elements as Nz([KeysReceived],0) forcing the return of zero when a
null value is encountered.

OK, you say the execution of the query asks for a parameter value. It does
this if there is any term in the SQL that it cannot resolve assuming this to
be a parameter. Thus the SQL parser does not understand what you intend.

Another concern is your use of the Sum function. Yes this is relevant for
reports and sums values across sections which usually means across records.
It has no use (as far as I know) in standard queries. (Yes there is a sum
function in agregate queries.) Now I assume that KeysReceived, etc. are all
fields/columns on a table and that you want to include only those records on
your report where the sum FOR THE RECORD is <= 2. If I'm wrong then ignore
the remainder of this post.

If you are using the Query Grid then enter the following in the Field cell

KeysReturned:Nz([KeysReceived],0)-Nz([KeysShrinkage],0)+........

Now in the criteria cell enter <=2

Out of interest look at the actual SQL generated. You will see the
expression is included in the SELECT phrase and referred to AS KeysReturned.
The expression is again repeated in the WHERE clause with the comparison of
<=2. The SQL does not say: WHERE KeysReturned <= 2 - this does not work.

Hope you find this useful. If however you are summing across groups of
records then the situation is rather different.

Regards,

Rod

 

[Guest]

BG,

I've been thinking some more about your situation and the conclusion is that
by implication you are summing across groups of records and therefore an
aggregate query is appropriate.

If you use the Access Query Grid to set up this query (click on the sigma
sign on the toolbar) then do as I described before and enter

KeysReturned:Nz([KeysReceived],0)-Nz([KeysShrinkage],0)+........

in the Field cell. Again enter <=2 in the Criteria cell. Now make sure you
select Sum in the Total cell. (There will also be one or more fields with the
Total cell set to Group By.)

If you examine the SQL you will notice that the criterion clause is now
specified as a HAVING clause and not a WHERE clause. The difference? Well
HAVING is evalutated AFTER the result set has been extracted, grouped,
summed, etc. which is what you want. WHERE accepts or rejects records at the
time of extraction which is not what you want.

It is possible that on reviewing the query design Access will have encased
the expression inside a Sum function and changed the Total cell to be
Expression. This is very similar to the situation you describe in your post.
The two ways are equivalent.

Rod

 

[sps2]

BG,

I've been thinking some more about your situation and the conclusion is that
by implication you are summing across groups of records and therefore an
aggregate query is appropriate.

If you use the Access Query Grid to set up this query (click on the sigma
sign on the toolbar) then do as I described before and enter

KeysReturned:Nz([KeysReceived],0)-Nz([KeysShrinkage],0)+........

in the Field cell. Again enter <=2 in the Criteria cell. Now make sure you
select Sum in the Total cell. (There will also be one or more fields with the
Total cell set to Group By.)

If you examine the SQL you will notice that the criterion clause is now
specified as a HAVING clause and not a WHERE clause. The difference? Well
HAVING is evalutated AFTER the result set has been extracted, grouped,
summed, etc. which is what you want. WHERE accepts or rejects records at the
time of extraction which is not what you want.

It is possible that on reviewing the query design Access will have encased
the expression inside a Sum function and changed the Total cell to be
Expression. This is very similar to the situation you describe in your post.
The two ways are equivalent.

Rod

Beginer Guy again

I have a text box on my form and on my report with this in it;

=Sum(nz([KeysReceived])-nz([KeysShrinkage])-nz([KeysIssued])
+nz([KeysReturned]))

Works great, but because it's not a field in any table I can't figure
out how to do a where clause on the results of this formula in my
report.

The results of this formula give me the total number of keys on hand.
I want my report to show me only the results that are below 2

When I put the textbox in the query builder it shows it as Expr1:
[keysonhand] and when I run the report it ask for the parameter
value. I was tying to use the where criteria to only show >=1, but it
isn't working.

Thanks for any help.- Hide quoted text -

- Show quoted text -

Thank you for responding, please don't be scared off, I don't think I
explained my problem good enough. Everything you worte above works
perfectly, but what I need to do is this....

(FORM)
Name KeysOrderd KeysRecived KeysIssued KeysReturned
KeysShrinkage
Central Key 5 5
0 0 0
Linda 0 0
1 0 0
Frank 0 0
1 0 0
Linda 0 0
0 1 0

"KeysOnHand" =Sum(nz([KeysReceived])-nz([KeysShrinkage])-
nz([KeysIssued])
+nz([KeysReturned])) result 4

(REPORT)
The report is setup the same way, and when I run it I get the
"KeysOnHand" result for every key (over a thousand). What I need is
to only see "KeysOnHand" results of 1 or less

problem is "KeysOnHand" is textbox not attached to a table, so I can't
figure out how to limit the results on the report to those
"KeysOnHand" that are less than 2.

Can I have field in my table that will add this information accross
several records or is the textbox the only way to get that result.
maybe a "setvalue" macro or somthing.

Anyway I thank you very much for your help, I know it's frustrating
trying to help someone that's not quite there yet. But I'm getting
there.

 

[sps2]

BG,

I've been thinking some more about your situation and the conclusion is that
by implication you are summing across groups of records and therefore an
aggregate query is appropriate.

If you use the Access Query Grid to set up this query (click on the sigma
sign on the toolbar) then do as I described before and enter

KeysReturned:Nz([KeysReceived],0)-Nz([KeysShrinkage],0)+........

in the Field cell. Again enter <=2 in the Criteria cell. Now make sure you
select Sum in the Total cell. (There will also be one or more fields with the
Total cell set to Group By.)

If you examine the SQL you will notice that the criterion clause is now
specified as a HAVING clause and not a WHERE clause. The difference? Well
HAVING is evalutated AFTER the result set has been extracted, grouped,
summed, etc. which is what you want. WHERE accepts or rejects records at the
time of extraction which is not what you want.

It is possible that on reviewing the query design Access will have encased
the expression inside a Sum function and changed the Total cell to be
Expression. This is very similar to the situation you describe in your post.
The two ways are equivalent.

Rod

Beginer Guy again

I have a text box on my form and on my report with this in it;

=Sum(nz([KeysReceived])-nz([KeysShrinkage])-nz([KeysIssued])
+nz([KeysReturned]))

Works great, but because it's not a field in any table I can't figure
out how to do a where clause on the results of this formula in my
report.

The results of this formula give me the total number of keys on hand.
I want my report to show me only the results that are below 2

When I put the textbox in the query builder it shows it as Expr1:
[keysonhand] and when I run the report it ask for the parameter
value. I was tying to use the where criteria to only show >=1, but it
isn't working.

Thanks for any help.- Hide quoted text -

- Show quoted text -

I understand what your saying about "WHERE" and "HAVING" but seems
either way i set this up it still accepts or rejects records at the
time of extraction instead after it gets a total, resulting in
inccorrect results.

 

[sps2]

BG,

I've been thinking some more about your situation and the conclusion is that
by implication you are summing across groups of records and therefore an
aggregate query is appropriate.

If you use the Access Query Grid to set up this query (click on the sigma
sign on the toolbar) then do as I described before and enter

KeysReturned:Nz([KeysReceived],0)-Nz([KeysShrinkage],0)+........

in the Field cell. Again enter <=2 in the Criteria cell. Now make sure you
select Sum in the Total cell. (There will also be one or more fields with the
Total cell set to Group By.)

If you examine the SQL you will notice that the criterion clause is now
specified as a HAVING clause and not a WHERE clause. The difference? Well
HAVING is evalutated AFTER the result set has been extracted, grouped,
summed, etc. which is what you want. WHERE accepts or rejects records at the
time of extraction which is not what you want.

It is possible that on reviewing the query design Access will have encased
the expression inside a Sum function and changed the Total cell to be
Expression. This is very similar to the situation you describe in your post.
The two ways are equivalent.

Rod

Beginer Guy again

I have a text box on my form and on my report with this in it;

=Sum(nz([KeysReceived])-nz([KeysShrinkage])-nz([KeysIssued])
+nz([KeysReturned]))

Works great, but because it's not a field in any table I can't figure
out how to do a where clause on the results of this formula in my
report.

The results of this formula give me the total number of keys on hand.
I want my report to show me only the results that are below 2

When I put the textbox in the query builder it shows it as Expr1:
[keysonhand] and when I run the report it ask for the parameter
value. I was tying to use the where criteria to only show >=1, but it
isn't working.

Thanks for any help.- Hide quoted text -

- Show quoted text -

This is what I get when I do it from your second post.
HAVING (((Sum(Nz([KeysReceived],0)+Nz([KeysReturned],0)-
Nz([KeysIssued],0)-Nz([KeysShrinkage],0)))<=1));

But it executes the <=1 criteria for each field and then does the
math, instead of doing the math and then executing the criteria.

 

[Guest]

BG,

I'm still here

Let's answer the simple question first.

<Can I have field in my table that will add this information accross
several records or is the textbox the only way to get that result.
maybe a "setvalue" macro or somthing. >

No! At least not without some messy, complicated coding and anyway including
such fields would be contrary to the principles of good data base design. The
point being that these principles have evolved from 'best practice' and are
not simply arbitrary rules to be mindlessly followed.

I am sure that the HAVING clause did not work the way you describe. What is
probably happening is that the grouping is creating one sum for each field
and thus the comparison only appears to be working at the field level.

While on the subject of grouping there is something you're not telling me.
In your example you describe four records across which the summation is
performed. But what identifies these records as belonging to the same group?
If your data base design is correct there should be another table related to
the table containing the keys data that defines the groups. OK, sometimes it
is expedient to denormalise such grouping and do away with the parent table.
Moreover I suspect the keys data is not properly normalised - but these are
side issues; I will explain in another post if you want.

It does not matter that KeysOnHand is a computed value that does not exist
as a table field. If you can compute it on the report you can compute it in
SQL.

I now have the suspicion that you want your report to show details (i.e.
people and keys) but that you also want to restrict this detail to groups
where a certain condition applies (i.e. KeysOnHand <= 1). An aggregate query
is the only way to achieve the comparison at the group level but by very
definition this loses the detail. So how to provide both the detail while
performing the test at a group level?

To do this you must use a nested query. Let me explain the principle but to
do so I must introduce this missing GroupId concept. The inner query is an
aggregate query that returns only the GroupIds that satify the condition. The
outer query selects all the details but only for those GroupIds returned by
the inner query. The general format of such a query is OuterSelect WHERE
GroupId In(InnerAggregate)

Not knowing your design I cannot be more specific.

Regards,

Rod