none
Append Fields to Table RRS feed

  • Question

  • I'm sure this is possible with an append query but I can't seem to make it work.  I have two tables - 'Customers' and 'Contracts'.  A customer can have multiple contracts, and I want to append the date and status of the last contract to the customers table.

    I've have tried using an Append Query with the 'MAX' function for the contract date but I keep coming across violation errors and can't seem to get it correct.  Can someone provide some guidance because I've exhausted myself trying to work this out

    Thursday, October 20, 2016 8:02 AM

Answers


  • What I'm trying to do is find the Customers where the last contract was more than 12 months ago.  These customers (and related contracts) will then be archived.

    That's much simpler.  The query to return those customers would be along these lines:

    SELECT *
    FROM Customers
    WHERE NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contracts.CustomerID = Customers.CustomerID
          AND ContractDate >= DATEADD("yyyy",-1,DATE());

    To 'archive' these customer's I'd suggest you simply add a Boolean (Yes/No) column named Archived to the Customers table, and change the above to an UPDATE query:

    UPDATE Customers
    SET Archived = TRUE
    WHERE NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contracts.CustomerID = Customers.CustomerID
          AND ContractDate >= DATEADD("yyyy",-1,DATE());

    If you want to simultaneously archive and un-archive customers (the latter where previously archived customers have entered into a contract within the last year) you could set the value of the Archived column in all rows like this:

    UPDATE Customers
    SET Archived = NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contacts.CustomerID = Customers.CustomerID
          AND ContractDate >=DATEADD("yyyy",-1,DATE());

    You can then restrict any form's or reports to non-archived customers by means of queries which are restricted to:

        WHERE NOT Archived

    There is no need to restrict the Contracts table similarly as no rows will be returned from this table by any query in which it is INNER JOINed to the Customers table and the query is restricted as above.


    Ken Sheridan, Stafford, England

    • Marked as answer by goodhealthit Friday, October 21, 2016 12:10 AM
    Thursday, October 20, 2016 11:14 PM

All replies

  • Since you can compute the date and status of the latest contract at any moment in a query, there is no need to store this information in the table. It would be (a) redundant, and (b) not necessarily up-to-date.

    By the way, an append query is used to add records to a table, not to add fields to a table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 20, 2016 2:02 PM
  • As Hans says, the latest contract data should be returned in a query, not as columns in the Customers table.  The  following query is an example, using Northwind.  In this orders are analogous to your contracts:

    SELECT Customers.Company, Customers.[First Name], Customers.[Last Name],
    LatestOrders.[Order Date], LatestOrders.[Status Name]
    FROM Customers LEFT JOIN
        (SELECT O1.[Customer ID], O1.[Order Date], [Orders Status].[Status Name]
         FROM Orders AS O1
         INNER JOIN [Orders Status]
         ON O1.[Status ID] = [Orders Status].[Status ID]
         WHERE [Order Date] =
             (SELECT MAX([Order Date])
              FROM Orders AS O2
              WHERE O2.[Customer ID] = [O1.Customer ID])) AS LatestOrders
    ON Customers.ID = LatestOrders.[Customer ID];

    The LEFT OUTER JOIN between Customers and the subquery is used here because in Northwind not all customers have made orders.

    Ken Sheridan, Stafford, England

    Thursday, October 20, 2016 3:36 PM
  • Hi Hans

    Yes, I actually meant it was an 'update' query not an 'append'.  The fields don't need to be kept up to date, only updated when the query is run.  I'll have a look at the code that Ken provided and see if that can provide what I need.

    Thursday, October 20, 2016 9:40 PM
  • Hi Ken

    Thanks for that example.  What I'm trying to do is find the Customers where the last contract was more than 12 months ago.  These customers (and related contracts) will then be archived.  I'm not sure how to use this query to find the customers and then archive them.

    Thursday, October 20, 2016 10:14 PM

  • What I'm trying to do is find the Customers where the last contract was more than 12 months ago.  These customers (and related contracts) will then be archived.

    That's much simpler.  The query to return those customers would be along these lines:

    SELECT *
    FROM Customers
    WHERE NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contracts.CustomerID = Customers.CustomerID
          AND ContractDate >= DATEADD("yyyy",-1,DATE());

    To 'archive' these customer's I'd suggest you simply add a Boolean (Yes/No) column named Archived to the Customers table, and change the above to an UPDATE query:

    UPDATE Customers
    SET Archived = TRUE
    WHERE NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contracts.CustomerID = Customers.CustomerID
          AND ContractDate >= DATEADD("yyyy",-1,DATE());

    If you want to simultaneously archive and un-archive customers (the latter where previously archived customers have entered into a contract within the last year) you could set the value of the Archived column in all rows like this:

    UPDATE Customers
    SET Archived = NOT EXISTS
         (SELECT *
          FROM Contracts
          WHERE Contacts.CustomerID = Customers.CustomerID
          AND ContractDate >=DATEADD("yyyy",-1,DATE());

    You can then restrict any form's or reports to non-archived customers by means of queries which are restricted to:

        WHERE NOT Archived

    There is no need to restrict the Contracts table similarly as no rows will be returned from this table by any query in which it is INNER JOINed to the Customers table and the query is restricted as above.


    Ken Sheridan, Stafford, England

    • Marked as answer by goodhealthit Friday, October 21, 2016 12:10 AM
    Thursday, October 20, 2016 11:14 PM
  • Thanks Ken - I actually worked that out by going back to basics and looking at what the client actually wants to achieve and starting from scratch with your first post!  My query matches pretty much what you've got above.

    Thanks for your help!

    Mark

    Friday, October 21, 2016 12:09 AM