MONSTER QUERY

[Glint]

Hi Guys,
I need help with this. I needed to display all transactions in an outfit. So
I designed a union query to combine all the transactions. The result worked,
but only after a long lunch break so the query would open; it was that slow.
So I decided to break the query into smaller bits and use them in sub-forms.
I made a smaller union query combining cash donations (positive figures) with
cash expenses as negative. When I used this union query to build another one
so that I could retrieve the sum (or net) of the cash column, I got two
records, one each for the donations and expenses. I had expected only one
record, representing the result of all the additions. Is there a way I can
make the query return just one record after doing the additions and
substractions?
Secondly, I used the query to build a form. The two records displayed
alright. But a textbox I placed with the record source as =Sum([Value])
returned #Error. Why is that?

 

[John W. Vinson]

Hi Guys,
I need help with this. I needed to display all transactions in an outfit. So
I designed a union query to combine all the transactions. The result worked,
but only after a long lunch break so the query would open; it was that slow.
So I decided to break the query into smaller bits and use them in sub-forms.
I made a smaller union query combining cash donations (positive figures) with
cash expenses as negative. When I used this union query to build another one
so that I could retrieve the sum (or net) of the cash column, I got two
records, one each for the donations and expenses. I had expected only one
record, representing the result of all the additions. Is there a way I can
make the query return just one record after doing the additions and
substractions?

Yes; correct the error in your query (which you chose not to post and which we
cannot see).

Secondly, I used the query to build a form. The two records displayed
alright. But a textbox I placed with the record source as =Sum([Value])
returned #Error. Why is that?

For one thing, Value is a reserved word (every control has a Value property!).
Is [Value] the name of the field in the form's recordsource query? Did you put
the Sum textbox in the detail section of the form (won't work!) or in the form
Footer (where it will)?

John W. Vinson [MVP]