We recently upgraded our back-end from Access 97 to SQL server 2000. But still have Access 97 as front-end. We run a report called "Trial Balance". But one of the query behind this report generates an error: "[Microsoft][ODBC SQL Server Driver][SQL Server]GROUP BY expressions must refer to column names in the select list.(#164)"
The query reads like this:
INSERT INTO [Collate TB by TX and TX] ( CompanyID, Name, SumOfTX, SumOfEuroTX, TransTypeID )
SELECT DISTINCTROW Receipts.CompanyID, Companies.Name, Sum(Receipts.Amount) AS SumOfAmount, Sum(Receipts.EuroAmount) AS SumOfEuroAmount, "10 Receipts" AS TransTypeID
FROM Companies INNER JOIN (Customers INNER JOIN (Banks INNER JOIN Receipts ON Banks.BankID = Receipts.BankID) ON Customers.CustomerID = Receipts.CustomerID) ON Companies.CompanyID = Receipts.CompanyID
WHERE (((Receipts.ReceiptDate) Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]))
GROUP BY Receipts.CompanyID, Companies.Name, "10 Receipts";
I am not expert in writing queries or SQL, but could someone show me where the error is being generated? or what the problem might be? Thanks.
The query reads like this:
INSERT INTO [Collate TB by TX and TX] ( CompanyID, Name, SumOfTX, SumOfEuroTX, TransTypeID )
SELECT DISTINCTROW Receipts.CompanyID, Companies.Name, Sum(Receipts.Amount) AS SumOfAmount, Sum(Receipts.EuroAmount) AS SumOfEuroAmount, "10 Receipts" AS TransTypeID
FROM Companies INNER JOIN (Customers INNER JOIN (Banks INNER JOIN Receipts ON Banks.BankID = Receipts.BankID) ON Customers.CustomerID = Receipts.CustomerID) ON Companies.CompanyID = Receipts.CompanyID
WHERE (((Receipts.ReceiptDate) Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]))
GROUP BY Receipts.CompanyID, Companies.Name, "10 Receipts";
I am not expert in writing queries or SQL, but could someone show me where the error is being generated? or what the problem might be? Thanks.