Bank Statement - Credit/Debit transactions

[Matt99]

Hi... Can you please help?

I have a table that display 4 columns as listed below...

ID Date Debit Credit
01 1-May-2010 0 10,000
02 1-May-2010 10,000 0
03 1-May-2010 66.75 0
04 4-May-2010 2,500 0
05 4-May-2010 1,010 0
06 4-May-2010 0 2,500
07 4-May-2010 75 0
08 4-May-2010 0 1,000
09 7-May-2010 510 0
10 7-May-2010 500 0
11 7-May-2010 0 500
12 7-May-2010 0 70

Note: As you see, Each line above can contain only one non-zero value either in Debit or Credit column. So each line can only indicate a Debit or Credit transaction.

I would like to use Ms-Access queries or reports or other solutions you think would work...

That will match Debits vs Credits for each day then display the:

1. matched exact values (between Debit and Credit values)
Or
2. display the values with the difference of up to $15


Table results is listed below...

ID Date Debit Credit
01 1-May-2010 0 10,000
02 1-May-2010 10,000 0
04 4-May-2010 2,500 0
05 4-May-2010 1,010 0
06 4-May-2010 0 2,500
08 4-May-2010 0 1,000
09 7-May-2010 510 0
10 7-May-2010 500 0
11 7-May-2010 0 500

Any idea? :banghead:

Thank you

 

[pbaldy]

If you create a totals query that groups on the date and sums the amount fields, you can use different criteria to return your results.

 

[Matt99]

Tnaks. But I tried to use the totals query beforehand to group , also used the function sum, could not get the results. Thing is I need to return the detailed records eventually.

You mentioned (you can use different criteria to return)...any hints?

 

[ITguy1981]

I have a similar issue and I haven't found a thread to answer so I thought I would post here. I have two fields. One field is [DepositAmount] and the other is [ExpenseAmount]. I have been able to use a query to get the sums of each, but how do I get the difference of the two. I tried BalanceSum([DepositAmount]))-(Sum([ExpenseAmount])). Of course this didn't work.

 

[ITguy1981]

Hey matt. Have you tried creating a totals query using the credit, debit, and date fields. Change total to sum for credit and debit. You could then specify a date for the date criteria which should give you the debit sum and credit sum for the day you entered.

 

[ITguy1981]

I've attached a sample database that has 4 fields: ID(for autonumber), Credit, Debit, and TransactionDate. I created a form with two unbound text boxes formatted for short date. I created a total query calculation sum for credit, sum for debit, and the transactiondate with the criteria to look for a start date and end date from the form made earlier. The query will give you the sum for each day that's in the range or just the sum of one day if the start and end is the same. Hope it helps.

 

[Matt99]

Thank you ITguy for your help. It is very much appreciated !!

I also (finally) found a solution. I simply created a select query and added the table twice in the same query, but I did not use any self join - meaning I did not link the same table to its alias.... so I got a cartesian product records, used few calculated boolean fields and a couple of criterias, and it worked!!

I can attach you the file but I still do not know how !! ) I appreciate it if you can give me a hint...I'm new to this site.

Thanks again

 

[ITguy1981]

I'm glad it's working for you Matt. If you click the reply button at the top of the page you will have more post options. You will notice a paperclip by the smiley addin at the top. That will allow you to add file attachments. You do not get that option using quick reply.

 

[Matt99]

Hello ITguy1981.
I am attaching a demo zip file.
Thanks
Matt