Update query / Operation must use an updateable query error messag

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

I am trying to perform an update query using 2 tables. The first table is a
billing file with many records per employee id [EmployeeID] and there is also
an employee name field [EmployeeName]. However, some of the data in this
employee name field is missing. The second table shows the employee name for
each employee id; the fields are [Name] and [EmployeeID]. I am trying to
update the employee name field in the first file using the second file, and
using the driver id as the link. This is what I am using but I am getting
the above error message:

UPDATE [Employee List] INNER JOIN [Billing file] ON [Employee
List].EmployeeID = [Billing file].EmployeeID SET [Billing file].EmployeeName
= [Name]

Thank you in advance.
 
Try

UPDATE [Employee List] INNER JOIN [Billing file]
ON [Employee List].EmployeeID = [Billing file].EmployeeID
SET [Billing file].EmployeeName = [Employee List].[Name]

You might want to add a where Clause
WHERE [Employee List].[Name] is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John, thank you for responding but unfortunately I am getting the same error
message both with and without the where Clause

John Spencer said:
Try

UPDATE [Employee List] INNER JOIN [Billing file]
ON [Employee List].EmployeeID = [Billing file].EmployeeID
SET [Billing file].EmployeeName = [Employee List].[Name]

You might want to add a where Clause
WHERE [Employee List].[Name] is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to perform an update query using 2 tables. The first table is a
billing file with many records per employee id [EmployeeID] and there is also
an employee name field [EmployeeName]. However, some of the data in this
employee name field is missing. The second table shows the employee name for
each employee id; the fields are [Name] and [EmployeeID]. I am trying to
update the employee name field in the first file using the second file, and
using the driver id as the link. This is what I am using but I am getting
the above error message:

UPDATE [Employee List] INNER JOIN [Billing file] ON [Employee
List].EmployeeID = [Billing file].EmployeeID SET [Billing file].EmployeeName
= [Name]

Thank you in advance.
Click to expand...
Click to expand...
 
That should work as long as the two tables are Access tables. Usually
you get that error message when you are trying to use an aggregate query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, thank you for responding but unfortunately I am getting the same error
message both with and without the where Clause

John Spencer said:
Try

UPDATE [Employee List] INNER JOIN [Billing file]
ON [Employee List].EmployeeID = [Billing file].EmployeeID
SET [Billing file].EmployeeName = [Employee List].[Name]

You might want to add a where Clause
WHERE [Employee List].[Name] is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to perform an update query using 2 tables. The first table is a
billing file with many records per employee id [EmployeeID] and there is also
an employee name field [EmployeeName]. However, some of the data in this
employee name field is missing. The second table shows the employee name for
each employee id; the fields are [Name] and [EmployeeID]. I am trying to
update the employee name field in the first file using the second file, and
using the driver id as the link. This is what I am using but I am getting
the above error message:

UPDATE [Employee List] INNER JOIN [Billing file] ON [Employee
List].EmployeeID = [Billing file].EmployeeID SET [Billing file].EmployeeName
= [Name]

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
John, thank you again. I realized the problem. One of my 'tables' was
actually a query. I didn't think that would make a difference, but when I
converted it to an actual table, it worked fine.

John Spencer said:
That should work as long as the two tables are Access tables. Usually
you get that error message when you are trying to use an aggregate query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, thank you for responding but unfortunately I am getting the same error
message both with and without the where Clause

John Spencer said:
Try

UPDATE [Employee List] INNER JOIN [Billing file]
ON [Employee List].EmployeeID = [Billing file].EmployeeID
SET [Billing file].EmployeeName = [Employee List].[Name]

You might want to add a where Clause
WHERE [Employee List].[Name] is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Graeme wrote:
I am trying to perform an update query using 2 tables. The first table is a
billing file with many records per employee id [EmployeeID] and there is also
an employee name field [EmployeeName]. However, some of the data in this
employee name field is missing. The second table shows the employee name for
each employee id; the fields are [Name] and [EmployeeID]. I am trying to
update the employee name field in the first file using the second file, and
using the driver id as the link. This is what I am using but I am getting
the above error message:

UPDATE [Employee List] INNER JOIN [Billing file] ON [Employee
List].EmployeeID = [Billing file].EmployeeID SET [Billing file].EmployeeName
= [Name]

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top