Access-SQL: Inner Join a table with multiple copies of another table

Question

This and this made a great overview on joining three tables. But suppose we have a table Freights with fields From and To linked to Destinations table. In a Data Scheme I see Destinations_1 table, but when I try to use it in a query, it is not present. What to do?

Can you provide some sample data and expected results? – Gurwinder Singh May 13 '17 at 07:22 — Gurwinder Singh, May 13 '17 at 07:22
Shows your query, please... – Giorgio Brausi May 13 '17 at 09:13 — Giorgio Brausi, May 13 '17 at 09:13

score 2 · Accepted Answer · answered May 13 '17 at 14:14

Destinations_1 is the way Access internally aliases it. What you need to do is open the SQL window and manually alias it to something a little more distinct. There's no bigger pet peeve or worse coding offense than to let Access name everything for you. For instance, Field28 doesn't mean bubkus to the next guy coming in to take over your database, but txtStartDate will make it pretty simple to figure out what it holds. Similarly, Destinations_1 leaves the incoming coder pretty confused. Go into your SQL and fix this, so it looks more like:

SELECT * FROM Destination as PrimDestination
INNER JOIN Destination as SecDestination
ON PrimDestination.MyField = SecDestination.MyField

It will make much more sense to you once you see this layout, it will make more sense in the Design View, and it will make more sense to any future admin of the DB.

Access-SQL: Inner Join a table with multiple copies of another table

1 Answers1