I have a table that has debits and credits in two different columns along with other info.
as you can see from the attached Excel there is one debit for each credit. in some cases though there is one debit and 2 or more credits that sum up that ammount.
the common thing that Debits and Credits have is the project number.. but this alone will bring me all the entries!
how can i finf these lines? i have tried all sort of queries but nothing works.. anyone can help... its one thing i didnt think of..
Assuming the table name is RFG_Append, run this query:-
SELECT Mid(Left([Proj],InStr([Proj]," ")-1),8) AS ProjID, [SOBP], [Debits], [Credits], [Proj]
FROM [RFG_Append]
ORDER BY Mid(Left([Proj],InStr([Proj]," ")-1),8), [Credits], [Debits];
EDIT:
the common thing that Debits and Credits have is the project number.. but this alone will bring me all the entries!
Oh, most likely I have read your question wrong! My query only extracts the project IDs and sort by them.
If what you want is to match each Debit with its corresponding Credits, then you will need to use VBA to loop through the records and place some markings (such as a Voucher Number as in doing book-keeping) for each group of matching Debit and Credits. .
.[/quote] If what you want is to match each Debit with its corresponding Credits, then you will need to use VBA to loop through the records and place some markings (such as a Voucher Number as in doing book-keeping) for each group of matching Debit and Credits. .[/QUOTE]
how i do the marking? any suggestions? am stuck and this is the last part of my utility... i would appreciate the help.. any hints to look up?
The VBA side of tackling this issue is rather involved. As the number of credit entries for a matching debit may vary, you may need to open the table in recordsets, use loops, arrays and the recordset GetRows method to develop a mathematical Combinations Model to test for the various possible sums of credits and update the suitable records.
i want to much only those entries that have more than one ammount to add up.... can i seperate that?
That's much easier to accomplish as you can do it with queries. No VBA is needed.
First, you can add three fields to the table RFG_Append:
ID - autonumber
CrMatched - text
DrID - number, long integer
Then build these queries.
qryDebits:-
SELECT ID, SOBP, Debits, Credits, Proj, CrMatched, DrID
FROM RFG_Append
WHERE Debits Is Not Null AND CrMatched Is Null AND DrID Is Null;
qryCredits:-
SELECT ID, SOBP, Debits, Credits, Proj, CrMatched, DrID
FROM RFG_Append
WHERE Credits Is Not Null AND CrMatched Is Null AND DrID Is Null;
qryUpdate:-
UPDATE qryDebits INNER JOIN qryCredits ON (qryDebits.Proj=qryCredits.Proj) AND (qryDebits.Debits=qryCredits.Credits) SET qryDebits.CrMatched = "Yes", qryCredits.DrID = qryDebits!ID;
qryView:-
SELECT ID, SOBP, Debits, Credits, Proj, CrMatched, DrID
FROM RFG_Append
WHERE CrMatched Is Null and DrID Is Null
ORDER BY Proj, Credits, Debits;
Run the Update query then the View query.
Where a debit can be matched with a credit, the Update query will mark the CrMatched field of the debit entry with a Yes and the DrID field of the credit entry with the ID number of the matching debit. Of the 73 entries in your sample data, the Update query will take care of 70 of them.
The View query should show the remaining 3 entries which have not been dealt with by the Update query.
The View query is updatable. You can manually markd the CrMatched and DrID fields in the query.
Note:
There is a potential risk/danger in using "project ID plus an amount" as record identifier for matching purposes in queries because there may be multiple same amounts in a project .
i tried it and for some cases it work but for some other cases it doestn. like in the case am attaching.. the same debit and credit are not matched. do you know why this might happen?
the lines are hilighted with yellow... they are matched but YES doesnt aplly to them.
I didn't have any problem with the two highlighted entries. They were properly matched by the update query.
The update query should work unless there are multiple instances of the same "[Proj] plus [Debits]" and/or the same "[Proj] plus [Credits]" entries in the table. .
If you are interested in writing the VBA code .....
(the title of the thread suggested you may be)
If multiple instances of "[Proj] + same amounts" exist (or may exist) in either the debit or the credit entries, you will have to use VBA.
Judging from your sample data, I think a Combinations Model of 5 credits to 1 debit should be more than enough to cover the entries. For 5 credits, there are 5+10+10+5+1 = 31 possible combinations to test for. Assuming the credit amounts are A,B,C,D and E, here are the 31 combinations to test for in VBA when matching with a debit amount:
A
B
C
D
E
AB = A + B
AC = A + C
AD = A + D
AE = A + E
BC = B + C
BD = B + D
BE = B + E
CD = C + D
CE = C + E
DE = D + E
ABC = A + B + C
ABD = A + B + D
ABE = A + B + E
ACD = A + C + D
ACE = A + C + E
ADE = A + D + E
BCD = B + C + D
BCE = B + C + E
BDE = B + D + E
CDE = C + D + E
ABCD = A + B + C + D
ABCE = A + B + C + E
ABDE = A + B + D + E
ACDE = A + C + D + E
BCDE = B + C + D + E
ABCDE = A + B + C + D + E
The VBA code will be a bit long; may need four or five pages. .
this weird thing is happenning my friend. when i import the excel using the automated import with VBA that i wrote.. even if i clear the IDs and everything i get the extra line.. the one that is colored in the Excel before.
when i import the excel manually into the table and clear the IDS that query works fine and brings me only the correct entries.
any idea why is this happening?
i have autonumber on my ID but i have it on both cases mentioned above so i dont thing thats the problem. Another thing is that in the table multiple excels are imported. is there a problem with that u think?
i tried the same thing on your attached database and i get the same thing. when i deleted the table and imported the one from my database i get the same thing..
i am attaching the mdb with the table from my database.. it gives the same error... it still brings the line even if i clean the IDS.. can you run it to see if u see anything?
When imported into the table with VBA, the internal values of the debit 315.4 and the credit 315.4 were not exactly the same (that's not uncommon with floating point numbers), so internally there is a tiny difference existing between the two amounts of 315.4.
You can run this query on the table to reveal their difference.
SELECT Debits AS Dr,
(SELECT Credits FROM RFG_Append WHERE ID=1384) AS Cr,
Dr-Cr AS Diff, CCur(Dr)-Ccur(Cr) AS CCurDiff
FROM RFG_Append
WHERE ID=1380;
On my system, it shows a slight difference of 5.6843418860808E-14 in the two amounts.
Code:
Dr Cr Diff CCurDiff
315.4 315.4 5.6843418860808E-14 $0.00
As the query results have shown, a workaround would be to use the CCur() function on the amounts (or change the two fields to Currency in the table) before doing the matching. Currency internally maintains 4 places of decimal. .
