Specified field could refer to more than one table listed in the FROM clause of your

[weswilson88]

<sup>"Specified field ;[WSO data],Counterparty could refer to more than one table listed in the FROM clause of your SQL Statement." is the error I am getting when i try to run a report.



From what I see, both my BMS and WSO data table have the same field name Counterparty yet they are both specifically referenced below. I cannot change either field name.

I created the report from report wizard from a query. The SQL for the query is:</sup>

SELECT [BMS Trades].[Today's Date], [BMS Trades].[Allocation ID], [BMS Trades].[LoanX ID], [WSO data].Asset_LOANXAssetID_Name, [BMS Trades].[Trade Date], [WSO data].Trade_TradeDate, Round([Price],3) AS Expr1, Round([Trade_Price],3) AS Expr2, [BMS Trades].Portfolio, [Portfolio Match].BMS, [BMS Trades].[Par Amount], Abs([Trade_ParAmount]) AS Expr4, [BMS Trades].[Total Proceeds], Round(Abs([WSO data]![Trade_Amount]),2) AS Expr3, [BMS Trades].Counterparty, [WSO data].CounterParty, [BMS Trades].[Transaction Type], [WSO data].TradeTypeDescription, [BMS Trades].[Settlement Date], [BMS Trades].[Asset Type], [WSO data].Trade_Settled
FROM [Portfolio Match] RIGHT JOIN ([WSO data] RIGHT JOIN [BMS Trades] ON [WSO data].Trade_Name = [BMS Trades].[Allocation ID]) ON [Portfolio Match].Trade_Name = [BMS Trades].[Allocation ID]
WHERE ((([WSO data].Asset_LOANXAssetID_Name)<>[BMS Trades].[LoanX ID]) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or ((([BMS Trades].[Trade Date])<>[WSO data].Trade_TradeDate) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or (((Round([Price],3))<>Round([WSO data].Trade_Price,3)) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or ((([BMS Trades].Portfolio)<>[Portfolio Match].BMS) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or (((Round(Abs([WSO data]!Trade_Amount),2))<>[BMS Trades].[Total Proceeds]) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or ((([BMS Trades].Counterparty)<>[WSO data].CounterParty) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or ((([BMS Trades].[Transaction Type])<>[WSO data].TradeTypeDescription) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0)) Or ((([BMS Trades].[Par Amount])<>Round(Abs(([WSO data].Trade_ParAmount)),2)) And (([BMS Trades].[Settlement Date]) Is Null) And (([BMS Trades].[Asset Type])="Loan") And (([WSO data].Trade_Settled)=0));


Any help is appreciated.

[June7]

I can't see anything definitely wrong but suspect something about the table joins is causing confusion.

If you want to provide db for analysis, follow instructions at bottom of my post.