Coulmn prefix does not match with any table name....

[tani1978]

Hello Friends
I have created an Access Frontend SQL Server backend application and one of my report is not working, whose record source is a SQL Server user defined function.When I try to open the report i get the following error

The column prefix 'Table1' does not match with a table name or alias name used in the query.

When I remove the fields related to Table1 from the report then I get the same error about Table2 amd Table3. Here is the sql script generated for the user defined function.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Query1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Query1]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.Query1()
RETURNS TABLE
AS
RETURN ( SELECT     TOP 100 PERCENT dbo.Table1.ID,dbo.Table2.Projekt AS [Table2.Projekt],
 dbo.Table2.AOX AS [Table2.AOX], dbo.Table2.Blei AS [Table2.Blei], dbo.Table2.BTEX AS [Table2.BTEX],
 dbo.Table2.[ph-Zulaufoben] AS Expr10,dbo.Table3.ID,dbo.Table3.Projekt AS [Table3.Projekt]
 dbo.Table2.ortho AS Expr11, dbo.Table2.[absetzbare Stoffe] AS Expr12

FROM         dbo.Table1 INNER JOIN
                   
                      dbo.Table2 ON dbo.Table1.ID = dbo.Table2.ID INNER JOIN
                      dbo.Table3 ON dbo.Table1.ID = dbo.Table3.ID 
ORDER BY dbo.Table1.ID, dbo.Table1.Dat DESC )

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

How I can tackle this problem?

 

[r937]

the function defines a table

this table has the following columns:

ID
[Table2.Projekt]
[Table2.AOX]
[Table2.Blei]
[Table2.BTEX]
Expr10
ID
[Table3.Projekt]
Expr11
Expr12

in other words, unless you build it into the column names (which i would not do with periods), there is no indication of which original table the columns came from

r937.com | rudy.ca

 

[tani1978]

All the coulmn names match with the value refered in the report.But the problem remians

 

[r937]

but you said the report refers to table1 -- there is no column with table1 in it anywhere

r937.com | rudy.ca

 

[tani1978]

I meant I have modified the query and created coulmns of Table1 but it is still not working.

 

[r937]

please show one or two of the columns that the report is referencing, and please show the query again

r937.com | rudy.ca