Still can't make it work

[me]

My query either returns 1.5 million records or it wont run due to the
following error.



"The SQL statement could not be executed due to an ambiguous join."



Here is the set up.



Employee Table

Date Table Contains the following data type Q4 2004, Q1 2005 etc.

Skill 1 table: ID, Date ID, EmployeeID, Attempts, Success

Skill 2 table: ID, Date ID, Employee ID, Attempts, Success

Skill 2 Table: ID, Date ID, Employee ID, Call Total, NX, Total, ALS Total.

Skill 4 Table: ID, Date ID, Employee ID, Miles Driven, Driving Score



The four skill tables have no common data other than dates and employees.



The Date ID links back to the date tbl and the Employee ID links back to the
Employee table. I was wondering what I am doing wrong here. I would like my
end result to be a single line of data for each employee for each date "qtr"
i.e



Employee 1, q2 2005 Skill 1 skill 2 skill 3 etc.

Employee 1, q1 2005 Skill 1, skill 2, skill3

 

[David S via AccessMonster.com]

There are a couple of approaches you can take - my recommended one would be
to first get all of the skills into the one table. Firstly, you'll need
queries to create a Skill field for each type of skill:

[Skill 1 Query]:
SELECT [Skill 1].[Date ID], [Skill 1].EmployeeID, "Skill 1" AS Skill, 1 AS
SkillCount
FROM [Skill 1];

[Skill 2 Query]:
SELECT [Skill 2].[Date ID], [Skill 2].[EmployeeID], "Skill 2" AS Skill, 1 AS
SkillCount
FROM [Skill 2];

(same for [Skill 3 Query] and [Skill 4 Query])

Then, you can use the union statement to get them all into the one query [All
Skills]:
SELECT [Date ID], EmployeeID, Skill, SkillCount
FROM [Skill 1 Query]
union
SELECT [Date ID], EmployeeID, Skill, SkillCount
FROM [Skill 2 Query]
union
SELECT [Date ID], EmployeeID, Skill, SkillCount
FROM [Skill 3 Query]
union
SELECT [Date ID], EmployeeID, Skill, SkillCount
FROM [Skill 4 Query];

You can then join this up with the [Date] table - BTW this is a very bad idea,
since "Date" also describes a data type in SQL - you'd be much better off
calling it something slighlty different, like [DateTable]. Anyway, [All
Skills By Quarter] looks like:
SELECT [All Skills].EmployeeID, DateTable.Quarter, [All Skills].Skill, Sum(
[All Skills].SkillCount) AS SumOfSkillCount
FROM [All Skills] INNER JOIN DateTable ON [All Skills].[Date ID] = DateTable.
[Date ID]
GROUP BY [All Skills].EmployeeID, DateTable.Quarter, [All Skills].Skill;

You can then use the Crosstab query wizard to create a crosstab query like
[All Skills By Query_Crosstab]:
TRANSFORM Avg([SumOfSkillCount])
SELECT [EmployeeID], [Quarter]
FROM [All Skills By Quarter]
GROUP BY [EmployeeID], [Quarter]
PIVOT [Skill];