Query Stumper (V2000)

Topic

New Reply

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.

Reply | Quote

Replies

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 SELECT EmployeeID FROM qrySelectEmployeeID by the SQL of qrySelectEmployeeID, but that makes it harder to debug and maintain.

Reply | Quote

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

Reply | Quote