Solved Update table (1 Viewer)

[smtazulislam]

Hi,
I have two table, tblEmployee & tblDesignation,
fields : DesignationEn; DesignationAr - Short Text - Index (No Duplicate) and with relationship tblEmployee - To DesignationEn

tblDesignationChange :
fields: DateFrom - DateTo- CurrentDesignation-NewDesignation-Notes
My question, how I create a query for three table update once when I update DesignationChange table.

I need a query for qryDesignationChange .

Note: tblDesignationChange, it is a SUBFORM AND Link with a Parent form frmEmployees.

Appreciate any help.

 

[Minty]

It sounds like you have duplicated data as you shouldn't have to update underlying records in a correctly normalised structure, but I'm struggling to visualise your data structure from the description.
Can you post up a picture of the tables and relationships?

 

[smtazulislam]

It sounds like you have duplicated data as you shouldn't have to update underlying records in a correctly normalised structure, but I'm struggling to visualise your data structure from the description.
Can you post up a picture of the tables and relationships?

Hi Minty.
Thanks for reply

 

[Minty]

That's sort of helped, but still confusing - You have DesignationAr in both the employee and link table, how does that work?
Couldn't you simply query the Employee table to generate the lookup automatically?

 

[smtazulislam]

That's sort of helped, but still confusing - You have DesignationAr in both the employee and link table, how does that work?
Couldn't you simply query the Employee table to generate the lookup automatically?

Its was automatically work. Actually I type Designation = DesignationAr same data any Translate (English to Arabic) when I select from Designation English it was fill up via VBA DesignationAR fields.

 

[onur_can]

Open the Relationships window. Double-click the relationship line between the two tables and in the window that appears, select the option to update related fields consecutively and save and close.

 

[Pat Hartman]

If the PK of LtblDesignationLookup is two fields, then, the joins to that table need to also be on two fields. If Designation is unique by itself, that can be the PK. If ar is unique by itself, that can be the PK. If it takes BOTH to be unique, the better solution is to create an autonumber as the PK and create a unique index on the two fields to implement the business rule that the combination of both must be unique. You would create this multi-column index by using the index dialog. You cannot create it by using the table design view the way you would with a multi-column PK. If you go with the autonumber, change your relationships to join to the autonumber rather than to the other two columns.

PS, I still do not understand your original question. I agree with Minty, you more than likely have a design problem with the database.

 

[smtazulislam]

If the PK of LtblDesignationLookup is two fields, then, the joins to that table need to also be on two fields. If Designation is unique by itself, that can be the PK. If ar is unique by itself, that can be the PK. If it takes BOTH to be unique, the better solution is to create an autonumber as the PK and create a unique index on the two fields to implement the business rule that the combination of both must be unique. You would create this multi-column index by using the index dialog. You cannot create it by using the table design view the way you would with a multi-column PK. If you go with the autonumber, change your relationships to join to the autonumber rather than to the other two columns.

PS, I still do not understand your original question. I agree with Minty, you more than likely have a design problem with the database.

This field needs to show in the table "Text" not Number. So that, I can't take AutoNumber. I apologize.
As per your advice I have change relationship. Now It is "one-to-many"

Actually I want update 2 tables on a single form. (attached my form)

1. tblEmployee
2. tblDesignationChange
my form name is frmDesignationChange

You can see my two table DATA TYPE in message #3
Just a UPDATE query or SQL for my frmDesignationChange Form. Appreciate any help.

EDIT/
You can call it REPLACING data value.

 

[Pat Hartman]

Use an update query to update DesignationAr from tblDesignationChange if you want to change the value permanently.

If you want to do this on the fly, then join to LtblDesignationLookup from tblDesignationChange rather than from tblEmployee

 

[smtazulislam]

Thanks for reply.
See I try to Update query as ...

UPDATE tblEmployee INNER JOIN tblDesignationChange ON tblEmployee.EmployeeID = tblDesignationChange.EmployeeID SET tblDesignationChange.N_Designation = [tblEmployee].[DesignationAr];

But when I change to tblDesignationChange in field N_Designation then tblEmployee field DesignationAr not response or Change.

 

[Pat Hartman]

Isn't the update statement backwards? Don't you want to update DesignationAr in tblEmployee to the value in the "change" table?

 

[smtazulislam]

Isn't the update statement backwards? Don't you want to update DesignationAr in tblEmployee to the value in the "change" table?

It is my last Update query and it was execute perfectly.

UPDATE tblEmployee INNER JOIN tblDesignationChange ON tblEmployee.EmployeeID = tblDesignationChange.EmployeeID SET tblEmployee.DesignationAr = [tblDesignationChange].[N_DesignationAr], tblEmployee.Designation = [tblDesignationChange].[N_Designation];

And I have button_Click to update table tblEmployee to DesignationAr

CurrentDb.Execute "DesignationUpdate"

and after try it

DoCmd.OpenQuery "DesignationUpdate", acViewNormal, acReadOnly

But those command run all of data in the table what I dont like.
My question now, Can run only last entry record data with my query table ?

 

[Pat Hartman]

Any query can have criteria. Just add a where clause that selects the record you want to update. For an operation like this, I would expect to update all rows but if you want to update only one, use criteria that selects that employee.