Brian:
The following Sql statement works.
Are you sure ? ... the
WHERE clause does not appear to have the proper syntax ... try ...
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#) AND (AccountNo >= [#34]4000[#34])
... and if
AccountNo is not a text/string field ...
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#) AND (AccountNo >= 4000)
I only want to 'Group By' AccountNo, but I get an error.
Post the exact command ... and the error message/text.
Might be a good idea to place a
semicolon at the end of the SQL command.
If all else fails, try one (itty bitty) step at a time e.g. ...
INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails;
... and then ...
INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
GROUP BY AccountNo;
... and then ...
INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#)
GROUP BY AccountNo;
... and finally ...
INSERT INTO IncomeExpenseTbl
(AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails
WHERE (JrDetailsDate BETWEEN #[FromDate]# AND #[ToDate]#)
AND (AccountNo >= 4000)
GROUP BY AccountNo;
See where it balks.