update table using a Query

[sunrise987]

Hello,

I have three of my database tables joined in a 'select query' and
displayed in a 'form'
I need to be able to update those three tables using the form.

my problem is the resulting 'select query' of joined tables is not
allowing update of record. I don't understand why is this happening.
(I am sure that a 'select query' do allow update of tables in other
cases!) Here is the code that I used:


SELECT
E.[Employment History Entry], E.[Employee ID] AS
[H_Employment_EmployeeID], E.[Effective Date], E.[End Date], E.[Job
Title], E.[Function], E.[Sub Function], E.[Service Group], E.[Grade],
E.[Service Line], E.[Location], E.[Counselor], E.[Additional Role],
B.EmployeeID AS [H_Marital Status_EmployeeID], B.Status, B.[Date of
Update],
Employee.EmployeeID AS Employee_EmployeeID, Employee.[First Name],
Employee.[Middle Name], Employee.[Last Name], Employee.Gender,
Employee.[Place of Birth], Employee.[Date of Birth], Employee.
[Employment Status], Employee.[Nationality 1], Employee.[Nationality
2], Employee.[Blood Type], Employee.[Joining Date as Per Offer
Letter], Employee.[Date of Contract], Employee.[Employee File Number],
Employee.[Beginning of Service at DTME], Employee.[Native Language],
Employee.[Written Skills], Employee.[Verbal Skills]


FROM (Employee LEFT JOIN [H_Employment] AS E
ON Employee.EmployeeID = E.[Employee ID])
LEFT JOIN [H_Marital Status] AS B
ON Employee.EmployeeID = B.EmployeeID

WHERE E.[Effective Date] =
(SELECT Max([Effective Date])
FROM H_Employment AS Tmp
WHERE Tmp.[Employee ID] = E.[Employee ID])

AND B.[Date of Update] =
(SELECT Max([Date of Update])
FROM [H_Marital Status] as Tmp
WHERE Tmp.EmployeeID = B.EmployeeID);



how can I make this query update corresponding tables? Please Help.

 

[Tom van Stiphout]

On Sat, 23 Aug 2008 09:15:14 -0700 (PDT), (e-mail address removed) wrote:

You probably have one-to-many relations between these tables, so
editing should be done in a Master/Detail form (aka form/subform).
Take a look at the Northwind sample application's Order form, with its
OrderDetail subform.

-Tom.
Microsoft Access MVP

 

[sunrise987]

On Sat, 23 Aug 2008 09:15:14 -0700 (PDT), (e-mail address removed) wrote:

You probably have one-to-many relations between these tables, so
editing should be done in a Master/Detail form (aka form/subform).
Take a look at the Northwind sample application's Order form, with its
OrderDetail subform.

-Tom.
Microsoft Access MVP

Hello,

I have three of my database tables joined in a 'select query' and
displayed in a 'form'
I need to be able to update those three tables using the form.

my problem is the resulting 'select query' of joined tables is not
allowing update of record. I don't understand why is this happening.
(I am sure that a 'select query' do allow update of tables in other
cases!) Here is the code that I used:

SELECT
E.[Employment History Entry], E.[Employee ID] AS
[H_Employment_EmployeeID], E.[Effective Date], E.[End Date], E.[Job
Title], E.[Function], E.[Sub Function], E.[Service Group], E.[Grade],
E.[Service Line], E.[Location], E.[Counselor], E.[Additional Role],
B.EmployeeID AS [H_Marital Status_EmployeeID], B.Status, B.[Date of
Update],
Employee.EmployeeID AS Employee_EmployeeID, Employee.[First Name],
Employee.[Middle Name], Employee.[Last Name], Employee.Gender,
Employee.[Place of Birth], Employee.[Date of Birth], Employee.
[Employment Status], Employee.[Nationality 1], Employee.[Nationality
2], Employee.[Blood Type], Employee.[Joining Date as Per Offer
Letter], Employee.[Date of Contract], Employee.[Employee File Number],
Employee.[Beginning of Service at DTME], Employee.[Native Language],
Employee.[Written Skills], Employee.[Verbal Skills]

