Here it is
qptQCSelect SQL is
-------
SELECT * FROM AlertSelect_UI_V WHERE 1=2
AlertSelect_V SQL CODE is
--------
/****** Object: View [dbo].[AlertSelect_UI_V] Script Date: 01/29/2010 08:27:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create view [dbo].[AlertSelect_UI_V] AS
SELECT RVW.QCReviewId, A.AlertId, RVW.CaseNumber, c.caseid RVW.QCReviewerId, STF.LastName + ', ' + LEFT(STF.FirstName, 1), '') AS ReviewerName, RVW.ReviewTypeId, RT.StateRollup, RT.QCType, RT.ReviewType, QCSampleDate, RVW.QCStart, RVW.QCFinalDate, RVW.QCApproved, RVW.RowVersion, RVW.ChecklistVersion, dbo.LOB.Description AS LOB, c.SecondaryWorkgroup, c.EventSubtype, RVW.AcctOpenClosedNonSAR
FROM dbo.QCReview AS RVW INNER JOIN
dbo.ReviewType_V AS RT ON RVW.ReviewTypeId = RT.ReviewTypeId
LEFT OUTER JOIN dbo.Alert AS A ON A.AlertSurrogateId = RVW.AlertSurrogateId
LEFT OUTER JOIN
dbo.LOB ON dbo.LOB.LobSurrogateId = A.LOBSurrogateId LEFT OUTER JOIN
dbo.AlertHistory AS AHO ON RVW.OriginalDispositionHistoryId = AHO.AlertHistoryId LEFT OUTER JOIN
dbo.AssignedAlert AS AA ON RVW.AlertSurrogateId = AA.AlertSurrogateId LEFT OUTER JOIN
dbo.ScoreGroup AS SG ON AA.ScoreGroupId = SG.ScoreGroupId LEFT OUTER JOIN
dbo.Staff AS STF ON RVW.QCReviewerId = STF.StaffId LEFT OUTER JOIN
dbo.Staff AS STI ON RVW.InvestigatorId = STI.StaffId LEFT OUTER JOIN
dbo.ProcessEventLog AS PEL ON RVW.ProcessEventLogId = PEL.ProcessEventLogId LEFT OUTER JOIN
dbo.SamplePopulationLog2 AS spl2 ON spl2.SourceRecordIdType = 'CaseId' AND spl2.SampleProcessEventLogId = RVW.ProcessEventLogId AND
RVW.CaseNumber = spl2.CaseNumber LEFT OUTER JOIN
dbo.[Case] AS c ON CASE WHEN IsNumeric(spl2.SourceRecordid) = 1 THEN CAST(spl2.SourceRecordid AS integer) ELSE NULL END = c.CaseId
WHERE (RVW.QCReviewId NOT IN
(SELECT DISTINCT QCReviewId
FROM dbo.QCReview
WHERE (ReviewedPriorToDatabase = 'Y') OR
(IsInvalid = 'Yes'))) AND ((RVW.QCApproved = 'N') OR
((RVW.QCApproved = 'Y') AND (CAST(CONVERT(VarChar(10), PEL.EndTime, 101) AS DateTime) BETWEEN CONVERT(varchar, DATEADD(m, - 4,
GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))))
Here RVW.QCReview is the main table and I try to update QCReviewerid, QCFlag and rowversion (Timestamp). This has been working fine. but after i have introduced a new column RVW.AcctOpenClosedNonSAR in the view which already exists in the QCreview table, am unable to do an update. This column i want to use as checkbox so users can choose multiple alerts or cases and assign to userid (QCreviewerId)
What version of Access are you using? Access 2007 / SQL Server 2008
What exactly are you trying to accomplish? Sounds like you want to use a form to identify cases, then assign an investigator to a case or cases.
Ans: Trying to provide a checkbox (using newly introduced column RVW.AcctOpenClosedNonSAR) to user so they can choose multiple alerts or cases and assign to investigator.