more than one table listed in from clause of sql

  • Thread starter Thread starter Marlo Clark
  • Start date Start date
M

Marlo Clark

I have a query built and am trying to create a report from that query. The
query runs fine, but the report does not. I get an error that says: "The
specified field 'Category' could refer to more than one table listed in the
FROM clause of your SQL statement. "

Below is the SQL from the query, but I can't seem to find where the error
is. Can someone help me?

Thanks

SELECT [Download Data].Period, [Download Data].Year, Category.Category,
Category.[Category Type], Category.[Unit of Measure], [Material Data].[GRD#],
[Material Data].Description, [PPV Detail by Period].[Std Spend], [PPV Detail
by Period].[Act Spend], [PPV Detail by Period].PPV, [Download Data].[Qty
Received], ([Download Data]![Qty Received]*Category![Volume
Multiplier])/Category![Volume Divisor] AS [Ton Volume], ([Download Data]![Std
Price]/Category![Currency Divisor])*Category![Currency Multiplier] AS [Ton
Std Price], ([Download Data]![Avg Price]/Category![Currency
Divisor])*Category![Currency Multiplier] AS [Ton Act Price], [Ton Std
Price]*[Ton Volume] AS [Ton Std Spend], [Ton Act Price]*[Ton Volume] AS [Ton
Act Spend], ([Ton Act Price]-[Ton Std Price])*[Ton Volume] AS [Ton PPV]
FROM (Category INNER JOIN ([Material Data] INNER JOIN [PPV Detail by Period]
ON [Material Data].[GRD#] = [PPV Detail by Period].[GRD#]) ON
(Category.Category = [Material Data].Category) AND (Category.Category = [PPV
Detail by Period].Category)) INNER JOIN [Download Data] ON [Material
Data].[GRD#] = [Download Data].[GRD#]
WHERE ((([Download Data].Period)=[Enter 2 Digit Period]) AND (([Download
Data].Year)=[Enter 4 Digit Year]))
ORDER BY [Material Data].[GRD#];
 
You have Category.Category and Category![Currency Multiplier] but should
use period instead of exclamation mark. Try changing in all places.
 
I did that but still get the error when running the report.

Here's the new query SQL data.

SELECT [Download Data].Period, [Download Data].Year, Category.Category,
Category.[Category Type], Category.[Unit of Measure], [Material Data].[GRD#],
[Material Data].Description, [PPV Detail by Period].[Std Spend], [PPV Detail
by Period].[Act Spend], [PPV Detail by Period].PPV, [Download Data].[Qty
Received], ([Download Data].[Qty Received]*Category.[Volume
Multiplier])/Category.[Volume Divisor] AS [Ton Volume], ([Download Data].[Std
Price]/Category.[Currency Divisor])*Category.[Currency Multiplier] AS [Ton
Std Price], ([Download Data].[Avg Price]/Category.[Currency
Divisor])*Category![Currency Multiplier] AS [Ton Act Price], [Ton Std
Price]*[Ton Volume] AS [Ton Std Spend], [Ton Act Price]*[Ton Volume] AS [Ton
Act Spend], ([Ton Act Price]-[Ton Std Price])*[Ton Volume] AS [Ton PPV]
FROM (Category INNER JOIN ([Material Data] INNER JOIN [PPV Detail by Period]
ON [Material Data].[GRD#]=[PPV Detail by Period].[GRD#]) ON
(Category.Category=[Material Data].Category) AND (Category.Category=[PPV
Detail by Period].Category)) INNER JOIN [Download Data] ON [Material
Data].[GRD#]=[Download Data].[GRD#]
WHERE ((([Download Data].Period)=[Enter 2 Digit Period]) AND (([Download
Data].Year)=[Enter 4 Digit Year]))
ORDER BY [Material Data].[GRD#];

KARL DEWEY said:
You have Category.Category and Category![Currency Multiplier] but should
use period instead of exclamation mark. Try changing in all places.
--
KARL DEWEY
Build a little - Test a little


Marlo Clark said:
I have a query built and am trying to create a report from that query. The
query runs fine, but the report does not. I get an error that says: "The
specified field 'Category' could refer to more than one table listed in the
FROM clause of your SQL statement. "

Below is the SQL from the query, but I can't seem to find where the error
is. Can someone help me?

Thanks

SELECT [Download Data].Period, [Download Data].Year, Category.Category,
Category.[Category Type], Category.[Unit of Measure], [Material Data].[GRD#],
[Material Data].Description, [PPV Detail by Period].[Std Spend], [PPV Detail
by Period].[Act Spend], [PPV Detail by Period].PPV, [Download Data].[Qty
Received], ([Download Data]![Qty Received]*Category![Volume
Multiplier])/Category![Volume Divisor] AS [Ton Volume], ([Download Data]![Std
Price]/Category![Currency Divisor])*Category![Currency Multiplier] AS [Ton
Std Price], ([Download Data]![Avg Price]/Category![Currency
Divisor])*Category![Currency Multiplier] AS [Ton Act Price], [Ton Std
Price]*[Ton Volume] AS [Ton Std Spend], [Ton Act Price]*[Ton Volume] AS [Ton
Act Spend], ([Ton Act Price]-[Ton Std Price])*[Ton Volume] AS [Ton PPV]
FROM (Category INNER JOIN ([Material Data] INNER JOIN [PPV Detail by Period]
ON [Material Data].[GRD#] = [PPV Detail by Period].[GRD#]) ON
(Category.Category = [Material Data].Category) AND (Category.Category = [PPV
Detail by Period].Category)) INNER JOIN [Download Data] ON [Material
Data].[GRD#] = [Download Data].[GRD#]
WHERE ((([Download Data].Period)=[Enter 2 Digit Period]) AND (([Download
Data].Year)=[Enter 4 Digit Year]))
ORDER BY [Material Data].[GRD#];
Click to expand...
Click to expand...
 
Still one left -- Category![Currency Multiplier]
If that does not fix it try creating a new query and paste your SQL in it.
--
KARL DEWEY
Build a little - Test a little


Marlo Clark said:
I did that but still get the error when running the report.

Here's the new query SQL data.

SELECT [Download Data].Period, [Download Data].Year, Category.Category,
Category.[Category Type], Category.[Unit of Measure], [Material Data].[GRD#],
[Material Data].Description, [PPV Detail by Period].[Std Spend], [PPV Detail
by Period].[Act Spend], [PPV Detail by Period].PPV, [Download Data].[Qty
Received], ([Download Data].[Qty Received]*Category.[Volume
Multiplier])/Category.[Volume Divisor] AS [Ton Volume], ([Download Data].[Std
Price]/Category.[Currency Divisor])*Category.[Currency Multiplier] AS [Ton
Std Price], ([Download Data].[Avg Price]/Category.[Currency
Divisor])*Category![Currency Multiplier] AS [Ton Act Price], [Ton Std
Price]*[Ton Volume] AS [Ton Std Spend], [Ton Act Price]*[Ton Volume] AS [Ton
Act Spend], ([Ton Act Price]-[Ton Std Price])*[Ton Volume] AS [Ton PPV]
FROM (Category INNER JOIN ([Material Data] INNER JOIN [PPV Detail by Period]
ON [Material Data].[GRD#]=[PPV Detail by Period].[GRD#]) ON
(Category.Category=[Material Data].Category) AND (Category.Category=[PPV
Detail by Period].Category)) INNER JOIN [Download Data] ON [Material
Data].[GRD#]=[Download Data].[GRD#]
WHERE ((([Download Data].Period)=[Enter 2 Digit Period]) AND (([Download
Data].Year)=[Enter 4 Digit Year]))
ORDER BY [Material Data].[GRD#];

KARL DEWEY said:
You have Category.Category and Category![Currency Multiplier] but should
use period instead of exclamation mark. Try changing in all places.
--
KARL DEWEY
Build a little - Test a little


Marlo Clark said:
I have a query built and am trying to create a report from that query. The
query runs fine, but the report does not. I get an error that says: "The
specified field 'Category' could refer to more than one table listed in the
FROM clause of your SQL statement. "

Below is the SQL from the query, but I can't seem to find where the error
is. Can someone help me?

Thanks

SELECT [Download Data].Period, [Download Data].Year, Category.Category,
Category.[Category Type], Category.[Unit of Measure], [Material Data].[GRD#],
[Material Data].Description, [PPV Detail by Period].[Std Spend], [PPV Detail
by Period].[Act Spend], [PPV Detail by Period].PPV, [Download Data].[Qty
Received], ([Download Data]![Qty Received]*Category![Volume
Multiplier])/Category![Volume Divisor] AS [Ton Volume], ([Download Data]![Std
Price]/Category![Currency Divisor])*Category![Currency Multiplier] AS [Ton
Std Price], ([Download Data]![Avg Price]/Category![Currency
Divisor])*Category![Currency Multiplier] AS [Ton Act Price], [Ton Std
Price]*[Ton Volume] AS [Ton Std Spend], [Ton Act Price]*[Ton Volume] AS [Ton
Act Spend], ([Ton Act Price]-[Ton Std Price])*[Ton Volume] AS [Ton PPV]
FROM (Category INNER JOIN ([Material Data] INNER JOIN [PPV Detail by Period]
ON [Material Data].[GRD#] = [PPV Detail by Period].[GRD#]) ON
(Category.Category = [Material Data].Category) AND (Category.Category = [PPV
Detail by Period].Category)) INNER JOIN [Download Data] ON [Material
Data].[GRD#] = [Download Data].[GRD#]
WHERE ((([Download Data].Period)=[Enter 2 Digit Period]) AND (([Download
Data].Year)=[Enter 4 Digit Year]))
ORDER BY [Material Data].[GRD#];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top