SQL View giving different results
-
I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.
I have also included the SQL query specs for this view.
I am hoping someone will have some idea what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.
Thanks!
Correct 3 records:
Incorrect 3 records:
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com -
I had an issue similar to this, but in SQL Server. I had a calculated field in a table. If I used SELECT * in a query the field values were incorrect. When I used SELECT Field1, Field2 etc. it worked fine.
Just a thought in case you had used SELECT *
-
I forgot to show the query code that produces the View I'm having problems with, so here it is...
ALTER VIEW [dbo].[vwTaskDetailsOverviewCSP]
AS
SELECT TOP (100) PERCENT
TaskAbstracts.TID, TaskAbstracts.TaskCategoryID, LookupTaskCategories.TaskCategoryName, TaskAbstracts.TaskID,
LookupTasks.TaskName, TaskAbstracts.Owner, [FirstName] + ' ' + [LastName] AS OwnerName,
TaskAbstracts.Status AS TaskStatus, TaskDetails.TaskDetailID, LookupTaskDetails.TaskDetailName,
TaskDetails.AssignedTo, TaskDetails.DueDate, TaskDetails.DoneDate, TaskDetails.Status AS Status,
TaskDetails.PercentComplete, TaskDetails.StartDate, TaskDetails.Priority AS Priority, TaskDetails.Description,
TaskDetails.Escalate, TaskAbstracts.Priority AS TaskPriority, TaskAbstracts.BeginDate AS TaskBegin,
TaskAbstracts.EndDate AS TaskEnd, TaskAbstracts.Notes AS TaskNotes, [TaskDetails].[ID] AS UniqueID,
TaskDetails.ApprovePending, TaskDetails.ForecastHours, TaskDetails.ForecastDate, TaskDetails.Dependency,
TaskDetails.MaintOrDevel, TaskDetails.ScoreOverallQual, TaskDetails.ScoreAppearence, TaskDetails.ScoreQualityAnalysisProject,
TaskDetails.ScoreFunctionality, TaskDetails.ActualHours, TaskDetails.Requestor, TaskDetails.DateRequested,
TaskDetails.CSPPriority, TaskDetails.SSMA_TimeStamp
FROM
((((TaskDetails RIGHT JOIN TaskAbstracts ON TaskDetails.TID = TaskAbstracts.TID)
LEFT JOIN LookupTasks ON TaskAbstracts.TaskID = LookupTasks.TaskID)
LEFT JOIN LookupTaskDetails ON TaskDetails.TaskDetailID = LookupTaskDetails.TaskDetailID)
LEFT JOIN LookupTaskCategories ON TaskAbstracts.TaskCategoryID = LookupTaskCategories.TaskCategoryID)
LEFT JOIN Employees ON TaskAbstracts.Owner = Employees.LanID
GO
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com -
Are you sure that you're using the same view and not different views with the same name and different schema? Is it possible that the information changed between each query? Why do you have the TOP 100 PERCENT?
-
Luis, thank you for your input, but yes, I am very sure that I'm using the same view. I only have one view that has a name anything like the view I'm using. Of course I have checked that the data has not changed.
I did find my error this morning. I had not included in the failing access link to make sure the record, within Access, could be identified as unique. So, that was the problem, and is now solved.
When linking a SQL Server recordset (table, view, SP, ...) Access requests that the person/user doing the linking provide what field or group of fields will make the record identifiable as unique. I missed one field when doing that task. My bad, but I really don't think I'll make that mistake again.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply