-1

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

  • Please post the code or expression that you're using. If you can, the entire SQL statement would be best so others can see the context of your expression. There is no way for anyone to guess what the problem is or suggest a solution without knowing precisely what is currently coded. – C Perkins Oct 03 '18 at 18:51
  • Ok, sorry about that. Please see my edited post. –  Oct 03 '18 at 19:37
  • 2
    But that is only the data, which can useful, but certainly not sufficient. The actual query... the SQL statement... is what determines what data is returned or not. – C Perkins Oct 03 '18 at 19:45
  • WorkDay - Date/Time TransactionID - Text Amount - Double –  Oct 03 '18 at 22:13
  • Your data only shows 2 dates, but what would you expect to be the case for multiple dates? What if there is a missing date, like over a weekend? Do you expect *all previous dates* to be summed up, or literally only the single *"day before"*? Also, are the dates listed as mm/dd/yyyy or dd/mm/yyyy? – C Perkins Oct 04 '18 at 19:27
  • I'm sorry, Date Format is dd/mm/yyyy –  Oct 04 '18 at 20:24

2 Answers2

1

If you are using the GUI add DateAdd("d",-1,[MyDateParameter]) to the OR line under [MyDateParameter] in the Workday field.

For SQL WHERE statement you would use 

WorkDay=[MyDateParameter] OR Workday=DateAdd("d",-1,[MyDateParameter])

Obviously substitute [MyDateParameter] with whatever your date parameter actually is.

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
  • I understand that part but how do I get the query results to include the **Total** of 2/10/18 WorkDay (Opening+Debit-Credit) and the transactions for 3/10/18 WorkDay. –  Oct 03 '18 at 22:18
  • You want to aggregate the previous day records but still display the raw current day records or aggregate those as well? The second option is much simpler. For the first one you will need sub-queries and/or SQL, you can't do it with a single GUI driven query. – SunKnight0 Oct 04 '18 at 15:30
0

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.
C Perkins
  • 3,733
  • 4
  • 23
  • 37
  • Nice. You did it. It needs to be polished a little but that's exactly what I'm looking for. The only problem is that the previous day's figures are not adding up ie. (Opening+Debit-Credit) and on QueryPrevious a parameter box for TransactionID is popping up. Oh and I do take note of the potential problems that can happen like missing dates etc. but baby steps for now. –  Oct 05 '18 at 15:58
  • Notice the possible conflict between the explicit field alias and the table field `TransactionID`. To avoid circular references and invalid parameter prompts, ensure that everything is in order to avoid the conflict. If it's asking for TransactionsID as a parameter, you need to *qualify a reference to the table field* like `TableAlias.TransactionID`, or correct the spelling, or change the explicit value's alias to something else, like `"[Expr]" as TranID`. This will likely fix the summation problem since the Credit's -1 value is dependent upon the TransactionID field. – C Perkins Oct 05 '18 at 16:51