Access-Sql: Inner Join With Multiple Tables

Access-SQL: Inner Join with multiple tables

If you are writing a query against an Access database backend, you need to use the following join syntax:

select
  t1.c1
, t2.c2
, t3.c3
, t4.c4
from ((t1
inner join t2 on t1.something = t2.something)
inner join t3 on t2.something = t3.something)
inner join t4 on t3.something = t4.something

The table and column names aren't important here, but the placement of the parentheses is. Basically, you need to have n - 2 left parentheses after the from clause and one right parenthesis before the start of each new join clause except for the first, where n is the number of tables being joined together.

The reason is that Access's join syntax supports joining only two tables at a time, so if you need to join more than two you need to enclose the extra ones in parentheses.

MS Access Inner Join On 3 Tables with the same field_name

With Access you need to use parentheses when doing more than one join:

SELECT  * 
FROM    (Table1 
        INNER JOIN Table2 
            ON Table1.Master_Number = Table2.Master_Number)
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

This is essentiall spliting down you query so you have at most one join per section, i.e. Your First query is:

SELECT  *
FROM    Table1
        INNER JOIN Table2
            ON Table1.Master_Number = Table2.Master_Number;

Then you have:

SELECT  *
FROM    YourFirstQuery
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

This differs slightly from subqueries as you are still able to reference all fields from Table1 and Table2.

---

EDIT

To avoid duplicating columns you will need to explicitly list the columns you want (although you should be doing this anyway):

SELECT  Table1.Master_Number,
        Table1.Asset_Tag,
        Table1.Serial_Number,
        Table2.Last_Name,
        Table2.First_Name,
        Table4.Office_Number,
        Table4.Location,
        Table4.Department
FROM    (Table1 
        INNER JOIN Table2 
            ON Table1.Master_Number = Table2.Master_Number)
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

SQL Access: Multiple Inner Join between Two Tables

You can try this method (this is using subqueries, beware it may be somewhat slow if the table is big):

SELECT Production.[Production Reference ID],
(SELECT Reagents.Supplier FROM Reagents WHERE Reagents.[Reagent Reference ID] = Production.[C Reference ID] ) as CSupplier, 
(SELECT Reagents.Supplier FROM Reagents WHERE Reagents.[Reagent Reference ID] = Production.[P Reference ID] ) as PSupplier
FROM Production;

Edit: With some Testing, I found out how it works in JOIN too, and it's a lot faster then method 1:

SELECT C.Supplier as 'C Supplier', P.Supplier as 'P Supplier'
FROM ( Production
INNER JOIN Reagents AS C ON C.[Reagent Reference Number]=Production.[C Reference Number] )
INNER JOIN Reagents AS P ON P.[Reagent Reagent Reference Number]=Production.[P Reference Number]
WHERE Production.[Production Reference Number]=?

MS Access requires parentheses when using more then one JOIN in a query.

MS Access SQL : INSERT and INNER JOIN on multiple tables gives missing operator error

Try adding parentheses:

FROM (tempFixtureSquad INNER JOIN Players ON tempFixtureSquad.FirstName = Players.FirstName AND tempFixtureSquad.LastName = Players.LastName) INNER JOIN Teams ON tempFixtureSquad.Team = Teams.Team

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

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.

Join multiple tables and apply where on multiple tables and then group by in access sql

Your problem is due to your WHERE clauses. When you add a WHERE clause to a LEFT JOIN, that WHERE will fail whenever the row from the LEFT JOINed table has a NULL value, and remove it from the output (it effectively converts the LEFT JOIN into an INNER JOIN). You need to move the WHERE conditions into the JOIN conditions instead i.e.

