If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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]); |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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); |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Sorry eveyone, I will stick to the link below.
http://www.accessmonster.com/Uwe/For...essMonster.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
query returning either nothing or multiples of records | Christopher W via AccessMonster.com | Running & Setting Up Queries | 16 | August 19th, 2005 08:25 AM |
Appending ONLY new records to a table | Ofer | Running & Setting Up Queries | 0 | April 27th, 2005 11:13 PM |
count number of records | Joe_Access | General Discussion | 1 | January 13th, 2005 06:27 PM |
Union Query returning Duplicate Records | Mike_Walrus | Running & Setting Up Queries | 1 | August 19th, 2004 09:40 PM |
append Query duplicating records | Alex | Running & Setting Up Queries | 1 | July 8th, 2004 01:31 PM |