How to calculate values from 2 tables

[Guest]

I store production values in 2 tables:
A – Production Value
B – Goal Coefficient

Both tables have 5 primary key fields that are used in to identify
corresponding values from table B (the relationship is indeterminate to
one(B)-to-many(A).

The values in table A are divided into 52 weekly periods (1-52). The values
in table B are used in multiplying values in table A and adjusted
periodically. For example:
P1 = 16%
P17 = 9.3%
P22 = 11%
P48 = 12.7%

So if production in table A showed:
P15 - 29,350
P16 – 30,000
P17 – 22,500
P18 – 21,800

I need to multiply these value as follows:
P15 - 29,350 * 16%
P16 – 30,000 * 16%
P17 – 22,500 * 9.3%
P18 – 21,800 * 9.3%
and so on.

These values are valid for the duration (e.g. P17 will go from 17 to 21, P48
from 48 till next change) and the latest value does not replace all previous
values. Not all production codes have multiple values (about 40% do) and
have only one value for the whole time.

I need to build a query that will take this into account and will show the
coefficients according to period they are valid for.

Another option is to add a field to Table A and update the required values
from Table B to this field.

Any idea how to accomplish that?

Any suggestion is greatly appreciated.

 

[Edward G]

Personally, I don't see any reason to have table B, just build a query
pulling values from table A
with calculated fields such as:
Expr: IIf([ProdID]>="p15" And [ProdID]<"p17",[Qty]*.16,0)

Hope that helps.

Ed G

 

[Guest]

Thanks for the suggestion.

First I need the values from Table B in Table A. I have been working on a
look up function, but so far have not been able to make it work. Get lot of
unexpected messages such as "values do not exist" when they do, etc. This
function has 6 conditional statements (none optional):

Function GetManQuota(strProdID As String, intProdNo As Integer, _
strLocation As String, intDept As Integer, intYear As Integer, _
intPeriod As Integer) As Double

GetManQuota = DLookup("[GrowthPctQta]", "Table B", _
"[PRODUCT_ID]= " & strProdID & " And [PRODUCT_NO]= " & intProdNo & _
" And [PLANT_LOC]= " & strLocation & " And [PLANT_DEPT]= " & intDept & _
" And [PROD_YEAR]= " & intYear & " And [PROD_PERIOD]>= " & intPP)

End Function

I think the function is way to go. If you have any suggestions please feel
free to post it here.

Thank you.

Personally, I don't see any reason to have table B, just build a query
pulling values from table A
with calculated fields such as:
Expr: IIf([ProdID]>="p15" And [ProdID]<"p17",[Qty]*.16,0)

Hope that helps.

Ed G

I store production values in 2 tables:
A - Production Value
B - Goal Coefficient

Both tables have 5 primary key fields that are used in to identify
corresponding values from table B (the relationship is indeterminate to
one(B)-to-many(A).

The values in table A are divided into 52 weekly periods (1-52). The values
in table B are used in multiplying values in table A and adjusted
periodically. For example:
P1 = 16%
P17 = 9.3%
P22 = 11%
P48 = 12.7%

So if production in table A showed:
P15 - 29,350
P16 - 30,000
P17 - 22,500
P18 - 21,800

I need to multiply these value as follows:
P15 - 29,350 * 16%
P16 - 30,000 * 16%
P17 - 22,500 * 9.3%
P18 - 21,800 * 9.3%
and so on.

These values are valid for the duration (e.g. P17 will go from 17 to 21, P48
from 48 till next change) and the latest value does not replace all previous
values. Not all production codes have multiple values (about 40% do) and
have only one value for the whole time.

I need to build a query that will take this into account and will show the
coefficients according to period they are valid for.

Another option is to add a field to Table A and update the required values
from Table B to this field.

Any idea how to accomplish that?

Any suggestion is greatly appreciated.