FROM (Employee LEFT JOIN  [H_Employment] AS E
 ON Employee.EmployeeID = E.[Employee ID])
LEFT JOIN [H_Marital Status] AS B
 ON Employee.EmployeeID = B.EmployeeID

WHERE E.[Effective Date] =
  (SELECT Max([Effective Date])
   FROM H_Employment AS Tmp
   WHERE Tmp.[Employee ID] = E.[Employee ID])

AND B.[Date of Update] =
  (SELECT Max([Date of Update])
   FROM [H_Marital Status] as Tmp
   WHERE Tmp.EmployeeID = B.EmployeeID);

how can I make this query update corresponding tables? Please Help.- Hide quoted text -

- Show quoted text -

That's ture, I do have a one-to-many relationship, but in the query, I
filter to only one H_Employment/ H_Marital Status to each Employee
Record.
This type of solution has worked for me before when I used only two
tables, I just need to join the three. Any suggestions?

 

[Tom van Stiphout]

On Sat, 23 Aug 2008 10:19:28 -0700 (PDT), (e-mail address removed) wrote:

Queries can be non-updatable for many reasons. See
http://allenbrowne.com/ser-61.html

-Tom.
Microsoft Access MVP

On Sat, 23 Aug 2008 09:15:14 -0700 (PDT), (e-mail address removed) wrote:

You probably have one-to-many relations between these tables, so
editing should be done in a Master/Detail form (aka form/subform).
Take a look at the Northwind sample application's Order form, with its
OrderDetail subform.

-Tom.
Microsoft Access MVP

Hello,

I have three of my database tables joined in a 'select query' and
displayed in a 'form'
I need to be able to update those three tables using the form.

my problem is the resulting 'select query' of joined tables is not
allowing update of record. I don't understand why is this happening.
(I am sure that a 'select query' do allow update of tables in other
cases!) Here is the code that I used:

SELECT
E.[Employment History Entry], E.[Employee ID] AS
[H_Employment_EmployeeID], E.[Effective Date], E.[End Date], E.[Job
Title], E.[Function], E.[Sub Function], E.[Service Group], E.[Grade],
E.[Service Line], E.[Location], E.[Counselor], E.[Additional Role],
B.EmployeeID AS [H_Marital Status_EmployeeID], B.Status, B.[Date of
Update],
Employee.EmployeeID AS Employee_EmployeeID, Employee.[First Name],
Employee.[Middle Name], Employee.[Last Name], Employee.Gender,
Employee.[Place of Birth], Employee.[Date of Birth], Employee.
[Employment Status], Employee.[Nationality 1], Employee.[Nationality
2], Employee.[Blood Type], Employee.[Joining Date as Per Offer
Letter], Employee.[Date of Contract], Employee.[Employee File Number],
Employee.[Beginning of Service at DTME], Employee.[Native Language],
Employee.[Written Skills], Employee.[Verbal Skills]

FROM (Employee LEFT JOIN  [H_Employment] AS E
 ON Employee.EmployeeID = E.[Employee ID])
LEFT JOIN [H_Marital Status] AS B
 ON Employee.EmployeeID = B.EmployeeID

WHERE E.[Effective Date] =
  (SELECT Max([Effective Date])
   FROM H_Employment AS Tmp
   WHERE Tmp.[Employee ID] = E.[Employee ID])

AND B.[Date of Update] =
  (SELECT Max([Date of Update])
   FROM [H_Marital Status] as Tmp
   WHERE Tmp.EmployeeID = B.EmployeeID);

how can I make this query update corresponding tables? Please Help.- Hide quoted text -

- Show quoted text -

That's ture, I do have a one-to-many relationship, but in the query, I
filter to only one H_Employment/ H_Marital Status to each Employee
Record.
This type of solution has worked for me before when I used only two
tables, I just need to join the three. Any suggestions?