Multiply a Rates Query with a Volumes Query - sounds simple!

[Guest]

Please can someone help me with this? I have one query resulting in a dataset
of rates and one with a dataset of volumes. Both are crosstabs, with weeks 1
to 52 going across.

Each MaterialNo has only one recordset on the rates query but can have
multiple recordsets in the volumes table. I need to get a third table / query
which multiplies, for each MaterialNo, the rates by the volumes.

Sounds simple but I can't do it. Any ideas?

TIA
James

 

[Guest]

Post your crosstab SQL statements and some sample data.

 

[Guest]

Hi Karl

Sorry for the delay and thanks for replying. Ok - I thought I'd step one
step back and let you see the underlying tables - Rate and Volume.

Rates Table:

MatNo WkNo AcWkNo FinYr Rate
AA06 27 6 2006 0.72
AA06 27 6 2006 0.72
AA06 27 6 2006 0.72
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68

Volume Table:

DelTo PropNo MatNo WkNo AcWkNo FinYr Quant
558473 100278 AA06 22 1 2006 0.25
575168 100725 AA06 22 1 2006 0.25
580980 100916 AA06 22 1 2006 1
638208 100679 AA06 22 1 2006 0.25
645911 100136 AA06 22 1 2006 0.25
812318 100138 AA06 22 1 2006 0.25
846978 100174 AA06 22 1 2006 0.25
78422 100917 AA06 21 52 2006 0.5
532210 100049 AA06 21 52 2006 0.25

The crosstabs I was referring to were created from the above two underlying
tables just to show the weeks going across. But this is probably not
necessary to do the multiplication. So, what I want is to multiply rate by
volume for each MatNo, WkNo combination, grouping the MatNo by PropNo in the
Volume table.

I'm still working on the SQL and will post what I have soon.

James
TIA

 

[Guest]

Hi again - I have been working on it. There was a logical error in my rates
table - namely I should have had only one unique rates line for each
combination of material and weekno. Unfortunately I had duplicates. So
correcting that (eliminating all the duplicates) I re-wrote my SQL (pasted
below) and I think I have the correct answer now.

SELECT tblVolume_Final.AdmiralPubNo, tblVolume_Final.MaterialNo,
Sum(tblVolume_Final.Quantity) AS SumOfQuantity, tblVolume_Final.ScotCoWkRef,
tblRates_Final.Rate,
Sum([tblRates_Final].[Rate]*[tblVolume_Final].[quantity]) AS Rebate
FROM tblRates_Final INNER JOIN tblVolume_Final ON
(tblRates_Final.AdmiralFinYr = tblVolume_Final.AdmiralFinYr) AND
(tblRates_Final.AdmiralWkRef = tblVolume_Final.AdmiralWkRef) AND
(tblRates_Final.ScotCoWkRef = tblVolume_Final.ScotCoWkRef) AND
(tblRates_Final.MaterialNo = tblVolume_Final.MaterialNo)
GROUP BY tblVolume_Final.AdmiralPubNo, tblVolume_Final.MaterialNo,
tblVolume_Final.ScotCoWkRef, tblRates_Final.Rate;

I think my main error was that I took this problem from an Excel environment
and continued thinking in Excel ways - that was the reason for the duplicate
rate lines.

Thanks again.
James

 

[Guest]

Volume_Rate --
SELECT Rates_1.MatNo, Rates_1.FinYr, Rates_1.WkNo, Sum(Volume_1.Quant) AS
SumOfQuant, Rates_1.Rate
FROM Rates_1 INNER JOIN Volume_1 ON (Rates_1.MatNo = Volume_1.MatNo) AND
(Rates_1.WkNo = Volume_1.WkNo) AND (Rates_1.FinYr = Volume_1.FinYr)
GROUP BY Rates_1.MatNo, Rates_1.FinYr, Rates_1.WkNo, Rates_1.Rate;


TRANSFORM Sum([SumOfQuant]*[Rate]) AS Expr1
SELECT Volume_Rate.MatNo, Volume_Rate.FinYr
FROM Volume_Rate
GROUP BY Volume_Rate.MatNo, Volume_Rate.FinYr
PIVOT Volume_Rate.WkNo IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33,
34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52);

 

[Guest]

Thanks Karl. It's useful having your solution as well. I'll give it a go when
I get a chance.
TFTH
James

Volume_Rate --
SELECT Rates_1.MatNo, Rates_1.FinYr, Rates_1.WkNo, Sum(Volume_1.Quant) AS
SumOfQuant, Rates_1.Rate
FROM Rates_1 INNER JOIN Volume_1 ON (Rates_1.MatNo = Volume_1.MatNo) AND
(Rates_1.WkNo = Volume_1.WkNo) AND (Rates_1.FinYr = Volume_1.FinYr)
GROUP BY Rates_1.MatNo, Rates_1.FinYr, Rates_1.WkNo, Rates_1.Rate;


TRANSFORM Sum([SumOfQuant]*[Rate]) AS Expr1
SELECT Volume_Rate.MatNo, Volume_Rate.FinYr
FROM Volume_Rate
GROUP BY Volume_Rate.MatNo, Volume_Rate.FinYr
PIVOT Volume_Rate.WkNo IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33,
34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52);

Hi Karl

Sorry for the delay and thanks for replying. Ok - I thought I'd step one
step back and let you see the underlying tables - Rate and Volume.

Rates Table:

MatNo WkNo AcWkNo FinYr Rate
AA06 27 6 2006 0.72
AA06 27 6 2006 0.72
AA06 27 6 2006 0.72
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68
AA06 21 52 2006 13.68

Volume Table:

DelTo PropNo MatNo WkNo AcWkNo FinYr Quant
558473 100278 AA06 22 1 2006 0.25
575168 100725 AA06 22 1 2006 0.25
580980 100916 AA06 22 1 2006 1
638208 100679 AA06 22 1 2006 0.25
645911 100136 AA06 22 1 2006 0.25
812318 100138 AA06 22 1 2006 0.25
846978 100174 AA06 22 1 2006 0.25
78422 100917 AA06 21 52 2006 0.5
532210 100049 AA06 21 52 2006 0.25

The crosstabs I was referring to were created from the above two underlying
tables just to show the weeks going across. But this is probably not
necessary to do the multiplication. So, what I want is to multiply rate by
volume for each MatNo, WkNo combination, grouping the MatNo by PropNo in the
Volume table.

I'm still working on the SQL and will post what I have soon.

James
TIA