Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: How to retreve a the correct data for a year

    Structure:
    i've got the tables
    BOM(ProductID, ComponentID, ValidFrom, Quantity)
    COMPONENTS(ComponentID,ComponentName,...)
    COMPPRICE(ComponentID, ValidFrom, Price, Units

    the relationship is

    [BOM]>--[COMPONENTS]--<[COMPPRICE]

    Task
    i need to select the correct BOM and CompPrice,
    ie if the bom has a validfrom Years the read 1990 , 1997 ,1999
    and if the compPrice has the validFrom years 1990 ,1997, 2002

    then to get the right data for 2001 i need to get the bom entry for 1999 and the compPrice for 1997

    Problem
    My brains gone on holiday and i can't figure out how to do it
    Last edited by m.timoney; 05-27-03 at 12:16.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    okay as no else has offered a solution and my brain is working again here the fix i've generated for anyone else with a simalar problem, of cause it someone else has a better method i would mind hearing it


    currentBOM
    SELECT Bom.ProductID, Bom.ComponantID, Max(Bom.ValidFrom) AS MaxOfValidFrom, Bom.Period
    FROM Bom
    GROUP BY Bom.ProductID, Bom.ComponantID, Bom.Period
    HAVING (((Max(Bom.ValidFrom))<=[forms]![YearSelector]![selyear]));

    currentPrices
    SELECT CompPrice.ComponantID, CompPrice.Period, CompPrice.ValidFrom
    FROM CompPrice
    GROUP BY CompPrice.ComponantID, CompPrice.Period, CompPrice.ValidFrom
    HAVING (((CompPrice.ValidFrom)<=[forms]![YearSelector]![selyear]));

    CalcBOM
    SELECT Products.ProductID, CompPrice.ComponantID, [Quantity]*[Price] AS Cost
    FROM Products INNER JOIN ((Componants INNER JOIN (CurrentBom INNER JOIN Bom ON (CurrentBom.ProductID = Bom.ProductID) AND (CurrentBom.ComponantID = Bom.ComponantID) AND (CurrentBom.MaxOfValidFrom = Bom.ValidFrom) AND (CurrentBom.Period = Bom.Period)) ON Componants.ComponentID = Bom.ComponantID) INNER JOIN (CurrentPrices INNER JOIN CompPrice ON (CurrentPrices.ComponantID = CompPrice.ComponantID) AND (CurrentPrices.ValidFrom = CompPrice.ValidFrom) AND (CurrentPrices.Period = CompPrice.Period)) ON Componants.ComponentID = CompPrice.ComponantID) ON Products.ProductID = Bom.ProductID;
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •