Question:
I’m working with an Access database and have two tables:| ID_1 | Number | Some other data |
|---|---|---|
| 1 | 1 | Data |
| 2 | 2 | Data |
| 3 | 3 | Data |
| 4 | 4 | Data |
| 5 | 3 | Data |
| 6 | 1 | Data |
| 7 | 2 | Data |
| 8 | 3 | Data |
| 9 | 1 | Data |
| 10 | 1 | Data |
| 11 | 2 | Data |
| 12 | 3 | Data |
| 13 | 4 | Data |
| 14 | 1 | Data |
| 15 | 2 | Data |
| 16 | 3 | Data |
| 17 | 4 | Data |
| 18 | 3 | Data |
| 19 | 3 | Data |
| ID_2 | Number | Some other data |
|---|---|---|
| 1 | 3 | Data |
| 2 | 1 | Data |
| 3 | 2 | Data |
| 4 | 3 | Data |
| 5 | 2 | Data |
As you see, both tables have duplicate data. I need a query that would select all the records in the first table that match each of the records in the second, they are related by Number field. It’s also necessary that these records aren’t repeated (that is, that the query doesn’t repeat values when selecting). For the given example I should get this result:
| ID | ID_1 | Number | Some other data |
|---|---|---|---|
| 1 | 3 | 3 | Data |
| 2 | 5 | 3 | Data |
| 3 | 8 | 3 | Data |
| 4 | 12 | 3 | Data |
| 5 | 16 | 3 | Data |
| 6 | 18 | 3 | Data |
| 7 | 19 | 3 | Data |
| 8 | 1 | 1 | Data |
| 9 | 6 | 1 | Data |
| 10 | 9 | 1 | Data |
| 11 | 10 | 1 | Data |
| 12 | 14 | 1 | Data |
| 13 | 2 | 2 | Data |
| 14 | 7 | 2 | Data |
| 15 | 11 | 2 | Data |
| 16 | 15 | 2 | Data |
I was thinking that maybe I could use Join, but I still don’t know how; tried Where, but also didn’t find a use for it. Could you please help me with that?
Answer:
I don’t see where you’re generating your output ID field from – or where you’re picking your Data field from so here’s the best guess.
If you have better answer, please add a comment about this, thank you!