Still can't get query to 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

 

[Ken Snell [MVP]]

And the query's SQL statement that you're trying to use is.....?

 

[me]

Sorry here is the SQL

SELECT DATE.DATE, Employee.QI, [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]
FROM (([DATE] LEFT JOIN [EMPLOYEE INTUBATION] ON DATE.INDEX = [EMPLOYEE
INTUBATION].Date) LEFT JOIN [Employee IV] ON DATE.INDEX = [Employee
IV].Date) LEFT JOIN [Employee Trip Count] ON DATE.INDEX = [Employee Trip
Count].Date, Employee INNER JOIN qryROADSAFETY ON Employee.QI =
qryROADSAFETY.QI;

 

[George Nicholson]

If Attempts, Success, Call Total, NX, Total, ALS Total, Miles Driven,
Driving Score are all numerical fields, you might consider a UNION query
(which will give you 4 lines per employee quarter, if that employee is in
all 4 tables) and then do a totals query off of the UNION to give you "one
line per employee quarter". The following is untested SQL, but may give you
the idea of what I mean:

SELECT "Skill 1" As Source, ID, Date ID, EmployeeID, Attempts, Success, 0 As
[Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as [Miles Driven], 0
as [Driving Score] From [Skill 1 table]
UNION SELECT "Skill 2" As Source, ID, Date ID, EmployeeID, Attempts,
Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as
[Miles Driven], 0 as [Driving Score] From [Skill 2 table]
UNION SELECT "Skill 3" As Source, ID, Date ID, EmployeeID, 0 as Attempts, 0
as Success, [Call Total], NX, Total, [ALS Total], 0 as [Miles Driven], 0 as
[Driving Score] From [Skill 3 table]
UNION SELECT "Skill 4" As Source, ID, Date ID, EmployeeID, 0 as Attempts, 0
as Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], [Miles
Driven], [Driving Score] From [Skill 4 table]

The idea being that each Select "group" has to have the same number of
fields, so plot out the total fields that need to be output and specify
those fields in each group. When a field doesn't actually appear in a
specific table, assign the field an Alias with a specified value of 0 (or
Null). It's up to you to keep the fields in order in your select clause.
Access will work from left to right order regardless of what the fields are
named. It only cares that there are the same number of fields. I believe the
ColumnHeaders that are output are derived from the first Select clause.

The result is a dataset that contains all Skills data. (This dataset may
also resemble what the data structure should have been at the outset <g>).
You can then base a Totals query on that data to get "one line per Employee
qtr"


HTH,

 

[me]

George, thank you for all the effort. You mentioned that all the skill
tables need the same amount of fields. Unfortunitly one of the tables has
one more field than the others.

If Attempts, Success, Call Total, NX, Total, ALS Total, Miles Driven,
Driving Score are all numerical fields, you might consider a UNION query
(which will give you 4 lines per employee quarter, if that employee is in
all 4 tables) and then do a totals query off of the UNION to give you "one
line per employee quarter". The following is untested SQL, but may give
you the idea of what I mean:

SELECT "Skill 1" As Source, ID, Date ID, EmployeeID, Attempts, Success, 0
As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as [Miles
Driven], 0 as [Driving Score] From [Skill 1 table]
UNION SELECT "Skill 2" As Source, ID, Date ID, EmployeeID, Attempts,
Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as
[Miles Driven], 0 as [Driving Score] From [Skill 2 table]
UNION SELECT "Skill 3" As Source, ID, Date ID, EmployeeID, 0 as Attempts,
0 as Success, [Call Total], NX, Total, [ALS Total], 0 as [Miles Driven], 0
as [Driving Score] From [Skill 3 table]
UNION SELECT "Skill 4" As Source, ID, Date ID, EmployeeID, 0 as Attempts,
0 as Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total],
[Miles Driven], [Driving Score] From [Skill 4 table]

The idea being that each Select "group" has to have the same number of
fields, so plot out the total fields that need to be output and specify
those fields in each group. When a field doesn't actually appear in a
specific table, assign the field an Alias with a specified value of 0 (or
Null). It's up to you to keep the fields in order in your select clause.
Access will work from left to right order regardless of what the fields
are named. It only cares that there are the same number of fields. I
believe the ColumnHeaders that are output are derived from the first
Select clause.

The result is a dataset that contains all Skills data. (This dataset may
also resemble what the data structure should have been at the outset <g>).
You can then base a Totals query on that data to get "one line per
Employee qtr"


HTH,
--
George Nicholson

Remove 'Junk' from return address.

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

 

[Ken Snell [MVP]]

