returning all records

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

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/For...essMonster.com


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...


--
Message posted via http://www.accessmonster.com

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

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

OK, I'm still getting the syntax error - I think it's because I don't have
the correct fields in the performance tables. Deconstructing from the query,
I have:
[EMPLOYEE INTUBATION] - ID, QI, DATE, ETTA, ETTS
[Employee IV] - ID, [QI Number], Date, Attempts, Successes
[Employee Trip Count] - ID, [QI Number[, Date, [Total Calls], [ALS Calls],
[NX Calls]
[qryROADSAFETY] - DATE, [QI NUMBER], MILES, SCORE
[Employee] - QI

In each case, [Date] is a text field, yes?

Anyways, to get the outer joins going, you are going to need more than just
the Employee table, because presumably you want to group the results from the
same Date together as well, right? Do you have a [Date] table that contains
all of the dates? If not, you will need to get the "master list" of Employees
and Dates out of the performance tables themselves. First, you first need to
put them all together in a UNION query:
AllEmployeeDates
SELECT QI, DATE from [EMPLOYEE INTUBATION]
UNION
SELECT [QI Number], [Date] FROM [Employee IV]
UNION
SELECT [QI Number], [Date] FROM [Employee Trip Count]
UNION
SELECT [QI NUMBER], [DATE] FROM qryROADSAFETY

Then you get the unique ones out of this for AllUniqueEmployeeDates:
SELECT QI, DATE
FROM AllEmployeeDates
GROUP BY QI, DATE;

This then forms the basis of your OUTER JOINs to the performance tables for
MeasuresByEmployeeDate, using not just QI Number but Date as well:
SELECT AllUniqueEmployeeDates.QI, AllUniqueEmployeeDates.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 (((AllUniqueEmployeeDates LEFT JOIN [EMPLOYEE INTUBATION] ON
AllUniqueEmployeeDates.QI = [EMPLOYEE INTUBATION].QI) LEFT JOIN [Employee IV]
ON AllUniqueEmployeeDates.QI = [Employee IV].[QI Number]) LEFT JOIN [Employee
Trip Count] ON AllUniqueEmployeeDates.QI = [Employee Trip Count].[QI Number])
LEFT JOIN qryROADSAFETY ON AllUniqueEmployeeDates.QI = qryROADSAFETY.[QI
NUMBER];


--
Message posted via http://www.accessmonster.com

PS. Your conversation is sstill going in one of your other threads:
http://www.accessmonster.com/Uwe/For...essMonster.com


Can you please decide which one you're going to talk about this in and stick
with that one? It would be nice if you could mention it in your duplicate
posts too, so we don't waste time jumping back and forth between them.
Otherwise, people will get annoyed and stop trying to help you...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200509/1

Sorry eveyone, I will stick to the link below.
http://www.accessmonster.com/Uwe/For...essMonster.com