SELECT
    LocationMaster.LocationName
  , SUM(MHReporting.DHE)                          AS TotalDHE
  , SUM(MHReporting.Contractors)                  AS TotalContractors
  , SUM(MHReporting.ADNOCContractors)             AS TotalADNOCContractors
  , SUM(NMReporting.NM)                           AS TotalNM
  , SUM(BSAReporting.BSA)                         AS TotalBSA
  , SUM(HSEGroupMeetingReporting.NoOfMeetings)    AS TotalNoOfMeetings
  , SUM(StopSafetyObservationCycleReporting.SSOC) AS TotalSSOC
  , COUNT(LTIReporting.LTI)                       AS TotalLTI
FROM
    (((((LocationMaster
    LEFT JOIN
        NMReporting
    ON
        (LocationMaster.ID = NMReporting.Location AND NMReporting.AttendanceDate = [DesiredDate]))
    LEFT JOIN
        LTIReporting
    ON
        (LocationMaster.ID = LTIReporting.Location AND LTIReporting.AttendanceDate = [DesiredDate]))
    LEFT JOIN
        HSEGroupMeetingReporting
    ON
        (LocationMaster.ID = HSEGroupMeetingReporting.Location AND HSEGroupMeetingReporting.AttendanceDate = [DesiredDate]))
    LEFT JOIN
        BSAReporting
    ON
        (LocationMaster.ID = BSAReporting.Location AND BSAReporting.AttendanceDate = [DesiredDate]))
    LEFT JOIN
        StopSafetyObservationCycleReporting
    ON
        (LocationMaster.ID = StopSafetyObservationCycleReporting.Location AND StopSafetyObservationCycleReporting.AttendanceDate = [DesiredDate]))
    LEFT JOIN
        MHReporting
    ON
        (LocationMaster.ID = MHReporting.Location AND MHReporting.AttendanceDate = [DesiredDate])
GROUP BY
    LocationMaster.LocationName

Note also that you are likely to end up with incorrect counts due to row duplication, so you may need to rewrite your query along these lines:

SELECT
    LocationMaster.LocationName
  , COALESCE(MHReporting.DHE, 0) AS DHE
  , ...
FROM
    LocationMaster
LEFT JOIN (SELECT Location
                , AttendanceDate
                , SUM(DHE) AS DHE
                , SUM(Contractors) AS Contractors
                , SUM(ADNOCContractors) AS ADNOCContractors
           FROM MHReporting
           WHERE MHReporting.AttendanceDate = [DesiredDate]
           GROUP BY Location, AttendanceDate) MHReporting ON LocationMaster.ID = MHReporting.Location
LEFT JOIN (SELECT ...

MS Access 2016, joining multiple tables with composite keys

Access apparently requires the JOINs to be ... I don't know what the word is ( mutually exclusive perhaps ):

SELECT
    t1.[model-number],
    t1.[serial-number],
    t1.[track],
    t1.[data],
    t2.[data],
    t3.[data]
FROM (
      [table1] t1
         LEFT JOIN [table2] t2
           ON (t2.[model-number] = t1.[model-number]) 
          AND (t2.[serial-number] = t1.[serial-number])
     )
     LEFT JOIN [table3] t3
       ON (t3.[model-number] = t2.[model-number]) 
      AND (t3.[serial-number] = t2.[serial-number])
WHERE t1.[track] = TRUE
ORDER BY t1.[model-number], t1.[serial-number] DESC;

Notice the extra parentheses around the join between t1 and t2? Seems necessary.

Note - The way I discovered this was to use the query design window joining the tables on their composite keys and changing the properties to LEFT (i.e. all rows from the left table and only matching rows from the right table) and then looking at the generated SQL.

Multiple INNER JOIN SQL ACCESS

Access requires parentheses in the FROM clause for queries which include more than one join. Try it this way ...

FROM
    ((tbl_employee
    INNER JOIN tbl_netpay
    ON tbl_employee.emp_id = tbl_netpay.emp_id)
    INNER JOIN tbl_gross
    ON tbl_employee.emp_id = tbl_gross.emp_ID)
    INNER JOIN tbl_tax
    ON tbl_employee.emp_id = tbl_tax.emp_ID;

If possible, use the Access query designer to set up your joins. The designer will add parentheses as required to keep the db engine happy.

---

Related Topics