Bank Statement - Credit/Debit transactions (1 Viewer)

Matt99 · Nov 17, 2012

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 · Nov 17, 2012

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 · Nov 17, 2012

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 · Nov 21, 2012

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 Balance

Sum([DepositAmount]))-(Sum([ExpenseAmount])). Of course this didn't work.

ITguy1981 · Nov 21, 2012

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 · Nov 21, 2012

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 · Nov 21, 2012

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 · Nov 21, 2012

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 · Nov 22, 2012

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

Bank Statement - Credit/Debit transactions (1 Viewer)

Matt99

New member

pbaldy

Wino Moderator

Matt99

New member

ITguy1981

Registered User.

ITguy1981

Registered User.

ITguy1981

Registered User.

Attachments

Matt99

New member

ITguy1981

Registered User.

Matt99

New member

Attachments

Similar threads

Users who are viewing this thread