Problem running query from front-end.

[ssatech]

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.

 

[mimi2]

I think you should write something like this first:

FROM Companies INNER JOIN Customers
ON Customers.CustomerID = Companies.CompanyID
INNER JOIN Banks
ON Banks.BankID = Companies.BankID
INNER JOIN Receipts
Companies.CompanyID
ON BANK.BankID= Receipts.BankID

 

[SQLSister]

WHen you use an agregate function like sum, then all the other columns in the selct must be in a group by clause of your SQL statement. Look in Books online for group by to see the syntax.

 

[rdroske]

I think it just doesn't like the "10 Receipts" constant in the group clause. Shouldn't need it anyways? Try deleting it?