Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 22nd, 2007, 06:56 AM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default REPORT

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








Reply With Quote
  #2 (permalink)  
Old November 26th, 2007, 08:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 29th, 2007, 03:04 AM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #4 (permalink)  
Old November 29th, 2007, 07:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the date field for these records?


mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #5 (permalink)  
Old November 30th, 2007, 03:30 AM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old November 30th, 2007, 07:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
simple report and crystal report in vb.net saket123 .NET Framework 2.0 0 August 13th, 2008 06:55 AM
reset links main-report to sub-report from c#.net epanyun Crystal Reports 0 April 18th, 2008 02:41 AM
how can i add report to report viewer in asp.net wael_e BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 0 February 19th, 2008 02:19 PM
Login fail Err when view report on Report Server dillig BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 22nd, 2004 05:31 AM
How to load a Web Server report into a Report Doc dshadle Crystal Reports 3 September 30th, 2003 03:47 PM



All times are GMT -4. The time now is 03:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.