none
Stuck with a data model relationship RRS feed

  • Question

  • I am attempting to create a data model and have an issue with the following data

    Inventory – this goes down to area level and details the quantity for a product

    Continent

    Country

    City

    Area

    Stock

    StockQuantity

    PKCountryProduct

    PKContinentArea

    Europe

    England

    London

    Camden

    A

    4

    EnglandA

    EuropeA

    Europe

    England

    Manchester

    Salford

    B

    7

    EnglandB

    EuropeB

    North America

    USA

    New York

    Little Italy

    C

    8

    USAC

    North AmericaC

    Area table – lists the continent, country, city and area.  A primary key has been created to join to Inventory

    Continent

    Country

    City

    Area

    PKContinentArea

    Europe

    England

    London

    Camden

    EuropeCamden

    Europe

    England

    Manchester

    Salford

    EuropeSalford

    North America

    USA

    New York

    Little Italy

    North America Little Italy

    Item – Lists the item at continent level.  The PKCountryProduct has been created to link to Inventory.

    Continent

    Product

    Name

    PKCountryProduct

    Europe

    A

    Aname

    EuropeA

    Europe

    B

    Bname

    EuropeB

    North America

    C

    Cname

    North AmericaC

    With the tables above I can cut the Inventory data by area and item.

    I want to link the minimum table but to get the minimum stock (this table is only at City level).  I cannot create a lookup in the item table because this is at continent level and does not include the City.  I have attempted to create a lookup from Inventory to the minimum table and brought in the minimum stock.  This does not work as there are some items in the minimum table that are not in the Inventory table and these need to be displayed.

    Country

    City

    Stock

    MinimumStock

    England

    London

    A

    3

    England

    Manchester

    B

    2

    USA

    New York

    C

    1

    Can anyone help how I can include this data in the model?

    Tuesday, February 12, 2019 9:04 PM

Answers