Ms Access query previous day's date

Question

How to bulid a query in Ms Access to include the day before amounts as an opening balance. So on running the query i enter 3/10/18 in the WorkDay parameter box and records for 3/10/18 and 2/10/18 is shown. The Table is setup as follows:

WorkDay....TranactionID....Amount

2/10/18......Opening........1000

2/10/18......Credit.........500

2/10/18.......Debit.........300

3/10/18.......Credit........700

3/10/18.......Debit.........200

So if I run the query for 3/10/18 it should return

WorkDay....TranactionID....Amount

2/10/18......[Expr].........800

3/10/18.......Credit........700

3/10/18.......Debit.........200

Answer 1

First some notes about the request:

1. The desired results imposes different requirements for the current day vs the previous day, so there must be two different queries. If you want them in one result set, you would need to use a UNION.

   • (You could write a single SQL UNION query, but since UNION queries do not work at all with the visual designer, you are left to write and test the query without any advantages of the query Design View. My preference is therefore to create two saved queries instead of embedded subqueries, then create a UNION which combines the results of the saved queries.)

2. Neither the question, nor answers to comments indicate what to do with any exceptions, like missing dates, weekends, etc. The following queries take the "day before" literally without exception.

3. The other difficulty is that the Credit entries also have a positive amount, so you must handle them specially. If Credits were saved with negative values, the summation would be simple and direct.

QueryCurrent:

PARAMETERS [Which WorkDay] DateTime;
SELECT S.WorkDay, S.TransactionID, Sum(S.[Amount]) As Amount
FROM [SomeUnspecifiedTable] As S
WHERE S.WorkDay = [Which WorkDay]
GROUP BY S.WorkDay, S.TransactionID

QueryPrevious:

PARAMETERS [Which WorkDay] DateTime;
SELECT S.WorkDay, "[Expr]" As TransactionID, 
  Sum(IIF(S.TransactionID = "Credit", -1, 1) * S.[Amount]) As Amount
FROM [SomeUnspecifiedTable] As S
WHERE S.WorkDay = ([Which WorkDay] - 1)
GROUP BY S.WorkDay

Union query:

SELECT * FROM QueryCurrent
UNION
SELECT * FROM QueryPrevious
ORDER BY [WorkDay]

Notes about the solution:

• You could also use DateAdd() function, but add/subtracting integers from dates defaults to a change of days.