HII,

someone please help me on my report
there is one employee table consisting of fields
ID --> primary key
NAME

second table consists of field

EMPID
DEBIT
CREDIT
MONTH format mm/yyyy

the two are linked with ID relationship is one to many



1. CRITERIA IS PASSED FROM A FORM
2. OPENING BALANCE FOR ID IS ARRIVED FROM ANOTHER QUERY LESS THAN THE CRITERIA MONTH BY (DEBIT-CREDIT) AND IT IS LINKED TO EMPLOYEE TABLE ALONG WITH FOR THE MONTH DEBIT CREDIT AS SHOWN

REPORT IS PREPARED ON MONTHLY BASIS

ID NAME OPENINGBALANCE DEBIT CREDIT CLOSINGBALANCE
1 XXX 50 10 5 55
2 YYY 100 50 25 125

THE PROBLEM IS WHEN THERE MORE THAN ONE TRANSACATION IN A MONTH TO A PARTICULAR ID THEN OPENING BALANCE IS APPEARING TWICE

ID NAME OPENINGBALANCE DEBIT CREDIT CLOSINGBALANCE
1 XXX 50 10 5 55
2 YYY 100 50 25 125
2 YYY 100 25 10 115

MY REQUIREMENT IS SOMETHING ELSE LIKE

ID NAME OPENINGBALANCE DEBIT CREDIT CLOSINGBALANCE
1 XXX 50 10 5 55
2 YYY 100 50 25 140
                                25 10

A REPORT IS BEEN PREPARED BASING ON THIS QUERY
REPORT FOOTER SUMS ALL THE TRANSACTIONS

HELP IS REQUIRED
CIVA

I am not sure if you can do this without transaction dates since the order of the transactions will determine the actual ClosingBalance for the month.

A bigger problem is in the programming logic somewhere since this data:

ID NAME OPENINGBALANCE DEBIT CREDIT CLOSINGBALANCE
1 XXX 50 10 5 55
2 YYY 100 50 25 125
2 YYY 100 25 10 115


SHOULD look like this, assuming this is the order of the transactions:

ID NAME OPENINGBALANCE DEBIT CREDIT CLOSINGBALANCE
1 XXX 50 10 5 55
2 YYY 100 50 25 125
2 YYY 125 25 10 140

In this case, it would be much easier to develop a query to give you opening balance, all transactions, and the closing balance for the month. Is there any way to correct this error first?




mmcdonal

Look it up at: http://wrox.books24x7.com

hii,

my problem is not solved
if ob & cb repeats twice than the report footer sum would be doubled and reflects wrong balance. it is in this way that for ob for each for the month credits are addeded up and from the sum debits are minused that is mt cb, the creditds and debits are repeated more than once in a month

if it is not possible in a query can the report be developed dynamically
with report footer summing up ob,credit,debit,cb
this is how my report should be generated
ID NAME OB credit debit cb
1 XXX 50 10 5 55
2 YYY 100 50 25 0
2 YYY 0 25 10 140
             ------------------------------------
                150 85 40 195

as per your query the result of my report result is different

ID NAME OB credit debit cb
1 XXX 50 10 5 55
2 YYY 100 50 25 125
2 YYY 125 25 10 140
---------------------------------------------------------
               275 85 40 320

thanks in advance

What is the date field for these records?


mmcdonal

Look it up at: http://wrox.books24x7.com

hii,

date field is formatted as mm/yyyy

arraving of ob, credits, and debits is not a problem but formatting of report is the main problem due to repeation of ob & cb

thanks

The solution to this problem is this:

tblEmployee
EmpID
FirstName
LastName
...

tblTransaction
TransID
EmpId
Debit_Credit - pos or neg currency values
TransDate - mm/dd/yyyy:time
...

Then all your problems are solved since the calculations take place sequentially. If you are using accounting rules and want only absolute values instead of positive or negative, then you can do this:

tblTransaction
TransID
EmpId
Debit_Credit - positive currency values
TransType - yes/no - default no, yes for credit, no for debit
TransDate - mm/dd/yyyy:time
...

You can even display data entry for this solution to look like a credit or debit column with one column. Just put two columsn on the form, have the user select the employee name, then have them enter data in either the credit or debit text box (disable the other when one is being filled out) then pass the data from the text box to the proper field and have the form check the check box as appropriate. With this structure, you can even show the current balance as the form is opened so the user doesn't over-debit.

THEN you might even create a table that stores an opening and closing balance for each month for each employee. THAT table would have:

tblTransMonth
TransMonthID
EmpID
OpeningBalance
ClosingBalance
TransMonth - mm/yyyy
...

You have to design here for n number of transactions, not one per month.

That being said, I am not sure how you will solve this since you are never going to know what your carry over opening balance is from the previous month this way. My inclination is to solve this issue with proper design. I seem to see your particular design coming out of India a lot since this is not the first time this issue has been posted to solve this design problem with ad hoc solutions. I am wondering how this paraigm was adopted for this business process.

Accounting transactions take place sequentially and exclusively, so the values can be stored in one column with a date/time stamp on the record. Then all your sequential calculations can be worked out, and you can determine balances at any point in time. Just because a double entry ledger has two columns for transactions by type doesn't mean your table has to as well.

That's my two cents. I have just seen this problem posted too many times in this forum.

mmcdonal

Look it up at: http://wrox.books24x7.com