using query in a query

[Guest]

I think this is a beginner question. I am modifing a query. And I have an
another query which I think I can use, so I made this query in the query
design window pane link to a table? I tried it but I got 5238 error.

For example:
Showdata query read from employee table, and from the employee data, there
is a supervisor ID belong each employee data. And I have a supervisor query
which collected from employee data's jobtitle. Can I make showdata query's
employee table(supervisorID) link to supervisor query to get the supervisor
name?

Thank you very much.

 

[Guest]

There shouldn't be any problem ctreating a query that link a query to a table.
What is the error you are getting?
Do you get error in all of the records?
Is the field type in the two tables identical?
Make sure that you linked the right fields.

 

[Guest]

I have a Select Query, it linked to table 1, table 2, and Employee table. Now
I try to add a Supervisor Query(which made from Employee table, too) to
Employee table, which linked by Employee ID, so I can get the supervisor's
name.
When I run it, Access gave me this error.

The SQL statement could not be executed because it contains ambiguous outer
joins. TO force one of the joins to be performed first, create a separater
query that performs the first join and then include that query in your SQL
statement. -3258

 

[Vincent Johns]

I have a Select Query, it linked to table 1, table 2, and Employee table. Now
I try to add a Supervisor Query(which made from Employee table, too) to
Employee table, which linked by Employee ID, so I can get the supervisor's
name.
When I run it, Access gave me this error.

The SQL statement could not be executed because it contains ambiguous outer
joins. TO force one of the joins to be performed first, create a separater
query that performs the first join and then include that query in your SQL
statement. -3258

Instead of splitting your Query into two parts, you may just want to
cascade the outer joins. If [table 1] is linked to [table 2] with an
outer join, then you might want to link [table 2] to [Employee] the same
way -- with the same kind of outer join. (You'll need to decide if
doing this makes sense, considering what you're trying to get your Query
to do.)

It's OK to use the same Table twice (or more) in one Query; Access will
give each instance a unique name, and you can rename them if you wish in
Query Design View. (You could instead rename them in the SQL view, but
that can be tedious, as the name will appear more than once there.)

You may want to post the SQL of your Query here, to give us a better
idea of what you tried to do.

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

 

[Guest]

I have a Select Query, it linked to table 1, table 2, and Employee table. Now
I try to add a Supervisor Query(which made from Employee table, too) to
Employee table, which linked by Employee ID, so I can get the supervisor's
name.
When I run it, Access gave me this error.
The SQL statement could not be executed because it contains ambiguous outer
joins. TO force one of the joins to be performed first, create a separater
query that performs the first join and then include that query in your SQL
statement. -3258

Instead of splitting your Query into two parts, you may just want to
cascade the outer joins. If [table 1] is linked to [table 2] with an
outer join, then you might want to link [table 2] to [Employee] the same
way -- with the same kind of outer join. (You'll need to decide if
doing this makes sense, considering what you're trying to get your Query
to do.)
It's OK to use the same Table twice (or more) in one Query; Access will
give each instance a unique name, and you can rename them if you wish in
Query Design View. (You could instead rename them in the SQL view, but
that can be tedious, as the name will appear more than once there.)
You may want to post the SQL of your Query here, to give us a better
idea of what you tried to do.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Let me try this way. Table 1 link to Table 2, Table 2 link to Employee
table. From the Employee table, I can get SupervisorID(number value). Now, I
would like to get the Supervisor name. How should I do? I try to make
SupervisorID link to Employee_1 table, but Access give me the error code
3258.

Thank you.

 

[Vincent Johns]

I have a Select Query, it linked to table 1, table 2, and Employee table. Now
I try to add a Supervisor Query(which made from Employee table, too) to
Employee table, which linked by Employee ID, so I can get the supervisor's
name.
When I run it, Access gave me this error.
The SQL statement could not be executed because it contains ambiguous outer
joins. TO force one of the joins to be performed first, create a separater
query that performs the first join and then include that query in your SQL
statement. -3258

Instead of splitting your Query into two parts, you may just want to
cascade the outer joins. If [table 1] is linked to [table 2] with an
outer join, then you might want to link [table 2] to [Employee] the same
way -- with the same kind of outer join. (You'll need to decide if
doing this makes sense, considering what you're trying to get your Query
to do.)
It's OK to use the same Table twice (or more) in one Query; Access will
give each instance a unique name, and you can rename them if you wish in
Query Design View. (You could instead rename them in the SQL view, but
that can be tedious, as the name will appear more than once there.)
You may want to post the SQL of your Query here, to give us a better
idea of what you tried to do.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Let me try this way. Table 1 link to Table 2, Table 2 link to Employee
table. From the Employee table, I can get SupervisorID(number value). Now, I
would like to get the Supervisor name. How should I do? I try to make
SupervisorID link to Employee_1 table, but Access give me the error code
3258.

