Creating a SQL query from access (1 Viewer)

[mikke3141]

Hi,

I am trying to generate a SQL-string from Access to make a UPDATE from excel to the Access database.

 UPDATE CountryTbl INNER JOIN EmployeeTbl ON CountryTbl.CountryID = EmployeeTbl.Country_ID SET EmployeeTbl.Country_ID = "Italy" WHERE (((EmployeeTbl.EmpID)=50));

This will of course not work as Italy is not a the ID, but the Country name itself. Access provides Combobox to lookup the relevant values and related keys when updating values, but how do the same in the SQL code. I tried something like in the attached picture to generate the code, but it did not work.

 

[jdraw]

Cross posted @ https://www.accessforums.net/showthread.php?t=78642

Please provide a brief description of your "project/business".

 

[arnelgp]

you put the Criteria to the Update to field:
put "Italy" on Criteria under Country.

 

[mikke3141]

Italy is not the criteria, but the value that I want to enter to the employee table for the employee with number 1234. And Italy word is not in the employee table, but in the Country table that is linked to the employee table ([CountryTbl].[CountryID]=[EmployeeTbl].[Country_ID]. How can it be done?

 

[theDBguy]

Hi. Can you describe using plain words what you want the query to do? For example, I'd like to update all records in the employees table to change their status from active to inactive if they live in Italy. Also, is the Country_ID field in EmployeeTbl a lookup field?

 

[mikke3141]

I want to change the country of employee 1234 in EmployeeTbl to Italy using the Country information that is located in the linked table of CountryTbl. Basically after the update the EmployeeTbl would contain the Country key of Italy. At the moment of update I do not know the country key number in CountryTbl, just the country in question Italy. The countryID in EmployeeTbl and the CountryID in CountryTbl are equal and linked.

 

[theDBguy]

I want to change the country of employee 1234 in EmployeeTbl to Italy using the Country information that is located in the linked table of CountryTbl. Basically after the update the EmployeeTbl would contain the Country key of Italy. At the moment of update I do not know the country key number in CountryTbl, just the country in question Italy. The countryID in EmployeeTbl and the CountryID in CountryTbl are equal and linked.

Okay, thanks! Just a guess but how about?

UPDATE EmployeeTbl 

SET Country_ID=DLookup("CounryID","CountryTbl","Country='Italy'") 

WHERE EmpID=1234

Hope it helps...

 

[mikke3141]

Thank you DBguy, your solution is updating the field in question.

SQL_string = "UPDATE (ManagerTbl INNER JOIN CCTbl ON ManagerTbl.ManID = CCTbl.Manager_ID) INNER JOIN (ColAgrTbl INNER JOIN " & _
"(LocationTbl INNER JOIN ((CountryTbl INNER JOIN (ContractTbl INNER JOIN EmployeeTbl ON ContractTbl.CoID = EmployeeTbl.Contract_ID) " & _
"ON CountryTbl.CountryID = EmployeeTbl.Country_ID) INNER JOIN (MonthTbl INNER JOIN HCTbl ON MonthTbl.ID = HCTbl.Month_ID) ON " & _
"EmployeeTbl.EmpID = HCTbl.EmpID) ON LocationTbl.LocID = EmployeeTbl.Location_ID) ON ColAgrTbl.CAID = EmployeeTbl.Collective_Agreement_ID) " & _
"ON CCTbl.CCID = EmployeeTbl.Cost_Center_ID SET EmployeeTbl.Country_ID = DLookup(CountryTbl.CountryID, 'CountryTbl', (CountryTbl.Country)='Italy') WHERE (((EmployeeTbl.EmpID)=1234));"

The SQL SET part however just empties to CountryID and does not maintain it with the correct ID for employee 1234.

 

[theDBguy]

Thank you DBguy, your solution is updating the field in question.
...
The SQL SET part however just empties to CountryID and does not maintain it with the correct ID for employee 1234.

The SQL you posted doesn't look anything like the one I posted, so I am not sure how the two are related. In other words, you lost me with your last statement quoted above. Sorry.

 

[arnelgp]

why do you have too much join?
what tables need to be updated beside EmployeeTbl?

 

[mikke3141]

Well just to clarify I removed the joins

SQL_string = "UPDATE CountryTbl INNER JOIN EmployeeTbl ON CountryTbl.CountryID = EmployeeTbl.Country_ID SET EmployeeTbl.Country_ID = DLookup(CountryTbl.CountryID, 'CountryTbl', (CountryTbl.Country)='Italy') WHERE (((EmployeeTbl.EmpID)=1234));"

This code empties the value and does not update the countryID in the EmployeeTbl.

 

[theDBguy]

Well just to clarify I removed the joins

SQL_string = "UPDATE CountryTbl INNER JOIN EmployeeTbl ON CountryTbl.CountryID = EmployeeTbl.Country_ID SET EmployeeTbl.Country_ID = DLookup(CountryTbl.CountryID, 'CountryTbl', (CountryTbl.Country)='Italy') WHERE (((EmployeeTbl.EmpID)=1234));"

This code empties the value and does not update the countryID in the EmployeeTbl.

Again, that SQL statement doesn't look the same as the one I posted. What happened if you just used the one I posted? Something else happens? What?

 

[arnelgp]

SQL_string = "UPDATE EmployeeTbl, CountryTbl SET EmployeeTbl.Country_ID = CountryTbl.CountryID
WHERE EmployeeTbl.EmpID=1234 AND CountryTbl.Country='Italy';"

 

[mikke3141]

You are the best Arnelgp Easy solution, but I was just too dumb to see it :banghead:

 

[theDBguy]

You are the best Arnelgp Easy solution, but I was just too dumb to see it :banghead:

Hi. Glad to hear you got it sorted out. Good luck with your project.