I've reread your original post, but I'm not clear what is meant by display
"skill1", "skill2", etc. as single fields.

Using this SQL as a reference, tell us in words the exact table.field
references that you desire to show in a single record for each Date value.

Note that it's not a good idea to use Date as the name of a field. See these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[Vincent Johns]

George, thank you for all the effort. You mentioned that all the skill
tables need the same amount of fields. Unfortunitly one of the tables has
one more field than the others.

It's not terribly unfortunate. Instead of that table, just use a Query
that displays all but 1 of the fields in the table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

[...]

 

[George Nicholson]

You mentioned that all the skill tables need the same amount of fields.

Nope, you misunderstood me.

For UNION queries to work, you need to have the same number of fields for
each set of data you are combining. However, those fields *do not* have to
pre-exist, you can use placeholders in order to make everything "fit" into a
common set of fields.

You listed 4 tables. There are 3 fields in common for all tables (ID, Date
ID, EmployeeID). 2 tables have an additional 2 fields in common (Attempts,
Success). The remaining tables have an additional 2 and 4 unique fields
(Call Total, NX, Total, ALS Total, Miles Driven, Driving Score). I added 1
field (Source) to identify the data source (but that is optional). That's a
total of 12 fields that you will be working with.

Each of the 12 need to be included (preferably in the same order <g>) in
each "group" that you are UNIONing (as shown in my example). Where a field
doesn't actually exist in a specific table, you leave a placeholder, just
like I did with "0 as NX" in the Skills 1 Table section.

UNION queries can't be created in Design View of the query designer. You
have to enter the SQL text in SQL View.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

George, thank you for all the effort. You mentioned that all the skill
tables need the same amount of fields. Unfortunitly one of the tables has
one more field than the others.

If Attempts, Success, Call Total, NX, Total, ALS Total, Miles Driven,
Driving Score are all numerical fields, you might consider a UNION query
(which will give you 4 lines per employee quarter, if that employee is in
all 4 tables) and then do a totals query off of the UNION to give you
"one line per employee quarter". The following is untested SQL, but may
give you the idea of what I mean:

SELECT "Skill 1" As Source, ID, Date ID, EmployeeID, Attempts, Success, 0
As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as [Miles
Driven], 0 as [Driving Score] From [Skill 1 table]
UNION SELECT "Skill 2" As Source, ID, Date ID, EmployeeID, Attempts,
Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as
[Miles Driven], 0 as [Driving Score] From [Skill 2 table]
UNION SELECT "Skill 3" As Source, ID, Date ID, EmployeeID, 0 as Attempts,
0 as Success, [Call Total], NX, Total, [ALS Total], 0 as [Miles Driven],
0 as [Driving Score] From [Skill 3 table]
UNION SELECT "Skill 4" As Source, ID, Date ID, EmployeeID, 0 as Attempts,
0 as Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total],
[Miles Driven], [Driving Score] From [Skill 4 table]

The idea being that each Select "group" has to have the same number of
fields, so plot out the total fields that need to be output and specify
those fields in each group. When a field doesn't actually appear in a
specific table, assign the field an Alias with a specified value of 0 (or
Null). It's up to you to keep the fields in order in your select clause.
Access will work from left to right order regardless of what the fields
are named. It only cares that there are the same number of fields. I
believe the ColumnHeaders that are output are derived from the first
Select clause.

The result is a dataset that contains all Skills data. (This dataset may
also resemble what the data structure should have been at the outset
<g>). You can then base a Totals query on that data to get "one line per
Employee qtr"


HTH,
--
George Nicholson

Remove 'Junk' from return address.

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

 

[Jason via AccessMonster.com]

George,

I took a stab at you Union qry. The SQL is below. Did I get it right? It
returns a lot of records, most with NULL values and none included the
employee number. Thank you to everyone for you patience with me.

SELECT "IV" As Source, ID, [Date ID], EmployeeID, Attempts, Success, 0 As
[Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as [Miles Driven], 0
as [Driving Score] From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, ID, [Date ID], EmployeeID, Attempts,
Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], 0 as
[Miles Driven], 0 as [Driving Score] From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source, ID, [Date ID], EmployeeID, 0 as Attempts, 0
as Success, [Call Total], NX, Total, [ALS Total], 0 as [Miles Driven], 0 as
[Driving Score] From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source, ID, [Date ID], EmployeeID, 0 as Attempts, 0

as Success, 0 As [Call Total], 0 as NX, 0 as Total, 0 as [ALS Total], [Miles
Driven], [Driving Score] From [qryROADSAFETY]

 

[Jason via AccessMonster.com]

David I tried your suggestion from the other post and I also get the syntex
error.