CODEWITHSUNDEEP
sqlms-accesssyntax
michaelnoze08
michaelnoze08

Reputation: 49

Join multiple query to a table with same column names

I have a table "tblEquipment" containing:

Equipment
1000
1001
1002
1003
402
413
420
421
428
439
446
483
3000
3001
3002
3005

a query "qry1"

Equipment   T1        T2           T3          T4
1000    2019-12-27                         2019-12-13
1001               2019-12-13       
1002                           2019-12-14   
1003    2019-12-25                         2019-12-06

a second query "qry2"

Equipment     T1            T2           T3           T4
402                     2019-06-29      
413       2019-06-15    2019-12-12                2019-12-12    
420       2019-06-15    2019-12-12   2019-12-12 
421                     2019-09-01      
428                                  2019-09-01     
439       2019-07-30
446       2019-07-30
483                                             2019-06-29

How can I create a third query to join all the related T1, T2, T3, T4 for each Equipment in tblEquipment?

The following code is a query I've been trying to make it work, however I keep getting a syntax error in FROM clase.

SELECT e.Equipment, vehicule.T1, vehicule.T2, vehicule.T3, vehicule.T4
FROM tblEquipment e
JOIN qr1 vehicule
ON e.Equipment = vehicule.Equipment
UNION ALL
SELECT e.Equipment, cart.T1, cart.T2, cart.T3, cart.T4
FROM tblEquipment e
JOIN qr2 cart
ON e.Equipment = cart.Equipment;

Upvotes: 2

Views: 41

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

MS Access does not automatically recognise the join statement as an inner join, you must explicitly state inner join:

select e.equipment, vehicule.t1, vehicule.t2, vehicule.t3, vehicule.t4
from tblequipment e inner join qr1 vehicule on e.equipment = vehicule.equipment
union all
select e.equipment, cart.t1, cart.t2, cart.t3, cart.t4
from tblequipment e inner join qr2 cart on e.equipment = cart.equipment;

You could alternatively write this as:

select e.equipment, u.t1, u.t2, u.t3, u.t4
from
    tblequipment e inner join
    (
        select v.equipment, v.t1, v.t2, v.t3, v.t4 from qr1 v
        union all
        select c.equipment, c.t1, c.t2, c.t3, c.t4 from qr2 c
    ) u
    on e.equipment = u.equipment

Upvotes: 1

Sotark
Sotark

Reputation: 196

If I am reading this correctly, it would appear that in your union section on the second FROM statement, even though you are referrincing tblEquipment a second time you must use a different table reference name, so you would want it to be

UNION ALL
select f.equipment, cart.t1, cart.t2, cart.t3, cart.t4
from tblequipment f
join qr2 cart
on f.equipment = cart.equipment;

Upvotes: 0

Related Questions