Hello,
In SQL Server I have created some tables. A main 'Software' table with some text fields and foreign keys pointing to ids of secondary tables like 'InstallationType' and 'LicenseType'. I have also created a view showing the labels corresponding to the 'InstallationTypeId' and 'LicenseTypeId'.
Remark: The main reason I am using a View is that I have to do some calculations involving several tables -> out of scope of this post
In Access I have defined a linked table pointing to the view. If I modify the values corresponding to the text fields (like the name of the version of the Software) this is ok. But if I am changing the value of the 'InstallationType' label (like replacing 'Saas' by 'On Premise' for the Salesforce row) there is an issue.
The label of the 'InstallationType' (of the record with id = 2) is modified ('Saas' is replaced by 'On Premise'). But my purpose is to modify the 'InstallationTypeId' of the Salesforce row in the 'Software' table instead.
So I'm wondering how may I modify the 'SQL Update' query executed in Access? Maybe through a Form?
Expected query, something like:
UPDATE Software
SET InstallationTypeId =
(SELECT Id
FROM InstallationType
WHERE InstallationTypeLabel = 'On Premise')
WHERE Name = 'Salesforce' AND Version = 'Winter 2020'
Thanks in advance for sharing your experiences!
Rudy.