major query issues

[Jason]

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

 

[JohnFol]

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)

 

[Jason]

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.

 

[Jason]

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;