Thank you.

What does your SQL look like?

In Query Design View, I think you want all the "outer join" arrows to
point the same direction. (Which direction that is, LEFT JOIN or RIGHT
JOIN, depends on what you want the Query to do. I don't know what's in
your Tables.)

Or you can change all of the relationships to INNER JOIN types, which
should make the message go away, but you might not see many data when
you run the Query. Maybe none at all.

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

 

[Guest]

What does your SQL look like?
In Query Design View, I think you want all the "outer join" arrows to
point the same direction. (Which direction that is, LEFT JOIN or RIGHT
JOIN, depends on what you want the Query to do. I don't know what's in
your Tables.)
Or you can change all of the relationships to INNER JOIN types, which
should make the message go away, but you might not see many data when
you run the Query. Maybe none at all.

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

Now it looks like this:
SELECT Services.Customer, Services.Date, Services.Type, Services.Seller,
Nz(Trim([LastName]))+", "+Nz(Trim([FirstName])) AS SellerName,
Services.Location, Employee.Region, Employee.Supervisor
FROM (Employee LEFT JOIN [Supervisor Query] ON Employee.Supervisor =
[Supervisor Query].EmployeeID) RIGHT JOIN (Mill RIGHT JOIN Services ON
Customer.Customer = Services.Customer) ON Employee.EmployeeID =
Customer.AssignedSeller
GROUP BY Services.Customer, Services.Date, Services.Type, Services.Seller,
Nz(Trim([LastName]))+", "+Nz(Trim([FirstName])), Services.Location,
Employee.Region, Employee.Supervisor, Format([Date],"yyyy")
HAVING (((Services.Type)<>"DEST" And (Services.Type)<>"FP") AND
((Employee.Region) Between [Forms]![Monthly Customer Visits by
Seller]![BegRegion] And [Forms]![Monthly Customer Visits by
Seller]![EndRegion]) AND ((Format([Date],"yyyy"))=[Forms]![Monthly Customer
Visits by Seller]![SelectYear]));

When I run it, Access said "The specified field "[LastName]" could refer to
more than one table listed in the FROM clause of your SQL statement. -3079"

Because 2 tables with same field names?
Thank you.

 

[John Spencer]

Pardon me for jumping in.

The error means that you do have two fields with the same name, so you will
have to specify which table to use. Try changing
Nz(Trim([LastName]))+", "+Nz(Trim([FirstName])) AS SellerName to

Nz(Trim([Employee].[LastName]))+", "+Nz(Trim([Employee].[FirstName])) AS
SellerName

I am guessing that you need information from the EMPLOYEE table, if you need
the information from one of the other tables or queries, then substitute
that name. Don't forget to do this in all the places in your statement

What does your SQL look like?
In Query Design View, I think you want all the "outer join" arrows to
point the same direction. (Which direction that is, LEFT JOIN or RIGHT
JOIN, depends on what you want the Query to do. I don't know what's in
your Tables.)
Or you can change all of the relationships to INNER JOIN types, which
should make the message go away, but you might not see many data when
you run the Query. Maybe none at all.

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

Now it looks like this:
SELECT Services.Customer, Services.Date, Services.Type, Services.Seller,
Nz(Trim([LastName]))+", "+Nz(Trim([FirstName])) AS SellerName,
Services.Location, Employee.Region, Employee.Supervisor
FROM (Employee LEFT JOIN [Supervisor Query] ON Employee.Supervisor =
[Supervisor Query].EmployeeID) RIGHT JOIN (Mill RIGHT JOIN Services ON
Customer.Customer = Services.Customer) ON Employee.EmployeeID =
Customer.AssignedSeller
GROUP BY Services.Customer, Services.Date, Services.Type, Services.Seller,
Nz(Trim([LastName]))+", "+Nz(Trim([FirstName])), Services.Location,
Employee.Region, Employee.Supervisor, Format([Date],"yyyy")
HAVING (((Services.Type)<>"DEST" And (Services.Type)<>"FP") AND
((Employee.Region) Between [Forms]![Monthly Customer Visits by
Seller]![BegRegion] And [Forms]![Monthly Customer Visits by
Seller]![EndRegion]) AND ((Format([Date],"yyyy"))=[Forms]![Monthly
Customer
Visits by Seller]![SelectYear]));

When I run it, Access said "The specified field "[LastName]" could refer
to
more than one table listed in the FROM clause of your SQL
statement. -3079"

Because 2 tables with same field names?
Thank you.