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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steel Banana - 06 May 2008 18:08 GMT

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!

Signature

Chris

Reply to this Message

Tom Wickerath - 06 May 2008 18:16 GMT

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
[quoted text clipped - 16 lines]
> here?
> Thanks for any help!

Reply to this Message

Golfinray - 06 May 2008 18:17 GMT

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.

Reply to this Message

Jeff Boyce - 06 May 2008 18:18 GMT

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

>I have a query (qryPortlandData) with one table (tblPortlandData) that
>works
[quoted text clipped - 21 lines]
> here?
> Thanks for any help!

Reply to this Message