I am afraid I am a little rusty WRT MS Access (the last version I used
was MS Access 2000, and THAT was only to fix to referential integrity
problems and create additional queries for an Access app that someone
else had created and couldn't fix).  I am presently working against MS
Access  2003.

I am fixing a form on a little DB.  The form uses a table, and
supports editing it, not just viewing it, and there are several
controls at the bottom for viewing summary data.  For example, there
is a column in the table called value, and one of the controls with
summary data contains the sum of the values in that column.  Simple
enough.  But now I have been asked to add an equivalent set of summary
controls, and to divide the data into items sold and not sold.  So the
label that goes with the control having the sum of the value column
will be replaced by the sum of the value column values on rows where
another column has a value <> "sold", and there'd be a second control
below it for the sum of values in the value column for rows where that
other column has the value "sold"

I tried doing this with a pair of queries, as in: SELECT SUM(value)
FROM engines WHERE col1<>"sold";

Now, the SQL is trivially simple, and when I open the queries, they
give me the correct data.  However, when I tried to use the query as a
data source for a text box, and open the form, the values from the
query are not displayed and instead I get a silly string '#NAME'  I do
not understand why this form can't access the data from a pair of
queries that work flawlessly independantly, or even give the control
the SQL statements required to get the right result.

I did try a subform, but it insists on putting the data in a grid,
with controls to navigate through a series of records, and obviously
with the summary data query there is only one record.

Using an expression like "=SUM(value)" seems to work, in that the
total for all records appears, but I have not figured out how to apply
the where clause to that kind of expression.  Just giving the SQL as
the data source gives the same silly string '#NAME' when the form is
opened.  Why isn't it obvious how to set up a pair of controls that
use the same SUM(value) expression, and apply a filter colX="sold" to
one and colX<>"sold" to the other?

I guess I should add that one additional requirement, apart from the
partition of the sums according to whether or not the item is sold, is
that when an item is sold (and the DB updated accodingly), the sums
displayed ought to be updated immediately.  Again, when I use the
subform, despite it being really ugly for this purpose, and change an
item to indicate it has been sold, the controls containing data for
that item update immediately (including one that shows the profit from
the sale), but the summary data is not updated until the next time I
open the form.

There are reasons I prefer to work with MS SQL Server, PostgreSQL or
MySQL, with a C++ or Java GUI.  :-(  This is turning into a reminder
why I am not fond of programming MS Office products.  :-(  Maybe,
admittedly, this is a catch 22 situation where I find programming MS
Office products painful because I don't do it much, and I don't do it
much because it has never been a really rewarding experience for me.

This really ought to be a trivial fix (and would take me all of 10
minutes if I were doing it using JDBC and a product like MS SQL
Server), but I am getting really frustrated and the docs and books I
have at hand are not much help.

Any tips that will help me get this done quickly, would be greatly
appreciated.

thanks

Ted