J
Jason
Thank you to everyone out there who has tried to help me over the past few
days. I am having a hard time getting my mind around this problem. The SQL
at the bottom is as close as I can get. The tables listed below contain data
on employee performance; each row contains a date field, employee ID and 2-3
fields regarding performance. All of this data is imported into each
performance tables on a quarterly basis, but only employees that have
performed that skill are imported. Here is a sample of each performance table.
[EMPLOYEE INTUBATION]
Field Names ID, QI, DATE, ETTA, ETTS (QI is the employee ID)
Field Data 1, 275, q2 2005, 1, 1
All other performance tables are similiar with the exception of the
[employee RS data] (actually it’s a query that adds in the employee ID) it
looks like this.
[qryROADSAFETY]
Field Name DATE, QI NUMBER, MILES, SCORE
Field Data Q3 2004, 275, 1178, 8
The query below successfully combines all the data into one table, however
it only includes records for employees that have data in all the tables. The
tables only include data on employees that have performed that table’s
“skill†in that quarter. Only a few employees have preformed all the skills
and none of them perform them all for all four quarters. It there any way to
return all the employees even if they are not listed in all tables. Can the
query return a null value for employees that did not perform that skill in
that quarter? Basically I would like my final result to look like this.
Fields: EMPLOYEEQI,ETTA,EETA,SUCCESS,ATTEMPTS,TOTAL,ALS,NX,RSMILES,RSSCORE
DATA
275,NULL,NULL,1,1,123,124,43,8
The only way I can think of to get the data is to go back to excel and add
in each employee and set up some type of function that would return a zero
value for employees that have not performed that skill, but that would be
very time consuming. I use crystal reports to pull data from one of our
software applications.
Thank you all for your patience and help. Jason
Query SQL that returns only 80 of the roughly 500 records I am looking for.
SELECT Employee.QI, [EMPLOYEE INTUBATION].Date, [EMPLOYEE INTUBATION].ETTA,
[EMPLOYEE INTUBATION].ETTS, [Employee IV].Attempts, [Employee IV].Successes,
[Employee Trip Count].[Total Calls], [Employee Trip Count].[ALS Calls],
[Employee Trip Count].[NX Calls], qryROADSAFETY.Miles, qryROADSAFETY.Score
FROM (((Employee INNER JOIN qryROADSAFETY ON Employee.QI = qryROADSAFETY.QI)
INNER JOIN [EMPLOYEE INTUBATION] ON (Employee.QI = [EMPLOYEE INTUBATION].QI)
AND (Employee.QI = [EMPLOYEE INTUBATION].QI)) INNER JOIN [Employee IV] ON
([EMPLOYEE INTUBATION].Date = [Employee IV].Date) AND (Employee.QI =
[Employee IV].[QI Number])) INNER JOIN [Employee Trip Count] ON ([Employee
Trip Count].Date = qryROADSAFETY.Date) AND ([Employee IV].Date = [Employee
Trip Count].Date) AND (Employee.QI = [Employee Trip Count].[QI Number]);
days. I am having a hard time getting my mind around this problem. The SQL
at the bottom is as close as I can get. The tables listed below contain data
on employee performance; each row contains a date field, employee ID and 2-3
fields regarding performance. All of this data is imported into each
performance tables on a quarterly basis, but only employees that have
performed that skill are imported. Here is a sample of each performance table.
[EMPLOYEE INTUBATION]
Field Names ID, QI, DATE, ETTA, ETTS (QI is the employee ID)
Field Data 1, 275, q2 2005, 1, 1
All other performance tables are similiar with the exception of the
[employee RS data] (actually it’s a query that adds in the employee ID) it
looks like this.
[qryROADSAFETY]
Field Name DATE, QI NUMBER, MILES, SCORE
Field Data Q3 2004, 275, 1178, 8
The query below successfully combines all the data into one table, however
it only includes records for employees that have data in all the tables. The
tables only include data on employees that have performed that table’s
“skill†in that quarter. Only a few employees have preformed all the skills
and none of them perform them all for all four quarters. It there any way to
return all the employees even if they are not listed in all tables. Can the
query return a null value for employees that did not perform that skill in
that quarter? Basically I would like my final result to look like this.
Fields: EMPLOYEEQI,ETTA,EETA,SUCCESS,ATTEMPTS,TOTAL,ALS,NX,RSMILES,RSSCORE
DATA
275,NULL,NULL,1,1,123,124,43,8
The only way I can think of to get the data is to go back to excel and add
in each employee and set up some type of function that would return a zero
value for employees that have not performed that skill, but that would be
very time consuming. I use crystal reports to pull data from one of our
software applications.
Thank you all for your patience and help. Jason
Query SQL that returns only 80 of the roughly 500 records I am looking for.
SELECT Employee.QI, [EMPLOYEE INTUBATION].Date, [EMPLOYEE INTUBATION].ETTA,
[EMPLOYEE INTUBATION].ETTS, [Employee IV].Attempts, [Employee IV].Successes,
[Employee Trip Count].[Total Calls], [Employee Trip Count].[ALS Calls],
[Employee Trip Count].[NX Calls], qryROADSAFETY.Miles, qryROADSAFETY.Score
FROM (((Employee INNER JOIN qryROADSAFETY ON Employee.QI = qryROADSAFETY.QI)
INNER JOIN [EMPLOYEE INTUBATION] ON (Employee.QI = [EMPLOYEE INTUBATION].QI)
AND (Employee.QI = [EMPLOYEE INTUBATION].QI)) INNER JOIN [Employee IV] ON
([EMPLOYEE INTUBATION].Date = [Employee IV].Date) AND (Employee.QI =
[Employee IV].[QI Number])) INNER JOIN [Employee Trip Count] ON ([Employee
Trip Count].Date = qryROADSAFETY.Date) AND ([Employee IV].Date = [Employee
Trip Count].Date) AND (Employee.QI = [Employee Trip Count].[QI Number]);