Search code examples
sqlms-access

Access Query - Relationship query with 1 to 3 relationship

I have a Microsoft access query I'm trying to set up but have hit a snag. I'd describe this as a select query that has a relationship of 1:3.

I have two tables (A & B), table B has one field (a new part number) that I need to compare against three fields in table A (old/new part numbers). I want to do a select query so I can export as a CSV. In this case I need to match 2ABCD so I get a row with the updated price of $5.

The basic format I'm looking at is this.

From table B, I want to check if UPC4 matches UPC1, UPC2 or UPC3 in table A - matching for the value "2ABCD" in this example. Ideally I want to do this in one query. Thoughts?

TABLE A

UPC1 UPC2 UPC3 Price
ABCD 1ABCD 2ABCD $4

TABLE B

UPC4 Price
2ABCD $5

END RESULT SELECT QUERY

UPC1 UPC2 UPC3 UPC4 Price
ABCD 1ABCD 2ABCD 2ABCD $5
Solution

  • Access supports joins https://learn.microsoft.com/de-de/office/vba/access/concepts/structured-query-language/perform-joins-using-access-sql

    So you can combine both tables

    SELECT
    TableA.UPC1,TableA.UPC2,TableA.UPC3,
    TableB.UPC4,TableB.Price
FROM
    TableA INNER JOIN TableB ON TableA.UPC1 = TableB.UPC4
OR TableA.UPC2 = TableB.UPC4
OR TableA.UPC3 = TableB.UPC4