major query issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone, I am having quite a time trying to create a query that returns
what I am looking for. Here’s the set up

Employee Table
Skill 1
Skill 2
Skill 3
Skill 4

Each of the skill tables lists a skill preformed by each employee for a
period of three month. For ease, each line is dated with the first date of
the quarter i.e. 7/1/2004 then employee number and two to three fields
describing performance with that skill. (basically a count of attempts and
successes) All tables are linked by employee number with a type 2 join. All
records form the employee table and only those form the skills tables where
the fields are equal. My end result is I get repeating records for each
employee and period. Somewhere around 8,000 records when I should only have
about 560. The goal is to take this query and use it as a basis for a summary
query to run reports, broken down by qtr. I should note that if I make the
joins type 1 I only get data on employees that have data in all the tables,
which is not the case. Only a few employees have data in all the tables. What
I want is really to combine all the data into one line for each date, so with
a year’s worth of data I should have four lines per employee. I am also
confused about linking the dates. They are equal in all tables, the only one
s I have are 7/1/2004,10/1/2004,1/1/2005,4/1/2005.

Am I going about this the wrong way, I am self taught and still have a long
way to go. Thank you for any assistance you can provide. Jason
 
Hi Jason, without reading and understanding the exact details, I think you
have a fundamental problem with the skills table.

"Each of the skill tables lists a skill preformed by each employee for a
period of three months"

I have read this to mean your employee table will have a matching record in
4 tables, and I presume the skills tables relate to the quarters.

If so I would tackle this as :
Table Employee: EMP_ID, Name, Family Name, Address etc.
Table Skill: SKILL_ID, SkillName, SkillDescription
Table EmployeeSkill: EMP_ID, SKILL_ID, DateFrom, DateTo. (or even EMP_ID,
SKILL_ID, QUARTER_ID)
 
Each skill has it's own table. Each line is a summary of performance for that
qtr. and is basicly as follows. index,date,employeenumber,skill attempts,
skill success. I would like the results to look like this.
Employeeid,date,skill1 atempts,skill2 success, skill2 attempts, skill2
success, skill3 attempts,skill3 success, skill4 attempt, skill4 successes.
 
Here is the SQL if it helps. Jay

SELECT [Employee.Lname] & ", " & [employee.fname] AS Expr1,
Employee.CertLevel, [Employee IV].Date, [Employee IV].Attempts, [Employee
IV].Successes, [Employee Trip Count].[Total Calls], [Employee Trip
Count].[ALS Calls], [Employee Trip Count].[NX Calls], [Employee RS
Data].Miles, [Employee RS Data].Score, [EMPLOYEE INTUBATION].ETTA, [EMPLOYEE
INTUBATION].ETTS
FROM (((Employee LEFT JOIN [Employee IV] ON Employee.QI = [Employee IV].[QI
Number]) LEFT JOIN [Employee Trip Count] ON Employee.QI = [Employee Trip
Count].[QI Number]) LEFT JOIN [Employee RS Data] ON Employee.[RS ID] =
[Employee RS Data].[Driver ID]) LEFT JOIN [EMPLOYEE INTUBATION] ON
Employee.QI = [EMPLOYEE INTUBATION].QI;
 
Back
Top