2

I have a list of suppliers who supply a list of countries. For example:

Supplier A supplies to:

  1. USA
  2. France
  3. China

and another list of suppliers with their suppliers' factories For example:

Supplier A manufactures from:

  1. UK
  2. Germany

How can I build the list (in rows) of all possible combinations of (suppliers, countries, factories)? I have more than 10.000 suppliers. For example, for Supplier A:

  1. USA-UK,
  2. USA-Germany
  3. France-UK
  4. France-Germany
  5. China-UK
  6. China-Germany

Could someone please help me with this? I have been trying to work on it for weeks using Excel and then Access.

0
6

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you:

Sample tables and data

CREATE TABLE SupplierCountry
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierCountry
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'USA'),
    ('Supplier A', 'France'),
    ('Supplier A', 'China');

SupplierCountry

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ USA         ║
║ Supplier A   ║ France      ║
║ Supplier A   ║ China       ║
╚══════════════╩═════════════╝

SupplierFactory

CREATE TABLE SupplierFactory
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierFactory
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'UK'),
    ('Supplier A', 'Germany');

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ UK          ║
║ Supplier A   ║ Germany     ║
╚══════════════╩═════════════╝

Query:

SELECT
    SC.SupplierName AS Supplier,
    SC.CountryName AS SupplyCountry,
    SF.CountryName AS FactoryCountry
FROM SupplierCountry AS SC
JOIN SupplierFactory AS SF
    ON SF.SupplierName = SC.SupplierName;

Output:

╔════════════╦═══════════════╦════════════════╗
║  Supplier  ║ SupplyCountry ║ FactoryCountry ║
╠════════════╬═══════════════╬════════════════╣
║ Supplier A ║ USA           ║ UK             ║
║ Supplier A ║ France        ║ UK             ║
║ Supplier A ║ China         ║ UK             ║
║ Supplier A ║ USA           ║ Germany        ║
║ Supplier A ║ France        ║ Germany        ║
║ Supplier A ║ China         ║ Germany        ║
╚════════════╩═══════════════╩════════════════╝
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.