Error FROM clause of your SQL statement

[Stephanie53]

Database52.zip



I went to the SQL Statement and I dont see a FROM that has to do with this field:

SELECT [Purchase Order Details].*, [Quantity]*[Standard Cost] AS [Extended Price], Products.[Product Name]
FROM Products LEFT JOIN [Purchase Order Details] ON Products.ID = [Purchase Order Details].[Product ID];

I have checked both Purchase Order Details and Product tables and I am not seeing where there is an issue.



Attached is the DB for Access 2010 - I can not save it to an earlier version due to other macros and what not that is being used in the rest of the program.

I thank anyone that can help me from the bottom of my heart

Respectfully,

Stephanie

[pbaldy]

The error is uncommonly descriptive and accurate. You have that field in both tables. You need to specify which to use in the calculation.

[Stephanie53]

Ok I am confused - i been told multiple times that when something is related to each other must have the same field names and either both Numeric or Text but not different.

So here I have both with same field names and it only shows coming from one to the other..

Can you tell me by this where I went wrong?

Thanks

Stephanie

[pbaldy]

I could tell from the first post. You have this:

[Quantity]*[Standard Cost] AS [Extended Price]

It doesn't know which table's [Standard Cost] you want used in that calculation.

I'm not sure what you mean by "when something is related to each other must have the same field names and either both Numeric or Text but not different". If you mean the fields in the JOIN, they do need to be the same data type, but they don't have to have the same name (which they don't in your case).

[Stephanie53]

ok under where it says Extended in the section FROM i selected Products - i saved that query - went back to open Purchase Order List Form and i am still getting the same error- so i went back to that query and it shows it not saved - did it again...and still will not save

now how can i get this to save so it reads only from ONE table?

Thanks

Stephanie

[Stephanie53]

Ok the same thing is happening to my following queries...and it has to do with this Standard Price (i included the DB above and nothing has changed since posting that)

Product Sales by Category - I get the enter paramenter Value for Standard Pricing
Product Sales by Total to Date - I get the enter parameter Value for Standard Pricing
Product Details Extended - get the FROM clause of your SQL statement regarding the Standard Pricing
Price Purchase Totals - - get the FROM clause of your SQL statement regarding the Standard Pricing
Purchase Summary - get the FROM clause of your SQL statement regarding the Standard Pricing

the forms that is being affected by all this:
Purchase Order Details
Purchase Order List

On Reports its affecting:
Product Sales by Category
Product Sales by Revenue
now Product details is not having this problem as the control for this is from Products which the others should be too...corrrect?

HELP :-)

this is holding me up big time..ughhhh

TIA

Stephanie

[pbaldy]

Without seeing the SQL of the query I would only be guessing. You have to add the table name here though:

[Quantity]*TableName.[Standard Cost] AS [Extended Price]

[Stephanie53]

WOW...is that all it took? lol

Thank youi so much again!! :-)

IT WORKS! :-)

Stephanie

[pbaldy]

No problemo.