Query with expressions that allows new records insert

[Fox]

Is there a way to create a query based on one table that totals fields
(without using the grouping) and also allows new records to be added when
it's used to create a form?

 

[Doctor]

If you're just wanting to see the totals in a form, calculate what you want
to see in the form instead of in the query.

To do this, create an unbound text box and set its control source to
calculate the totals for you. For instance setting a control source to
=Count([ClientName]) would count all of the the records that have a value in
ClientName. Setting one to =Sum([ItemCost]) would add all of the ItemCosts
together.

This approach would work so long as you don't mind the totals changing if
you apply a filter on your form. Because if you have 95 records when you open
the form, Count([ClientName]) would show 95, but then if you applied a filter
on your form that limited what is shown to 14 records, then
Count([ClientName]) would only show 14.

Replace ClientName and/or ItemCost with the field names you want your
calculations based on.

 

[Fox]

Yes, actually currently the form is calculating this way. I have a form
called products that gets it's data from the products table, and the form has
a subform(suppliesUse) that gets it's data from the supplies used table.--

Because a product can have many supplies used when I placed the unbound
label on the form after relating the tables I am able to get the suppliesUsed
total x tax paid. However the Products form has a field called total cost
and the supplies used unbound total gets manually entered into this
field......
I've been trying to figure out to no avail how to update the products table
field with the suppliesused calulation field automatically. So it doesn't
have to be entered (or forgotten to be entered.) At the time of creating the
form I wanted to be able to see my total supply cost while I was entering a
new product. Now being anal I think it should update the products table
manually.... I maybe barking up the wrong tree and it might be impossible.
when I create a query with the total so that I can group the supplies by the
productname then I no longer can add new records to the form. If I create a
query that does the totals and add the query to the properties of the
suppliesused form then it doesn't group the supplies used and there is a
seperate total on each record....
Is this even possible??
Thanks for your response, I've searched and searched for the answer but
either it is not possible or I don't know what I'm asking...<S>

Foxy

 

[Fox]

Okay, I've decided I really don't know if there is a way to do this or not
but instead I just reworked the reports and forms so that I didn't need to do
this. Thanks for your help.

 

[Doctor]

If you want to, I can help you do this. But it would have to happen in code.
Let me know, and I will get back with you.

 

[Fox]

Thanks Doctor! I've made all the changes to avoid needing this and
really...it was redundant to do this. Sometimes I think my head is up my
$#$%^%$#$
I decided that I didn't need to store the info in two different places....
However, I seen this /similar question posted that hasn't been answered. So
if you don't mind posting how to accomplish this task for other users like me
that would be fantastic...heck I might need the answer later on...

Thanks for being so supportive of us newbees!!!