Again, I am very sorry for being such a noob. I am just trying to get us into a better way of reporting that takes less time. I wish I was a pro but im not.
It took me a while to realize that there was a SQL View in the query. Below is the SQL data for the query I use to create the report.
Code:
SELECT tbl_Accts.AcctName, tbl_Imports.AcctID, tbl_Imports.ReportDate, tbl_Imports.Debit, tbl_Imports.Credit, tbl_Imports.Balance, tbl_Imports.ImportID, tbl_DailyRollupAccts.DailyRollupID, tbl_DailyRollupAccts.DailyRollupAcct
FROM (tbl_Accts INNER JOIN tbl_DailyRollupAccts ON tbl_Accts.DailyRollupID = tbl_DailyRollupAccts.DailyRollupID) INNER JOIN tbl_Imports ON tbl_Accts.AcctID = tbl_Imports.AcctID
WHERE (((tbl_Imports.ReportDate)=[Reporting Date:]) AND ((tbl_Accts.InclDaily)=Yes));
If I understand that write-up you linked correctly, my goal is to edit this query to temporarily create a new field called [PrevBal]. Ive tried 4 times to translate the write-ups table and field names to mine. I am having no luck.
If lets say I did an import today. tbl_Imports.ImportsID is the PK. On record 1, tbl_Accts.AcctName is "Cash", tbl_Imports.ReportDate is "4-8-17", and tbl_Imports.Balance is "1,578,123".
And tomorrow after I import, on record 220, tbl_Accts.AcctName is "Cash", tbl_Imports.ReportDate is "4-9-17", and tbl_Imports.Balance is "1,987,674".
The lines I add in the query should temporarily create [PrevBal] and its value on record 220 would be 1,578,123?
My latest attempt...
Code:
SELECT tbl_Accts.AcctName, tbl_Imports.AcctID, tbl_Imports.ReportDate, tbl_Imports.Debit, tbl_Imports.Credit, tbl_Imports.Balance, tbl_Imports.ImportID, tbl_DailyRollupAccts.DailyRollupID, tbl_DailyRollupAccts.DailyRollupAcct
(SELECT TOP 1 Dupe.Balance
FROM tbl_Imports AS Dupe
WHERE Dupe.AcctsID = tbl_Imports.AcctsID
AND Dupe.ReportDate < tbl_imports.ReportDate
ORDER BY Dupe.ReportDate DESC, Dupe.ImportID) AS PrevBal
FROM (tbl_Accts INNER JOIN tbl_DailyRollupAccts ON tbl_Accts.DailyRollupID = tbl_DailyRollupAccts.DailyRollupID) INNER JOIN tbl_Imports ON tbl_Accts.AcctID = tbl_Imports.AcctID
WHERE (((tbl_Imports.ReportDate)=[Reporting Date:]) AND ((tbl_Accts.InclDaily)=Yes));
When I run the query, its asking for user input on Dupe.AcctsID and tbl_Imports.AcctsID
When I run the report that uses the query, I get a message telling me Multi-level GROUP BY clause is not allowed in a subquery