Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Stumper (V2000)

    I have a table of employees in a form and I want to be able to edit their information based on query information contained in other tables.
    For instance, I want to view an updateable recordset of all employees with sales in the northeast OR all employees with service agreements with our two biggest customers.
    The employees, sales and service information is contained in three different tables with employees as the one to the many side of the relationships.
    I only want to modify the employee information. A simple query returns multiple records because of the one-to-many relationship. A 'return unique values' set to yes produces a recordset that isn't upateable. And selecting 'Dynaset (inconsistent updates)' hasn't worked either.
    I know there is a logical way to do this but I've had no luck finding it.
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Stumper (V2000)

    I would do this with two queries:

    (1) Create a query that selects the unique Employee ID's of all employees that meet the criteria. This query is not updateable. Let's call it qrySelectEmployeeID
    (2) Create a query based on the Employees table (I'll call it tblEmployees here) that selects those employees whose Employee ID is in the first query. Its SQL is

    SELECT * FROM tblEmployees WHERE EmployeeID In (SELECT EmployeeID FROM qrySelectEmployeeID)

    This query is updateable; use it as record source of the form.

    Note: it is possible to do this in one query: replace <font face="Georgia">SELECT EmployeeID FROM qrySelectEmployeeID</font face=georgia> by the SQL of qrySelectEmployeeID, but that makes it harder to debug and maintain.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Stumper (V2000)

    Thanks a bunch!! I've never seen criteria used that way before. It worked great and I appreciate your help!
    Cheers,
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •