SSF
asked on
can union query results be edited?
I'm using a union query as a source for a form. "Allow Edits" on form property is "yes" and recordset type is "dynaset", but when I type something in one of the fields I get a message at the bottom saying "this recordset is not updatable". what's wrong?
ASKER
There are a couple of IIF functions in the first query. Also, doesn't union query require the same field names in both queries? Anyway, here's the sql:
SELECT DISTINCTROW tblFD.FD_Num AS FD_Num, tblFD.BrandName AS BrandName, tblFD.[X&W_Flag] AS [X&W_Flag], tblFD.MainIndiCode AS MainIndiCode, tblFD.Mfg AS Mfg, tblATCDesc.ATCd AS ATCd, tblATCDesc.EngDesc AS EngDesc, tblFD.DoseForm AS DoseForm, tblFD.ApplRoute AS ApplRoute, tblFD.NumOfUnits AS NumOfUnits, tblFD.DoseFormUnits AS DoseFormUnits, tblFD.DoseFormType AS DoseFormType, tblFD.NumOfUnitsPerDose AS NumOfUnitsPerDose, tblFD.DoseFm AS DoseFm, tblFD.PackSize AS PackSize, tblFD.PackType AS PackType, tblFD.Phase1 AS Phase1, tblFD.EasyGCNSkip AS EasyGCNSkip, [Tbl_IDDF-DE].IDC AS IDC, [Tbl_IDDF-DE].FD_Num AS [FD#], [Tbl_IDDF-DE].LabelName AS LabelName, [Tbl_IDDF-DE].BrandName AS Brand_Name, [Tbl_IDDF-DE].MfgIdentifie r AS MfgIdentifier, [Tbl_IDDF-DE].GCNSeqno AS GCNSeqno, [Tbl_IDDF-DE].RecReady AS RecReady, [Tbl_IDDF-DE].Notes AS Notes, IIf(IsNull([Tbl_IDDF-DE]![ Initial]), "SYS",[Tbl _IDDF-DE]! [Initial]) AS Initial, IIf(IsNull([Tbl_IDDF-DE]![ AddDate]), Date(),[Tb l_IDDF-DE] ![AddDate] ) AS AddDate, [Tbl_IDDF-DE].ModifyDate, [Tbl_IDDF-DE].CallMfg AS CallMfg, [Tbl_IDDF-DE].NewGCNIngre AS NewGCNIngre, [Tbl_IDDF-DE].NewGCNSalt AS NewGCNSalt, [Tbl_IDDF-DE].NewGCNStr AS NewGCNStr, [Tbl_IDDF-DE].NewGCN_DF AS NewGCN_DF, [Tbl_IDDF-DE].NewGCNRt AS NewGCNRt, [Tbl_IDDF-DE].QCDate AS QCDate, Null AS iobsdte, " " AS iobsdte1, [Tbl_IDDF-DE].QCInitial AS QCInitial
FROM ((tblFD LEFT JOIN tblATCDesc ON tblFD.ATC = tblATCDesc.ATCd) LEFT JOIN [Tbl_IDDF-DE] ON tblFD.FD_Num = [Tbl_IDDF-DE].FD_Num)
ORDER BY tblFD.FD_Num
UNION SELECT DISTINCTROW [Tbl_IDDF-DE_Obsolete].FD_ Num AS FD_Num, [Tbl_IDDF-DE_Obsolete].Bra ndName AS BrandName, [Tbl_IDDF-DE_Obsolete].[X& W_Flag], Tbl_FD.MainIndiCode AS MainIndiCode, [Tbl_HD/HI].AbbrName AS Mfg, tblATCDesc.ATCd AS ATCd, tblATCDesc.EngDesc AS EngDesc, Tbl_DoseFormDesc.DoseForm AS DoseForm, Tbl_DoseFormDesc.ApplRoute AS ApplRoute, Tbl_FD.NumOfUnits AS NumOfUnits, Tbl_FD.DoseFormUnits AS DoseFormUnits, Tbl_FD.DoseFormType AS DoseFormType, Tbl_FD.NumOfUnitsPerDose AS NumOfUnitsPerDose, Tbl_FD.DoseForm AS DoseFm, Tbl_FD.PackSize AS PackSize, Tbl_FD.PackType AS PackType, 0 AS Phase1, 0 AS EasyGCNSkip, [Tbl_IDDF-DE_Obsolete].IDC AS IDC, [Tbl_IDDF-DE_Obsolete].FD_ Num AS [FD#], [Tbl_IDDF-DE_Obsolete].Lab elName AS LabelName, [Tbl_IDDF-DE_Obsolete].Bra ndName AS Brand_Name, [Tbl_IDDF-DE_Obsolete].Mfg Identifier AS MfgIdentifier, [Tbl_IDDF-DE_Obsolete].GCN Seqno AS GCNSeqno, [Tbl_IDDF-DE_Obsolete].Rec Ready AS RecReady, [Tbl_IDDF-DE_Obsolete].Not es AS Notes, [Tbl_IDDF-DE_Obsolete].Ini tial AS Initial, [Tbl_IDDF-DE_Obsolete].Add Date AS AddDate, [Tbl_IDDF-DE_Obsolete].Mod ifyDate AS ModifyDate, [Tbl_IDDF-DE_Obsolete].Cal lMfg AS CallMfg, [Tbl_IDDF-DE_Obsolete].New GCNIngre AS NewGCNIngre, [Tbl_IDDF-DE_Obsolete].New GCNSalt AS NewGCNSalt, [Tbl_IDDF-DE_Obsolete].New GCNStr AS NewGCNStr, [Tbl_IDDF-DE_Obsolete].New GCN_DF AS NewGCN_DF, [Tbl_IDDF-DE_Obsolete].New GCNRt AS NewGCNRt, [Tbl_IDDF-DE_Obsolete].QCD ate AS QCDate, [Tbl_IDDF-DE_Obsolete].iob sdte, [Tbl_IDDF-DE_Obsolete].iob sdte as iobsdte1, [Tbl_IDDF-DE_Obsolete].QCI nitial AS QCInitial
FROM (Tbl_DoseFormDesc RIGHT JOIN ((tblATCDesc RIGHT JOIN Tbl_FD ON tblATCDesc.ATCd = Tbl_FD.ATC) LEFT JOIN [Tbl_HD/HI] ON Tbl_FD.FstMfgNum = [Tbl_HD/HI].Key) ON Tbl_DoseFormDesc.DoseFormC d = Tbl_FD.DoseFormCode) INNER JOIN [Tbl_IDDF-DE_Obsolete] ON Tbl_FD.Key = [Tbl_IDDF-DE_Obsolete].FD_ Num;
SELECT DISTINCTROW tblFD.FD_Num AS FD_Num, tblFD.BrandName AS BrandName, tblFD.[X&W_Flag] AS [X&W_Flag], tblFD.MainIndiCode AS MainIndiCode, tblFD.Mfg AS Mfg, tblATCDesc.ATCd AS ATCd, tblATCDesc.EngDesc AS EngDesc, tblFD.DoseForm AS DoseForm, tblFD.ApplRoute AS ApplRoute, tblFD.NumOfUnits AS NumOfUnits, tblFD.DoseFormUnits AS DoseFormUnits, tblFD.DoseFormType AS DoseFormType, tblFD.NumOfUnitsPerDose AS NumOfUnitsPerDose, tblFD.DoseFm AS DoseFm, tblFD.PackSize AS PackSize, tblFD.PackType AS PackType, tblFD.Phase1 AS Phase1, tblFD.EasyGCNSkip AS EasyGCNSkip, [Tbl_IDDF-DE].IDC AS IDC, [Tbl_IDDF-DE].FD_Num AS [FD#], [Tbl_IDDF-DE].LabelName AS LabelName, [Tbl_IDDF-DE].BrandName AS Brand_Name, [Tbl_IDDF-DE].MfgIdentifie
FROM ((tblFD LEFT JOIN tblATCDesc ON tblFD.ATC = tblATCDesc.ATCd) LEFT JOIN [Tbl_IDDF-DE] ON tblFD.FD_Num = [Tbl_IDDF-DE].FD_Num)
ORDER BY tblFD.FD_Num
UNION SELECT DISTINCTROW [Tbl_IDDF-DE_Obsolete].FD_
FROM (Tbl_DoseFormDesc RIGHT JOIN ((tblATCDesc RIGHT JOIN Tbl_FD ON tblATCDesc.ATCd = Tbl_FD.ATC) LEFT JOIN [Tbl_HD/HI] ON Tbl_FD.FstMfgNum = [Tbl_HD/HI].Key) ON Tbl_DoseFormDesc.DoseFormC
UNION queries are not updateable.
JimD.
JimD.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks all!
It seems plausible that a union query could be updated, after all, each field is unique. But if there are an ambiguities, that would not be the case.
Are there any calculated fields in this query, (or any of the queries)? If so, you won't be able to update it.
What is the SQL involved?
Brian