RunningTotal or Closing balance Issue ( already tried posted solutions )

[darshit_goswami]

Hi friends,

This is my first post over this forum. I'm a newbie to access world. I m actually trying to make a little database utility to maintain store balance in my school.

Everything in layout looks great but i m stuck with a problem which i m not able to solve. So here is the problem, i wish u guys will help me sort it out.

I kept Access2003 format so that it could run in almost all the pcs.

Here i m not able to get running total / closing balance in tblRegister.

here r the attached SS.

Table Designs : tbl_in and tbl_issue

table sample data :

Make Table query :

SELECT tbl_issue.srNo, tbl_issue.CrNo, tbl_issue.gprNo, tbl_in.itemName, tbl_in.inQty, tbl_in.inDate, tbl_issue.issueQty, tbl_issue.issueDate, tbl_issue.issuedTo, [inQty]-[issueQty] AS closingBal INTO tblRegister
FROM tbl_in INNER JOIN tbl_issue ON tbl_in.gprNo = tbl_issue.gprNo;

tblRegister ( Which I m getting ) Wrone One...


Continue in next post...

[darshit_goswami]

tblRegister ( Desired one, which i want to get )



Here is that sample mdb file.db1.mdb

One Item in tbl_in can be issued to many person with different qty in tbl_issue. All I need is , everytime when an item is issued , it should be deducted from inQty and get a closing balance , and when same item issued again , issueQty should be deducted from closing balance.

I hope you got my point.


hope to get some solution..

If there is any confusion regarding this post. Kindly ask. I can try to explain to my best.

Thanks in advance.

Darshit

[lfpm062010]

The queries works. db1.zip
It depends on the field "srNo" on table "table_issue" to be unique (which it should be since it is an autonumber and not to have any duplicate).

Hope this works for you.

I'm sure there are other ways to do the same.

[darshit_goswami]

thanks ifpm

This seems to work on this db. Will implement your given suggestions on main database and will report.

Thanks for the help ,..

Regards,
Darshit

[darshit_goswami]

Bump......

First of all apologies if I'm violating any forum rule here by bumping the thread. But the question is related to this and instead of starting a new thread i think its better to be asked here only.

The solution posted above works perfectly. But now my storekeeper asks me to get list of all the records where ClosingBalance > 0 after item issue process is done.

so the basic idea is, get a gprNo , loop through all records in issuetable with same gprNo. Find last record, get Closingbalance , if its > 0 then display it else move to next gprNo.

I dont know how to do it... Any help ?

[June7]

So you need to know which grpNo's have a 0 balance? Don't need the running balance for that. Total inQty minus total issueQty should accomplish. Do an aggregate query for each table then join those queries in another query.

tblIn shows only one record for each grpNo - will there ever be multiple records for each grpNo?

[darshit_goswami]

thanks june for heads up ,..

I think I'm dumb.. Will try your given solution,...

gprNo is unique cant be duplicated ever,...

CrNo is unique too but once the physical register is filled , it can again have same crNo.. but that aint going to happen for next few years and i kept enough place in the field so that in future it can hold numbers for next 9 physical registers to be filled up,.. it will take more then 50 years at current space..

I m actually developing a small utility for my institute, and it will be free for use for several institutes who fall in same category

thanks will report u back quickly with inQty - sum (issueQty) formula.

[darshit_goswami]

worked ,..

SELECT qryCR1.gprNo, qryCR1.itemName, qryCR1.itemQty, Sum(qryCR1.outQty) AS SumOfoutQty, [qryCR1]![itemQty]-Sum([qryCR1]![outQty]) AS leftBh
FROM qryCR1
GROUP BY qryCR1.gprNo, qryCR1.itemName, qryCR1.itemQty
HAVING ((([qryCR1]![itemQty]-Sum([qryCR1]![outQty]))<>0));

where qryCR1 is

SELECT tblCR.srID, tblCR.crNo, tblCR.gprNo, tblGPR.itemName, tblGPR.itemQty, tblItems.itemUnit, tblGPR.inwardDate, tblCR.outDate, tblCR.outQty, tblCR.indentNo, tblCR.indentDate, tblCR.instName, tblInstructors.tradesAssigned
FROM tblItems INNER JOIN (tblInstructors INNER JOIN (tblGPR INNER JOIN tblCR ON tblGPR.[gprNo] = tblCR.[gprNo]) ON tblInstructors.[instID] = tblCR.[instName]) ON tblItems.itemCode = tblGPR.itemName
ORDER BY tblCR.srID, tblCR.gprNo;

qryCR1 may be overkill here but I made that query earlier for some other work ,.. like getting closing balance, and generate report ,.. i picked up fields from there ,..

I m sure there will be many other ways ... but this one worked for me ,..

Table names are not as same as posted in db above but most of them can be understood.

Thanks ,.. marked as solved,..