Recordset resulting from INNER JOIN-ing 3 tables not updateable

Question

I have 3 tables in Access 2007, one which a list of turbopumps and has a primary key [Numéro interne]. I'm trying to make a query that joins that table with the most recent operation of each type regarding a pump listed in the other two tables.

The recordset resulting from joining 2 tables is still updateable:

SELECT t.*, n.[Numéro attribué]
  FROM Turbopompes AS t 
  INNER JOIN (
    SELECT Turbopompe, [Date attribution], [Numéro attribué]
    FROM [Turbopompes Numéros]
    ORDER BY [Date attribution] DESC
  ) AS n ON t.[Numéro interne] = n.Turbopompe;

But no matter how I try to articulate it, joining 3 tables makes the result read-only:

SELECT t.*, n.[Numéro attribué], m.[Date maintenance], m.Détails
FROM (
  Turbopompes AS t 
  INNER JOIN (
    SELECT Turbopompe, [Date attribution], [Numéro attribué]
    FROM [Turbopompes Numéros]
    ORDER BY [Date attribution] DESC
  ) AS n ON t.[Numéro interne] = n.Turbopompe
) INNER JOIN ( 
  SELECT Turbopompe, [Date maintenance], Détails
  FROM [Turbopompes Maintenances]
  ORDER BY [Date maintenance] DESC
) AS m ON t.[Numéro interne] = m.Turbopompe;

Another query I've tried with the same result:

SELECT t.*, n.[Numéro attribué], m.[Date maintenance], m.Détails
FROM (
  Turbopompes AS t 
  INNER JOIN (
    SELECT Turbopompe, [Date attribution], [Numéro attribué]
    FROM [Turbopompes Numéros]
    ORDER BY [Date attribution] DESC
  ) AS n ON t.[Numéro interne] = n.Turbopompe
) INNER JOIN ( 
  Turbopompes AS t2
  INNER JOIN (
    SELECT Turbopompe, [Date maintenance], Détails
    FROM [Turbopompes Maintenances]
    ORDER BY [Date maintenance] DESC
  ) AS m ON t2.[Numéro interne] = m.Turbopompe
) ON t.[Numéro interne] = t2.[Numéro interne];

From:

http://www.fmsinc.com/Microsoftaccess/query/non-updateable/index.html

There are many reasons why your data may not be updateable. :

Queries with multi-table joins that aren't on key fields

So the inner join between the Turbopompes and [Turbopompes Numéros] doesn't preserve the primary key? Is there any way around that?

PhilS PhilS · Accepted Answer · 2015-04-13T15:49:59

Access is somewhat peculiar regarding the nesting of the join expressions. The field "Turbopompe" has to be the the primary key in table N as well as table M. That provided, you should get the desired, updateable result with this query:

SELECT t.*, n.[Numéro attribué], m.[Date maintenance], m.[Détails]
FROM ( Turbopompes AS t 
INNER JOIN [Turbopompes Numéros] AS n 
  ON t.[Numéro interne] = n.Turbopompe )
INNER JOIN [Turbopompes Maintenances]   AS m 
  ON t.[Numéro interne] = m.Turbopompe
ORDER BY n.[Date attribution] DESC, 
     m.[Date maintenance];

But if tables N and M contain multiple records for each [Numéro interne], you will need queries that get the most recent record for each "Turbopompe" first and then join them as derived tables to find the records you are looking for. This could look like this:

SELECT t.*, n.[Numéro attribué], m.[Date maintenance], m.Détails
FROM (
  Turbopompes AS t 
INNER JOIN (
  SELECT n1.Turbopompe, n1.[Date attribution], n1.[Numéro attribué]
  FROM [Turbopompes Numéros] n1
  INNER JOIN (SELECT Turbopompe, MAX([Date attribution]) AS MaxDateAttribution
            FROM [Turbopompes Numéros] 
            GROUP BY Turbopompe) n_max
    ON n1.[Date attribution] = n_max.MaxDateAttribution
    AND n1.Turbopompe = n_max.Turbopompe
  ) AS n ON t.[Numéro interne] = n.Turbopompe
) INNER JOIN ( 
  SELECT m1.Turbopompe, m1.[Date maintenance], m1.[Détails]
  FROM [Turbopompes Maintenances] m1
  INNER JOIN (SELECT Turbopompe, MAX([Date maintenance]) AS MaxDateMaintenance
                FROM [Turbopompes Maintenances]
                GROUP BY Turbopompe ) m_max
    ON m1.Turbopompe = m_max.Turbopompe
    AND m1.[Date maintenance] = m_max.MaxDateMaintenance
) AS m ON t.[Numéro interne] = m.Turbopompe;

However, as is it mandatory to aggegrate the records by [Date maintenance]/[Date attribution] to find the most recent record in each table, it is not possible to write any query that returns the correct result as an updateable recordset.

Recordset resulting from INNER JOIN-ing 3 tables not updateable

1 Answers