Table Calculation

[mis]

To Forum,

I have created a table called categories and one of the fields is called Quantity, this field is supose to callculate all the products they are in the database under the relevant category. The products are stored in a product table and the quantity for each item is listed in the quantity field.

Is there a way of calculating all products with a certain category and placing this result in the quantity field of the category table?

Thanks

 

[Mile-O]

If you can calculate a value then you shouldn't store it.

 

[Mile-O]

Is there a way of calculating all products with a certain category

Yes. Use a DCount domain aggregate function, for example.

placing this result in the quantity field of the category table?

That violates Third Normal Form (3NF) and shouldn' be stored in a table as you can calculate it at any time.

 

[mis]

Thanks

So there is no need for this feild as it requirs a calculation which can be calculated when needed correct?

Still not sure how i would achieve the calculation?

 

[Mile-O]

Yes, that's right. An example could be that you would have the Quantity in a table and then somebody deletes a record - that quantity is now wrong. If you calculate the Quantity each time then you are always guaranteed a correct total.

To do it, here's a generic DCount - you can get more examples in the Help files.

=DCount("MyField", "MyTable", "OtherField = " & Me.MyTextBox)

 

[mis]

Thanks

Thanks for your help.