Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by access user, Jul 28, 2006.

  1. access user

    access user
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Festivals London Celebrates Colourful Sikh Festival Vaisakhi History of Sikhism Apr 13, 2015
    World Emirates issues clarification after Sikh pilot alleges discrimination Breaking News Nov 7, 2013
    World Sikh Pilot Alleges Emirates Denied Him a Job because He Wears a Turban Breaking News Nov 5, 2013
    USA Claremont Lincoln University Inaugurates Sikh Studies Center Breaking News Jan 11, 2013
    Sikh News White House celebrates Guru Nanak's birthday Breaking News Dec 11, 2012

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Post your crosstab SQL statements and some sample data.

    "access user" wrote:

    > 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
     
  4. access user

    access user
    Expand Collapse
    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


    "KARL DEWEY" wrote:

    > Post your crosstab SQL statements and some sample data.
    >
    > "access user" wrote:
    >
    > > 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
     
  5. access user

    access user
    Expand Collapse
    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

    "access user" wrote:

    > 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
    >
    >
    > "KARL DEWEY" wrote:
    >
    > > Post your crosstab SQL statements and some sample data.
    > >
    > > "access user" wrote:
    > >
    > > > 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
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    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);


    "access user" wrote:

    > 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
    >
    >
    > "KARL DEWEY" wrote:
    >
    > > Post your crosstab SQL statements and some sample data.
    > >
    > > "access user" wrote:
    > >
    > > > 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
     
  7. access user

    access user
    Expand Collapse
    Guest

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

    "KARL DEWEY" wrote:

    >
    > 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);
    >
    >
    > "access user" wrote:
    >
    > > 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
    > >
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Post your crosstab SQL statements and some sample data.
    > > >
    > > > "access user" wrote:
    > > >
    > > > > 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
     

Share This Page