I have a SQL query with a few subqueries. The subqueries essentially pull data from other tables together in order to combine records to be accessed from a single source. It is setup that when accesses it pulls the most recent record added. However, if I were to add a new record to the group, and try to look it up later the main query does not recognize that it was added. This code is used in an access query that runs the code below to populate a table with the most recent employee records.
The query is below
SELECT AC.REG_NR
,AC.DIS_NR
,AC.GEMSID
,AC.TMS_ID
,AC.EMP_NA
,AC.EMP_SEX_TYP_CD
,AC.EMP_EOC_GRP_TYP_CD
,AC.DIV_NR
,AC.CTR_NR
,AC.JOB_CLS_CD_DSC_TE
,AC.JOB_GRP_CD
,AC.Job_Function
,AC.Job_Group
,AC.Meeting_Readiness_Rating
,AC.Manager_Readiness_Rating
,CD.Employee_ID
,CD.Meeting_Readiness_Rating AS Expr1
,CD.Manager_Readiness_Rating AS Expr2
,CD.Meeting_End_Date
,CD.EmployeeFeedback
,CD.DevelopmentForEmployee1
,CD.DevelopmentForEmployee2
,CD.DevelopmentForEmployee3
,CD.DevelopmentForEmployee4
,CD.DevelopmentForEmployee5
,CD.Justification
,CD.Changed
,CD.Notes
FROM dbo.AC_Source AS AC
LEFT OUTER JOIN (
SELECT Employee_ID
,Meeting_Readiness_Rating
,Manager_Readiness_Rating
,Meeting_End_Date
,EmployeeFeedback
,DevelopmentForEmployee1
,DevelopmentForEmployee2
,DevelopmentForEmployee3
,DevelopmentForEmployee4
,DevelopmentForEmployee5
,Justification
,Changed
,Notes
,RowNum
FROM (
SELECT Employee_ID
,Meeting_Readiness_Rating
,Manager_Readiness_Rating
,Meeting_End_Date
,EmployeeFeedback
,DevelopmentForEmployee1
,DevelopmentForEmployee2
,DevelopmentForEmployee3
,DevelopmentForEmployee4
,DevelopmentForEmployee5
,Justification
,Changed
,Notes
,ROW_NUMBER() OVER (
PARTITION BY Employee_ID ORDER BY Meeting_End_Date DESC
) AS RowNum
FROM (
SELECT EmployeeID AS Employee_ID
,MeetingReadinessLevel AS Meeting_Readiness_Rating
,ManagerReadinessLevel AS Manager_Readiness_Rating
,logdate AS Meeting_End_Date
,EmployeeFeedback
,DevelopmentForEmployee1
,DevelopmentForEmployee2
,DevelopmentForEmployee3
,DevelopmentForEmployee4
,DevelopmentForEmployee5
,Justification
,Changed
,Notes
FROM dbo.AC_CDData_1
UNION ALL
SELECT Employee_ID
,Meeting_Readiness_Rating
,Manager_Readiness_Rating
,Meeting_End_Date
,'' AS EmployeeFeedback
,'' AS DevelopmentForEmployee1
,'' AS DevelopmentForEmployee2
,'' AS DevelopmentForEmployee3
,'' AS DevelopmentForEmployee4
,'' AS DevelopmentForEmployee5
,'' AS Justification
,'' AS Changed
,'' AS Notes
FROM dbo.TMS_Data_Latest_Career_Meeting_Rating
WHERE (Plan_Year = '2013')
) AS A
) AS B
WHERE RowNum = 1
) AS CD
ON AC.TMS_ID = CD.Employee_ID
When data is added it is added into the AC_CDDATA_1 table which is in one of the inner subqueries. It will not recognize it because the outer most select statement does not have a matching ID for a new one that is added. I have tried the different subqueries together and they all work with the new IDs I add in. However when you do the entire query it does not recognize the new IDs. I tried a RIGHT OUTER JOIN but then that only included the employees from the INNER queries, then I tried a FULL OUTER JOIN which pulled all the records in SQL Server but when I tried to run the download in access I got an INVALID USE OF NULL error. I am running out of ideas, is it in the JOIN or is it one of the subqueries or do I need to add something?
Quite an impressive sql statement. But I believe if you follow the rules of db design there should be no duplication of data in tables.
Without knowing your table and relationship design it would be next to impossible to figure out if what you are doing is possible.
A simple case of why you should not design you're queries this way is in a master detail relationship. the queries might be trying to add the details before the master. This will generate an error every time.
To solve or to start a new method to accomplish you're goals I would create a form with a button and using VBA code run the individual queries one at a time.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments