Previous Day Closing as Opening for the next day. A simple query or report with a cutoffdate to get current balance. Access Database

Angelito Magnaye
Angelito Magnaye used Ask the Experts™
on
Previous Day Closing as Opening for the next day. A simple query or report with a cutoffdate to get current balance. Access Database

Below is my table and expected result for 3 day mix transaction:

TableInitial
Tdate       Trxn           Amt
-----------------------------------------
Day1        Initial           100

TblCStock
Tdate       Trxn         Amt
------------------------------------------
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10


 
Qry/RptTblCStock
Addl row to be added below each nextday 1st  transaction date.
Legend:

1Day =
Result = Initial - Issue + Receive

2Day = Result - Issue + Receive
and next day

Tdate       Trxn         Amt
1Day        Initial        100
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day     Result            125
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day     Result           185
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10
4Day     Result            165

Tnx in adv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
You need a Union query that contains two queries.  The first query sums all transactions PRIOR to the start date and the second selects detail transactions from the start date going forward.  The report needs to break on date and have sums at the end of each group.  Use a running sum to give the ending balance for each period.

Select Forms!yourform!txtStartDate As StartDate, "Initial" As TranType, Sum(Amt) As BegBal
From YourTable Where TranDate < Forms!yourform!txtStartDate
Groub By Forms!yourform!txtStartDate, "Initial"
Union Select TranDate, TranType, Amt
From YourTable Where TranDate > Forms!yourform!txtStartDate;
David BernsteinChief Developer/ Microsoft Access

Commented:
I suggest recording the receipts and initial quantities as positive numbers  and issues as negative numbers
The query is a simple query summing the quantity field and a date parameter to give you a balance as of any date.

SELECT Sum(Table1.amt) AS SumOfamt FROM Table1 WHERE (((Table1.tdate)<=[Enter Cutoff Date]));

Author

Commented:
Many Thanks Mr. Pat and  Mr. David but Im not an expert in Access I just learn by doing self study. I able to folow what Mr. David comment but i need what the format I want in my question, for Mr. Pat comment  I find it very hard to follow could you please elaborate more

Many Thanks again
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
I gave you a sample of what a union query looks like.  Union queries must be created in SQL view so you can't build them using drag and drop in QBE view.

The query is an example but it is complete.  You just have to translate to your table and column names.

Unions essentially stack one query behind another.  Picture a phone book.  Tear three pages from the white pages section.  Lay them down on the table in front of you
page1
page2
page3

Notice that the columns all line up.  Sometimes, as in the example I created for you, not all sets of data have all the same columns.  You can substitute literal values for any missing columns.  So the first query in the union takes a date value from a form control, the literal "initial", and the sum of all the amounts prior to the specified date.
That is what a union does.  It takes multiple sets of data comprised of the SAME columns, in the same order and returns them as a single recordset.

Here is another explanation :
http://www.fmsinc.com/microsoftaccess/query/union-all/index.htm

Author

Commented:
Mr. Pat thanks for the info
Distinguished Expert 2017

Commented:
You're welcome.  If you have your solution, please choose the answer and mark the question closed.

Author

Commented:
Mr. Pat im not yet done with your propose solution, still trying to figure out. Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial