Criteria for calculated field in query ... <>0

[gusbrunston]

Hi,
Have a calculated field in a query based on calculated sums earlier in the query. I need to suppress records when the final calculated field equals zero. To simplify here are three fields in the query:

Field: Sum Of SecurityDeposit:_
            SecurityDeposit
Table: tblSecurityDeposits
Total: Sum 

Field: Sum Of Refunded: Refunded
Table: tblSecurityDeposits
Total: Sum

Field: LastBalance:[Sum Of SecurityDeposit]_
            -[Sum Of Refunded]
Table:            
Total: Expression

My problem: Can't get a criteria (that works) entered for the third field, &quot;LastBalance&quot;. I need to suppress records with a last balance of zero. (This is the best argument I know of for storing calculated values! Mea culpa!) When I enter a criteria (<> 0) then I get no records.

Your help would be appreciated.


Gus Brunston
An old PICKer
padregus@home.com

 

[donnadee]

Why are you doing a 0 after <>?

 

[cmmrfrds]

This syntax is for an Sql Server table so the syntax for an Access table might be slightly different.

Select
(sum(isnull(SecurityDeposit,0)) + sum(isnull(Refunded,0))) as TotalSum
from tblSecurityDeposits
where TotalSum > 0

 

[GingerR]

try leaving out your LastBalance calculation and putting it in a separate query based on the query above.

g

 

[gusbrunston]

Hi.
Thanks to all for your attention to my thread.
(Donnadee: I was trying to suppress listings in a report when the result of the included calculations was zero.)
Before I solved my problem another popped up: with my groupings and sortings I managed to omit lines from my report that included the same last name as the preceding line. I solved both problems (suppress lines ending in zero, and show all lines even with same last name) by changing my query into a make-table query, and using the resultant &quot;made table&quot; as the foundation of the report. Then I could suppress zeroes and groupings didn't matter. Now I have to find out how to automate the running of the make-table query before the report is run. It's seems never-ending, but with the help of interested people on this web site, I'm sure there's light at the end of the tunnel.
Thanks again,
Gus Brunston
An old PICKer
padregus@home.com

 

[KyleS]

Gus, now that you've got your table &quot;made table&quot; change your make table query to an Append query (leave everything else the same) Now create a Delete query to empty the table. On the report's &quot;OnOpen&quot; event, pu tin this code:

DoCmd.OpenQuery (&quot;[DeleteQueryName]&quot
DoCmd.OpenQuery (&quot;[AppendQueryName]&quot

this will automaticall delete all the info in your &quot;made Table&quot; and reinput the current data.

Hope this helps...

Kyle )

 

[GingerR]

gus--yes do what kyle says. using MakeTable query bloats the db much much quicker than DELETE then APPEND.

i always use make-table first, tho, to get the structure of the table, like you did. then change to APPEND, etc...

g

 

[gusbrunston]

Hi again, Kyle and Ginger (I can't help but wonder if the &quot;R&quot; stands for &quot;Rogers&quot:

Thanks so much for your help. About replacing the maketable query with delete and append queries...

I'm using Access2000.

Using the make table query each time the report is run deletes the previous table, and replaces it with a new table based on the criteria in the query (instantaneously). This seems to work great: there are at present approximately 95 records and the table might grow to a few hundred.

On the surface it seems to me that there will be either a &quot;made table&quot; or an &quot;appended table&quot; in the database. From a neophyte's view, how does the &quot;made table&quot; bloat the database more than the &quot;appended table&quot;?

After backing up my db, I toyed with changing the maketable query to delete and append queries, but then I got scared: &quot;If it ain't broke don't fix it!&quot; I'm not arguing with your recommendations, just wondering about the relative advantage of changing.

If you choose to comment, your answers will be given very careful attention.

Gracias

Gus Brunston
An old PICKer
padregus@home.com

 

[gusbrunston]

Hi, again.
OK, I figured out the delete and append queries. I was having problems with the query design (don't ask).
Thanks again for your help.

Gus Brunston
An old PICKer
padregus@home.com