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

 

[Vincent Johns]

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.

Note that (as I mentioned in another posting) you don't need to change
the design of any of your Tables. Just define a new Query that has the
correct number of fields (taken from your Table or defined as an
expression) and use that in the UNION. Also, you don't have to use
every field in the Table.

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.

Yes, even if they have different names, the corresponding fields MUST be
in the same order or you will get garbage. You don't want garbage.

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

HTH,

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

 

[David S via AccessMonster.com]

Unfortunately, Jason's double posting - see this thread here
http://www.accessmonster.com/Uwe/Fo...g-all-records#[email protected]


I've put a reference to this thread in that thread - Jason, please conduct
trhe one conversation in one thread, please - it gets very confusing
otherwise, and if you irritate people too much, they'll stop being so helpful.
...

 

[George Nicholson]

(From the other thread)

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...

David, I couldn't have said it better myself.
Thanks for the multi-posting heads up.

 

[Jason via AccessMonster.com]

Sorry everyone for the mutiple threads and post from differant accounts. I
will avoid that in the future.

 

[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.

 

[George Nicholson]

and none included the employee number

Is EmployeeID the actual name of the field in all tables? The same question
applies to any field name that you aren't Aliasing (e.g., "0 AS MyField").
Aliased fields are the only cases where the field names in your SQL
shouldn't *exactly* match the name of an actual field in that table/query.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

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 P via AccessMonster.com]

George, Thanks I finally got it. I needed to clean up the SQL with my correct
fields but we got there. Thank you everyone.

Here is the SQL that works.

SELECT "IV" As Source,DateID,QI, IVAttempts, IVSuccess, 0 As ETATTEMPTS, 0 as
ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as Miles, 0
as Score From [EMPLOYEE IV]
UNION SELECT "ETT" As Source, DateID, QI, 0 as IVAttempts,
0 as IVSuccess, ETATTEMPTS, ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], 0 as
Miles, 0 as score From [EMPLOYEE INTUBATION]
UNION SELECT "CALLS" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, [Total Calls], [ALS Calls],
[NX Calls], 0 as Miles, 0 as
score From [EMPLOYEE TRIP COUNT]
UNION SELECT "DRIVING" As Source,DateID, QI, 0 as IVAttempts, 0
as IVSuccess, 0 As ETATTEMPTS, 0 as ETSUCCESS, 0 As
[Total Calls], 0 as [ALS Calls], 0 as[NX Calls], Miles, score From
[qryROADSAFETY];

 

[George Nicholson]

yeah, team!!!!