none
Updateable Form with query that joins two tables, with unique records. RRS feed

  • Question

  • I am using Ms Access 2010.  I have a form that has a subform.  The master Form is Company Information, the subform is Licensing Information.  I want to display the Company information for a certain License Type, each Company can have multiple License and multiple License with the same type.  I am having issues with the query for the Master form.  When I join it with the License table to narrow it down to a certian License type, it will display multiple company records if they have more that one License.  If I try to add distinct to the query it makes the query not updateable I understand that is how it is designed.  I only need the Licese table to narrow it down to companies with a certian type.  I have tried using disinctrow but that doesn't work because it looks at the whole License record row, not just the "Type" field I have selected.  My query would look like 

    SELECT Co.IdCo, Co.Name, Lic.Type FROM Co INNER JOIN Lic ON Co.IdCo = Lic.IdCo where Lic.Type="Car"

    I there a way I can filter the information with the Lic Type that is located in the sub form from the master form and still have the form updateable.


    • Edited by Teamof1 Thursday, September 10, 2015 7:34 PM
    Thursday, September 10, 2015 7:33 PM

Answers

  • Try this:

    SELECT Co.IdCo, Co.Name, "Car" AS LicenseType
    FROM Co
    WHERE EXISTS
        (SELECT *
         FROM Lic
         WHERE Lic.IdCo = Co.IdCo
         AND Lic.Type="Car");

    Ken Sheridan, Stafford, England

    • Marked as answer by Teamof1 Thursday, September 10, 2015 9:01 PM
    Thursday, September 10, 2015 8:40 PM

All replies

  • Use a left join so it will display the company even if it does not have that type.

    SELECT Co.IdCo, Co.Name, Lic.Type FROM Co LEFT JOIN Lic ON Co.IdCo = Lic.IdCo where Lic.Type="Car"


    Build a little, test a little

    Thursday, September 10, 2015 8:37 PM
  • Try this:

    SELECT Co.IdCo, Co.Name, "Car" AS LicenseType
    FROM Co
    WHERE EXISTS
        (SELECT *
         FROM Lic
         WHERE Lic.IdCo = Co.IdCo
         AND Lic.Type="Car");

    Ken Sheridan, Stafford, England

    • Marked as answer by Teamof1 Thursday, September 10, 2015 9:01 PM
    Thursday, September 10, 2015 8:40 PM
  • Thanks! That type of select worked and left the query as updateable.
    Thursday, September 10, 2015 9:02 PM