Table 1

Table 2

I am trying to use the order numbers in table 1 to match in table 2. If wanted a exact match I use the code below. I have using like but it still doesn't read the order number row in table 2 if there are multiple order numbers.

I need to be able to use table 1 order number to match any of the table 2 order numbers as long as there's a partial match.

SELECT [Table 1].[Order number], [Table 2].[Order number]
FROM [Table 1] 
INNER JOIN [Table 2] ON [Table 1].[Order number] = [Table 2].[Order number]

CodePudding user response：

You could probably do something like what is found here: Where value in column containing comma delimited values But, might I suggest that you reconsider your table design? The design you presented is not normalized, which will create a level of difficult you are seeing here (and it will likely get worse). A better design for Table 2 is to make each record its own row. Like this:

Each record has it's own row, with a primary key that uniquely identifies the record. This will make querying a lot simpler. This is considered normalized: https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

CodePudding user response：

In your state there are four possible variations on matching:

1. The number is at the begining,
2. The number is in the middle,
3. The number is at the end.
4. Exactly same

To cover all of these situations you can simply create conditions and append them with "OR" statement.

In the final form your query will be look like this:

SELECT
    [Table 1].[Order number], 
    [Table 2].[Order number]
FROM 
    [Table 1]
    INNER JOIN [Table 2]
    ON  (
            [Table 1].[Order number]=[Table 2].[Order number] 
            OR [Table 2].[Order number] LIKE [Table 1].[Order number] & ",%"
            OR [Table 2].[Order number] LIKE "%," & [Table 1].[Order number] & ",%"
            OR [Table 2].[Order number] LIKE "%," & [Table 1].[Order number]
        )