returning all records

[Guest]

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]);

 

[David S via AccessMonster.com]

OK, I'm trying to recreate your tables and the SQL, and I'm getting a "Syntax
error in JOIN operation" - are you sure the SQL for your query is exactly
what it is in the database?

I'm sure you would have already received feedback about how this database
design has some issues - are you the "me" responsible for this post as well?
http://www.accessmonster.com/Uwe/Fo...-make-it-work#[email protected]


Anyway, what you want to do can be achieved by replaced the INNER JOINs in
your query with LEFT OUTER JOINs. Unfortunately, I can't give you the exact
SQL because I can't get your SQL to work in my database - sorry about that...

 

[Guest]

David,

Yes I am the same person, jumping from work to home and end up using two
differant profiles. The SQL should be correct, I copied it straight out of
Access and pasted it here. Thank you Jason

 

[Guest]

Sorry I did make a change from what I had posted.




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.QI = [Employee IV].[QI Number]) AND ([EMPLOYEE INTUBATION].Date =
[Employee IV].Date)) INNER JOIN [Employee Trip Count] ON (Employee.QI =
[Employee Trip Count].[QI Number]) AND ([Employee IV].Date = [Employee Trip
Count].Date) AND (qryROADSAFETY.Date = [Employee Trip Count].Date);

 

[Jason via AccessMonster.com]

Sorry eveyone, I will stick to the link below.