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!
 
Ad

Advertisements

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!
 
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?

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

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?
 
Ad

Advertisements

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?

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

Top