Making 1 Field = Another Specific Field

[Richard Anderson]

Hello,

I have a task of creating a simple database to report daily numbers to HQ.

SITUATION:
Table is tbl_Imports. Field in this are [ImportID](key), [AcctsID](foreign key to annother table), [ReportDate], [Debit], [Credit], [Balance](Credit-Debit).
Everyday we import 219 records that include [AcctID], [ReportDate], [Debit], and [Credit].
I want to add another field called [PrevBal] that will automatically equal the balance of that same [AcctID] from the previous import.

EXAMPLE:
We do an import on 8-4-17. The [ReportDate] field for all 219 records will be the same(8-3-17, the days business we are reporting), The [AcctID] fields will be 1 through 219. Every Record has its own [Balance] calculated by [Credit]-[Debit]. Since this would be the first Import I would manually fill in the [PrevBal].
Then we do an import on 8-7-17 with a [ReportDate] on all 219 records being 8-4-17(Previous Workday) and [AcctID] fields being 1-219 once again. Again the [Balance] field will make its calculation. Since this is not the first import, I need to make the [PrevBal] Field equal the [Balance] field for each [AcctID] from the previous import.

ImportID	ACCTID	ReportDate	Debit	Credit	Balance	PrevBal
1	1	8-3-17	100	0	-100	-75
220	1	8-4-17	130	0	-130	-100	[PrevBal] Should Pull from [Balance] on [ImportID] 1
439	1	8-7-17	165	0	-165	-130	[PrevBal] Should Pull from [Balance] on [ImportID] 220. Notice 3 day gap not just 1 (weekend)

My first thought was to put a calculation into [PrevBal] just like I did [Balance]. Something like =[Balance] Where [AcctID] matches and [ReportDate] is previous report date (which is not just the day before). Or could I do =[Balance] Where [ImportID] = current [ImportID]-219. The problem here would be if we add any accounts in the future making more than 219 [AcctID]s.

[June7]

Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[Richard Anderson]

Im not understanding. I need the value of [PrevBal] in a new record to automatically record the value of [Balance] from the the most recent record in the same table that has the same [AcctID]. Excuse my noobness, but I dont see how this is done by query. In my understanding of a query, all they do is filter the data you want to see from a table... I am trying to get permanent data into the table.

[JoeM]

In my understanding of a query, all they do is filter the data you want to see from a table

That is incorrect (and a drastic oversimplification of what queries can do). ALL calculations should be done at the query level (they should NOT be done at the Table level).
The link June provided shows how to do the calculation you are looking for.

Trying to do calculations at the Table level violates the rules of data normalization, which says that field values should not be dependent upon other fields or records.
Doing so can undermine the dynamic nature of the database and cause data integrity issues.

Have a look here for more information on many different things you can do with queries: https://support.office.com/en-us/art...7-785a01357516

[June7]

Queries don't just filter - they can sort, group, summarize, rearrange. And with a nested subquery, pull value from another record in same table - which is what you are asking for and the referenced article demonstrates.

Saving this value into table is not really necessary and not simple. Also, saving data that is dependent on other data (another field) is usually a bad idea.

[Richard Anderson]

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.

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...

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

[June7]

When I run the query, its asking for user input on Dupe.AcctsID and tbl_Imports.AcctsID

Isn't the fieldname AcctID - without the 's'?

[Richard Anderson]

Thank you, The query now creates the PreVal field and pulls the correct data.

However, How do I handle the "Multi-Level GROUP BY clause is not allowed in a subquery" error?

The report that I made is based off this query which uses data from 3 tables.

EDIT: I tried to recreate the report from scratch and it worked all the way up to the point I Added a group under Group&Sort. I need to do this so I can give subtotals for each rollup.

EDIT2: I created another query and made it only source the query with the subquery. I then made the source of the report this newly created query. Everything works until I create the group in the report to group by. When I do, The PrevBal field on all records are blank.

[June7]

Instead of subquery to pull previous balance, consider doing a running sum calculation on report. Textbox on report has a RunningSum property.

The real trick is figuring out the starting balance when report is restricted to a particular time period. Usual technique is a textbox with a DSum() expression that would sum records up to a designated date. Here is one discussion https://www.accessforums.net/showthread.php?t=53779