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 |
CodePudding user response：
Access supports joins https://docs.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