Access SQL Inner join 3 tables without like field

Question

First off, I am pretty sure my answer is here: SQL Inner-join with 3 tables?

but after two weeks, I give up.

• Table AssemLines Fields ID, AssemID, ItemID, ItemQty, ServiceID, ServiceQty

• Table Items Fields ItemID, ItemName, ItemDesc

• Table Services Fields ServiceID, ServiceName, ServiceDesc

AssemLines sample data:

ID   -   AssemID   -   ItemID  -   ItemQty -  ServiceID   -  ServiceQty
1           1            12          102                        
2           1            62          15                              
3           1                                    3              45
4           2                                    6              90
5           2            23           5 

Desired Query result:

AssemID   -   ItemName     -   ItemQty     -   ServiceName    -   ServiceQty
1               2" tube         102                          
1               3" tube         15
1                                                 Weld               45
2                                                 Saw                90
2               1" tube          5

Any ideas?

Answer 1

It seems you just want to replace id's with proper names looked up in other tables. So the most basic join should work:

SELECT a.AssemID, i.ItemName, a.ItemQty, s.ServiceName, a.ServiceQty
FROM AssemLines a
LEFT JOIN Items i
  ON a.ItemID = i.ItemID
LEFT JOIN Services s
  ON a.ServiceID = s.ServiceID