specified field could refer to more than one table

G

Guest

I've been fiddling with this for a couple days now but can't get it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where the the problem is. Can anyone?

Thanks!
 
G

Guest

Clearly you have two tables with txtProfileID in your query and need to
provide a table name. Try:
SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & tblFGAttributes.[txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));
 
M

Michel Walsh

I don't know what CONCATENATE does, but its argument involves txtProfileID,
specify from which table:

.... WHERE txtProfileID =""" & tblProfiles.[txtProfileID] & """ ORDER BY ...


or


.... WHERE txtProfileID =""" & tblFGAttributes.[txtProfileID] & """ ORDER BY
....



Since they should be equal, by virtue of the inner join ON criteria, it is
probably both of these two formulations will do.


Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Thanks, Duane!

--
www.Marzetti.com


Duane Hookom said:
Clearly you have two tables with txtProfileID in your query and need to
provide a table name. Try:
SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & tblFGAttributes.[txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

--
Duane Hookom
Microsoft Access MVP


JohnLute said:
I've been fiddling with this for a couple days now but can't get it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where the the problem is. Can anyone?

Thanks!
Click to expand...
Click to expand...
 
G

Guest

--
www.Marzetti.com


Marshall Barton said:
JohnLute said:
I've been fiddling with this for a couple days now but can't get it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where the the problem is. Can anyone?
Click to expand...


If a field name exists in two or more tables in the query,
you must qualify the field name with the table name.

In this case, you have a missing quote along with two
references to the field txtProfileID that are ambiguous.

SELECT tblProfiles.txtProfileID,
Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & tblProfiles.txtProfileID
& """"
ORDER BY ProfilesAssociations") AS Drums,
tblFGAttributes.Status
FROM tblProfiles
INNER JOIN tblFGAttributes
ON tblProfiles.txtProfileID=tblFGAttributes.txtProfileID
WHERE tblProfiles.txtProfileID
IN(SELECT tblPKProfilesAssociations.txtProfileID
FROM tblPKProfilesAssociations)
AND tblFGAttributes.Status="Drum"

I don't understand the use of the IN operator in the WHERE
clause. Are you sure that you need it?
Click to expand...
 
G

Guest

Thanks for all of the help. I kept pluggin along and finally got it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations INNER JOIN tblFGAttributes
ON tblPKProfilesAssociations.ProfilesAssociations=
tblFGAttributes.txtProfileID
WHERE tblPKProfilesAssociations.txtProfileID =""" &
[tblProfiles.txtProfileID] & """ and [tblFGAttributes.Status]=""Drum"" ORDER
BY ProfilesAssociations") AS Drums
FROM tblProfiles;

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations INNER JOIN tblFGAttributes
ON tblPKProfilesAssociations.ProfilesAssociations=
tblFGAttributes.txtProfileID
WHERE tblPKProfilesAssociations.txtProfileID =""" &
[tblProfiles.txtProfileID] & """ and [tblFGAttributes.Status]=""Drum"" ORDER
BY ProfilesAssociations")

Crazy but it works.

--
www.Marzetti.com


Marshall Barton said:
JohnLute said:
I've been fiddling with this for a couple days now but can't get it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where the the problem is. Can anyone?
Click to expand...


If a field name exists in two or more tables in the query,
you must qualify the field name with the table name.

In this case, you have a missing quote along with two
references to the field txtProfileID that are ambiguous.

SELECT tblProfiles.txtProfileID,
Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & tblProfiles.txtProfileID
& """"
ORDER BY ProfilesAssociations") AS Drums,
tblFGAttributes.Status
FROM tblProfiles
INNER JOIN tblFGAttributes
ON tblProfiles.txtProfileID=tblFGAttributes.txtProfileID
WHERE tblProfiles.txtProfileID
IN(SELECT tblPKProfilesAssociations.txtProfileID
FROM tblPKProfilesAssociations)
AND tblFGAttributes.Status="Drum"

I don't understand the use of the IN operator in the WHERE
clause. Are you sure that you need it?
Click to expand...
 
M

Marshall Barton

JohnLute said:
I've been fiddling with this for a couple days now but can't get it:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where the the problem is. Can anyone?
Click to expand...


If a field name exists in two or more tables in the query,
you must qualify the field name with the table name.

In this case, you have a missing quote along with two
references to the field txtProfileID that are ambiguous.

SELECT tblProfiles.txtProfileID,
Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & tblProfiles.txtProfileID
& """"
ORDER BY ProfilesAssociations") AS Drums,
tblFGAttributes.Status
FROM tblProfiles
INNER JOIN tblFGAttributes
ON tblProfiles.txtProfileID=tblFGAttributes.txtProfileID
WHERE tblProfiles.txtProfileID
IN(SELECT tblPKProfilesAssociations.txtProfileID
FROM tblPKProfilesAssociations)
AND tblFGAttributes.Status="Drum"

I don't understand the use of the IN operator in the WHERE
clause. Are you sure that you need it?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid SQL Statement - concatenation 3
subreport not displaying fields 5
Help with Delete Coding 1
specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1
subreport not displaying in main report 15
"the specified field could refer to more than one table" error 1
Error: Specified field could refer to more than one table 8
Error: Specified field refers to more than one table 6

Top