The specified field 'Ledger.ID' could refer to more than one table listed in the FROM clause of your SQL statement

Question

I am not new in access but not even pro. I really need help with this problem.

Key: Ledger - Main Table; Customer - Customer Table.

My Goal: I am using access for accounting purpose, I want to show customer balance in daily accounting sheet.

What have I done

First I made a query (DailySheetQuery) out of Ledger and Customer which gives total of balances of each customer

Second I took Ledger again and DailySheetQuery to a new query (DailySheet).

Third I linked the customer foreign key in ledger to DailySheetQuery ID (which is customer ID originally) to link the balance in DailySheetQuery to the customer in ledger

Now the problem - DailySheet (Query) doesn't show any error - but when I bring it to report it shows this error "The specified field 'Ledger.ID' could refer to more than one table listed in the FROM clause of your SQL statement."

My Codes: Query 1: DailySheetQuery

SELECT [Customer Database].ID, Ledger.Customer, Sum([Recable]-[Received]) AS Balance
FROM [Customer Database] RIGHT JOIN Ledger ON [Customer Database].ID = Ledger.Customer
GROUP BY [Customer Database].ID, Ledger.Customer;

Query 2: DailySheet

SELECT Ledger.ID, Ledger.[Transaction Type], Ledger.[Invoice Date], Ledger.Passenger, Ledger.Customer, Ledger.Employee, Ledger.Service, Ledger.Recable, Ledger.Received, Ledger.Payable, Ledger.Paid, Ledger.[Mode Of Payment], DailySheetQuery.ID, DailySheetQuery.Balance, IIf([Ledger].[Mode Of Payment]=2,[Ledger].[Received]-[Ledger].[Paid],0) AS CashInHand, [Ledger].[Recable]-[Ledger].[Payable] AS Profit, [Ledger].[Received]-[Ledger].[Paid] AS [Money]
FROM Ledger LEFT JOIN DailySheetQuery ON Ledger.Customer = DailySheetQuery.ID
WHERE (((Ledger.[Invoice Date])=Date()) AND ((Ledger.Employee)=[Employee #?]));

Please go to through my problem and send help.

---

P.S. I am new to forum question and answer, if there is something missing let me know.

Answer 1

You could alias Ledger in the query:

SELECT 
    L.ID, L.[Transaction Type], .. , [L].[Received]-[L].[Paid] AS [Money]
FROM 
    Ledger AS L
LEFT JOIN 
    DailySheetQuery ON L.Customer = DailySheetQuery.ID
WHERE
    (((L.[Invoice Date])=Date()) AND ((L.Employee)=[Employee #?]));