Query problem?! (error 3079, FROM clause no good

[Steel Banana]

I have a query (qryPortlandData) with one table (tblPortlandData) that works
fine. I'm trying to add another table (tblToteParts) with one common field
(PartNumber) between the two tables...but I keep getting the error 3079 "The
specified field <field> could refer to more than one table listed in the FROM
clause of your SQL statement."

SQL:
SELECT "J:\Inventory\Product Photos\ClassA\" & [PartNumber] & ".jpg" AS
PartPicture, tblPortLandData.Location, tblPortLandData.PartNumber,
tblPortLandData.Description, tblPortLandData.Revision,
tblPortLandData.QtyOnOrder, tblPortLandData.QtyOnHand,
tblPortLandData.SalesOrder,
tblPortLandData.LineNumber
FROM tblPortLandData INNER JOIN tblToteParts ON tblPortLandData.PartNumber =
tblToteParts.PartNumber;

This is really bugging me as I've done simple queries like this. The error
is apparently in the FROM clause, but it looks fine to me. What am I missing
here?
Thanks for any help!

 

[Tom Wickerath]

Hi Chris,

I think Access is confused by the initial part of your SELECT statement:

SELECT "J:\Inventory\Product Photos\ClassA\" & [PartNumber] & ".jpg" AS
PartPicture

Which [PartNumber] field should it use....the one from tblPortLandData or
the one from tblToteParts? Try this instead:

SELECT "J:\Inventory\Product Photos\ClassA\" & [tblPortLandData.PartNumber]
& ".jpg" AS PartPicture

or

SELECT "J:\Inventory\Product Photos\ClassA\" & [tblToteParts.PartNumber] &
".jpg" AS PartPicture


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a query (qryPortlandData) with one table (tblPortlandData) that works
fine. I'm trying to add another table (tblToteParts) with one common field
(PartNumber) between the two tables...but I keep getting the error 3079 "The
specified field <field> could refer to more than one table listed in the FROM
clause of your SQL statement."

SQL:
SELECT "J:\Inventory\Product Photos\ClassA\" & [PartNumber] & ".jpg" AS
PartPicture, tblPortLandData.Location, tblPortLandData.PartNumber,
tblPortLandData.Description, tblPortLandData.Revision,
tblPortLandData.QtyOnOrder, tblPortLandData.QtyOnHand,
tblPortLandData.SalesOrder,
tblPortLandData.LineNumber
FROM tblPortLandData INNER JOIN tblToteParts ON tblPortLandData.PartNumber =
tblToteParts.PartNumber;

This is really bugging me as I've done simple queries like this. The error
is apparently in the FROM clause, but it looks fine to me. What am I missing
here?
Thanks for any help!

 

[Golfinray]

Usually, anytime you get that error the problem is that Jet does not know
which table you want to use. Make sure that table is defined and make sure it
has a unique name and a primary key.

 

[Jeff Boyce]

If both tables have a field named [PartNumber], which one are you referring
to in your SELECT statement with the "& [PartNumber] &" expression?

By the way, I noticed that you have a table you've named "tblPortlandData".
This implies that you have other tables you've also named in a way that
captures data in the title of the table (I don't know, maybe
"tblSeattleData"). If your tables are structured this way, you have a
spreadsheet, not a relational database.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP