Sum usage

[Davecb]

I use a form 'Control' to save a record of Item usage to 'Control Table'.
Each record may show the Item used more than once. I use a series of combo
boxes (4 off) to get the correct Item number from the 'ItemTable'. Once the
Item number is displayed in the last combo box I would like a textbox to show
its total use. I use combo boxes to 'select' Items because some Items have
the same Item Number but are in different categories.

Any ideas welcome

Access 2003

 

[George]

There must be a question in there somewhere, but I can't quite coax it out.

Perhaps you want to count the number of times a particular "Item Number"
appears in the table?

If so, the calculation could be done with a DCount Function:
DCount("ItemNumber", "tblControlTable", "tblControlTable.ItemNumber = " &
Me.CboLastComboofFour

If I've answered the wrong question, please post back with a more explicit
version of it.

Thanks.

 

[Davecb]

Sorry for the confusion

I would like a query (or other method) to find the total use of an "Item"
(sum) and display it in a textbox on the form "Control"

An "Item" may have 10 records in the database but total use may be 30

To be able to select the correct "Item" I use two combo boxes "Category" and
"Item Number" on the form "Control"

I can get the total use from a query using Group By "Category" and Group By
"Item Number" and Sum "Total Use" but I have to "Enter Parameter Values"
manually and then type the result into the textbox.

How can I get the "Total Use" textbox to display total number of times
"Item" used automatically when "Item Number" is selected on the form?

Hope this explains a little better

Thanks

 

[George]

"An "Item" may have 10 records in the database but total use may be 30"

What defines "total use"? Is that the name of a field in the table? Are you
summing the values in a field called "total use"?

 

[Davecb]

Sorry I'm not good at explaining this.
I'll try and give you an example

Test No. 1234. A "Type C" pressure transducer, serial number 121 is put in a
test rig and the test rig is pressurised to a high pressure in 5 separate
events. The transducer has to be calibrated after 500 events so I need to
know the running total.

The Database Record has a field for the "Test No."(1234), "Category"(Type C),
"Item No."(121), "Usage"(5) and "Total Use"(Sum of Usage)

How do I get the database to filter out (Type C) and (121), from the Form
and then
get the field "Total Use" to display the sum of the values in the field
"Usage"

Hope you can make some sense of this

Thanks

 

[John W. Vinson]

The Database Record has a field for the "Test No."(1234), "Category"(Type C),
"Item No."(121), "Usage"(5) and "Total Use"(Sum of Usage)

How do I get the database to filter out (Type C) and (121), from the Form
and then
get the field "Total Use" to display the sum of the values in the field
"Usage"

You could, with an Update query... BUT YOU SHOULDN'T.

Storing the "Total Use" in every one of multiple records is redundant; worse,
storing it in *any* record in *any* table risks data corruption. When you add
a new Usage record to the table, every single record for that item will now be
WRONG and will need to be recomputed!

Just store the usage for each individual record, and use a Totals query,
grouping by Item (or by Item and Category, if that's more appropriate), and
summing Usage to dynamically calculate the total use as of the time the query
is run.

 

[a a r o n . k e m p f]

John;

the only thing that encourages corruption is _JET_.

Move to SQL Server and you'll have fast enough performance that you
won't need to care about pre-calculating summaries