Doubt to use SUMPRODUCT with divide option..

[ldiaz]

I want to sum: the week 1, values that start with PLT, then the three rights
digits between 100 and the value multiply by the value of week rows,
something like this..
(120/100*1000)+(150/100*1300)+(200/100*1600) plus...
(120/100*1100)+(150/100*1450)+(200/100*1200)

following Week1 and PLT* datas.

PLT-120 PLT-150 PLT-200 ALT-200
Week1 1000 1300 1600 x
Week1 1100 1450 1200 x
Week2 10 20 60 x


Thanks in advanced.
LD

 

[Tom Wickerath]

Hi Lorenzo,

You can use a grouped query, which uses a Union query as it's source of
data, to solve this problem. Of course, you likely know that with proper
database design, you would not have to do these types of gymnastics...

First the Union query: Name it "quniPLTData":

SELECT WeekNo,[PLT-120] AS [PLTValue], 120 AS [PLTMultiplier]
FROM tblData
WHERE [PLT-120] Is Not Null
UNION
SELECT WeekNo,[PLT-150] AS [PLTValue], 150 AS [PLTMultiplier]
FROM tblData
WHERE [PLT-150] Is Not Null
UNION
SELECT WeekNo,[PLT-200] AS [PLTValue], 200 AS [PLTMultiplier]
FROM tblData
WHERE [PLT-200] Is Not Null
ORDER BY WeekNo;


Now, create a new query that uses this Union query as it's source of data.
The SQL statement for this new query is this:

SELECT quniPLTData.WeekNo,
Sum(([PLTMultiplier]/100)*[PLTValue]) AS PLTSum
FROM quniPLTData
GROUP BY quniPLTData.WeekNo
ORDER BY quniPLTData.WeekNo;


Note: I named the field corresponding to the Week# data as "WeekNo", and
gave the table a name of "tblData".


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
_______________________________________________


I want to sum: the week 1, values that start with PLT, then the three rights
digits between 100 and the value multiply by the value of week rows,
something like this..
(120/100*1000)+(150/100*1300)+(200/100*1600) plus...
(120/100*1100)+(150/100*1450)+(200/100*1200)

following Week1 and PLT* datas.

PLT-120 PLT-150 PLT-200 ALT-200
Week1 1000 1300 1600 x
Week1 1100 1450 1200 x
Week2 10 20 60 x


Thanks in advanced.
LD