Allocation of Calculation(VB)

  • Thread starter Thread starter James Beckz
  • Start date Start date
J

James Beckz

Hi

*********************************************************
Allocation of Calculation into their respective Field
*********************************************************
I doing a "Cost Project" and there is a scenario that
different products (Food, Drink, Cups) have its different
cost. Cost includes "Fixed Cost" which need to be spread
by the number of sales.

Assume that i can handle the some calculation issue. Now i
have TotalCost_food, TotalCost_Drink, TotalCost_Cup.
*(They are all variables to be throw to the fields)

This tblCost has field ProductID (Fd, Dr, Cu) and the
headache is i have no idea what to use to tie
TotalCost_Food into ProductID (Fd)
TotalCost_Drink into ProductID (Dr)
TotalCost_Cup into ProductID (Cu)

Pls advice. Thanks!

James Beckz
 
I'm not completely sure I understand your message, but
I'll give it a try.

It sounds like you have a list of items that all belong
to one of three categories, and each of these has a cost
which you want to total. But, you are wondering how to
tie the total back to the category.

I don't really understand what kind of list you are
totalling (purchases, sales, price list, etc), so I'm not
sure what the best way to deal with the cost would be,
but I'll give some general info on tying individual items
to a category.

For your detailed table (say TblItems), you could have
something like this:

TblItems
--------
ItemID, ItemDesc, ItemCost, ItemCategoryID

Then, you would also have a category table as follows:

TblCategories
-------------
CategoryID, CategoryDesc

You would then add both of these tables to a query and
join ItemCategoryID to CategoryID. Add the CategoryID,
CategoryDesc, and ItemCost fields to the query. Then,
click the totals button on the toolbar, which looks like
a greek sigma (assuming you are using Access query
builder). Under CategoryID and CategoryDesc,
choose "Group By" and under ItemCost choose "Sum". This
will give you the totals for each Category.

Post back if I misunderstood your question, or if the
above isn't clear.

-Ted Allen
 
In reading your original post again, I see that you want
to reference these totals in code and set the appropriate
total to a control on the form.

Assuming that the form is displaying a product, the
product record should have a reference to the category
that it belongs to. If you use the query in my previous
post, but use code to append a WHERE condition to set the
category equal to the current product category you can
use the sql to open a recordset which will have only the
appropriate category. Then, you could set the control
value equal to the cost total field in the recordset.

But, it would probably be much easier to use the DSum()
function. You could just enter the DSum() expression
directly in the control's control source. The DSum
expression would look something like this:

DSum("[FieldNameToBeSummed]", "TableName","[CategoryID]
= " & Me.Category)

Where the actual Field and Table names would be
substituted. [CategoryID] would be replaced with
whatever field name exists in the cost table to identify
the category, and Me.Category would be replaced with Me.
and the control name on the form identifying the current
items category.

HTH

-Ted Allen
 
Back
Top