jonplaca jonplaca - 1 month ago 17
SQL Question

MS Access - JOIN using LIKE and AND

OBJECTIVE

Develop a Sales Report that outputs a product catalog to showcase new/existing products. Report details include:


  • CUSTOMER ID

  • CUSTOMER NAME (specific to
    CUSTOMER ID
    )

  • ITEM ID

  • STYLE

  • INVENTORY COUNT

  • DEMAND FORECAST (specific to
    ITEM ID
    &
    CUSTOMER ID
    )

  • LAST PURCHASE ORDER DATE (specific to
    ITEM ID
    &
    CUSTOMER ID
    )






First, a bit of BACKGROUND

Initially, I developed reports that highlighted a Company's (
COMPANY ID
) historical purchase orders (max(
PURCHASE ORDER DATE
)) for a specific item (
ITEM ID
). However, this quickly failed since new products are also apart of the catalog (where there is no history of a
PURCHASE ORDER DATE
). To make matters worse, our company forecasts demand for items (
DEMAND FORECAST
), even if the product is completely new (why this is the case, I have no idea).

As a result,
CUSTOMER
and
ITEM ID
cannot be directly related (since, sometimes customers shown new products).


To make matters worse, a
CUSTOMER
may have multiple
CUSTOMER NAMES
(e.g "Home Depot", "HOME DEPOT ONLINE", etc.)





APPROACH


  1. Create a table of
    Customer IDs
    ,
    Customer Names
    (completed)

  2. Create a table of
    Item ID
    and their
    Inventory
    (completed)

  3. Create a table of max(
    Purchase orders
    ) (latest purchase order), including
    Customer IDs
    and
    Item IDs
    (completed)

  4. Create a table of
    Demand Forecasts
    , based on
    Customer ID
    and
    Item ID
    (completed)

  5. Create a middle table, "Active Catalog" that links
    Customer Name
    to
    Item ID
    depending on whether or not we want to showcase certain new/existing products (incomplete)

  6. Link the "Active Catalog" table to "Purchase Order Table" and "Forecast Table", based on
    ITEM ID
    and LIKE
    CUSTOMER NAME
    (since 1 customer can have multiple variations of a name).



NOTE: To reiterate, the reason why I have to Steps #3 and #4 is due to the fact that a company can have a purchase order, but no forecasts; or a forecast, but no previous purchase order for each item. As a result, step #5 is required to solidify the link between a
Company ID
and a
Item
and Step #6 is added complexity because our database is way screwed up...





ILLUSTRATION
Currently in MS Access




CODE

SELECT
[Active Catalog].[CUSTOMER NAME],
[Table - Item and Company Forecasted Sales].[COMPANY ID],
[Table - Item and Company Forecasted Sales].ITEM,
[Table - Item and Company Forecasted Sales].STYLE,
[Table - Item and Company Forecasted Sales].DESCRIPTION,
[Table - Item and Company Historic Sales].PRICE,
[Table - Item and Company Forecasted Sales].[ON-HAND QTY],
[Table - Item and Company Forecasted Sales].[FORECAST QTY],
[Table - Item and Company Historic Sales].[LATEST PO DATE]
INTO [Table - Summary]

FROM ([Active Catalog]
INNER JOIN [Table - Item and Company Forecasted Sales]
ON ([Active Catalog].STYLE = [Table - Item and Company Forecasted Sales].STYLE)
AND ([Active Catalog].[CUSTOMER NAME] LIKE '%' + [Table - Item and Company Forecasted Sales].[CUSTOMER NAME] + '%'))
INNER JOIN [Table - Item and Company Historic Sales]
ON ([Active Catalog].STYLE = [Table - Item and Company Historic Sales].STYLE)
AND ([Active Catalog].[CUSTOMER NAME] LIKE '%' + [Table - Item and Company Historic Sales].[CUSTOMER NAME] +'%')

GROUP BY
[Active Catalog].[CUSTOMER NAME],
[Table - Item and Company Forecasted Sales].[COMPANY ID],
[Table - Item and Company Forecasted Sales].ITEM,
[Table - Item and Company Forecasted Sales].STYLE,
[Table - Item and Company Forecasted Sales].DESCRIPTION,
[Table - Item and Company Historic Sales].PRICE,
[Table - Item and Company Forecasted Sales].[ON-HAND QTY],
[Table - Item and Company Forecasted Sales].[FORECAST QTY],
[Table - Item and Company Historic Sales].[LATEST PO DATE];





QUESTIONS


  1. I would like to join a table based on the
    ITEM ID
    AND the relative likeliness of the
    CUSTOMER NAME
    since, as stated, 1
    CUSTOMER ID
    has multiple but similar
    CUSTOMER NAMES
    (e.g HOMEDEPOT, HOMEDEPOT ONLINE). However, my code seems be incorrect. See:
    ... LIKE '%' + [Table - Item and Company Forecasted Sales].[CUSTOMER NAME] + '%'))
    in Code above.


Answer

Follow-up:

I created an "Active Product" table to display new/existing products.

Furthermore, I've created a seperate query that has MsgBox prompt, enabling users to search for LIKE terms by following this tutorial