Access 2000 query syntax using DSUM function

[seanseaghan]

New to this group, so greetings all!

I am trying to develop query syntax in Access 2000 to accomplish the
following:

Imagine you are in an accounting dept. and you are working on a
reconiliation of debits and credits.

Say you have a table / list that is 1000s of lines long. These could
be either debits or credits. Then imagine you know that a certain
combination of these numbers (could be 5, 10, 50 or any number of
lines) equal another (a total) item, that would be the offsetting
debit or credit that is not in the list / table.

I have tried several times writing a query that would find the
combination of debits/credits in the table that equal the known
offsetting debit or credit without success. I would imagine a query
is needed for each offsetting debit or credit, or the original query
would be re-used by changing the original plugged in debit or credit.

I have looked in other online forums, books, and consulted other
Access users with more experience than I without luck. Currently, I
think I am heading in the right direction using the DSUM function, but
all the examples that I have found via Google are just variations of
the canned MS-Access help.

Can anyone provide any help / direction with this problem? It would
be greatly appreciated.

Thanks!

 

[Brendan Reynolds]

I don't think you can obtain the result you are looking for without taking
additional columns into account. For any amount, there could potentially be
many combinations of other amounts that added together equal the first
amount. The example below uses three columns (hopefully the names of the
columns will be self explanatory) but even so, it would not eliminate this
problem.

SELECT tblTest.Amount
FROM tblTest
WHERE (((tblTest.Amount)=(SELECT Sum([Amount]) FROM tblTest WHERE CustomerID
= 1 AND DebitCredit = 2)) AND ((tblTest.DebitCredit)=1));

I'm not an accountant, but my understanding is that the usual practice is to
use a system of 'allocations' to keep track of which debit transaction(s) a
particular credit transaction should be allocated against. For example, a
customer may pay you 100 as part-payment of a 200 invoice on one occasion,
and 100 as full payment of two 50 invoices on another occasion. These two
payments form three allocations, one of 100 and two of 50.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Jack Woltz]

Brendan,

thanks for your reply.

I am aware of the two issues you raised.

1. If I could get this to work as described, it would be used as a
starting point only.

So, if a table had 15,000 records, and say it had a debit or credit of
100 USD/EUR/GBP that was returned by the query as a record (with
others). the query results are part of a known, offsetting debit or
credit. But the 100 is a pretty common amount, and could be in the
table many times. Therefore, even though it is part of a group that
equals the known offsetting debit/credit, it would still be investigated
to confirm that it belongs with that offset. The query may have to be
run several times (each time returning a record for 100) before the
correct combination of records is determined with additional research.

2. I am dealing with very large amounts of data converted from a
legacy system. Apparently, accountants/finance people were not involved
to a significant degree with this project, and thus an absolute bare
minimum of transaction details made it through to the new system. This
seemed very odd (unbelievable, even) that this would/could happen in
this day and age at a very large, well-known company, but it has. I
have tried what you described at the bottom of your reply, and
sometimes it will work, other times not. I am looking for a better,
more time efficient solution, rather than a hit and miss approach.

Thanks again for your reply, I will try it out.







=================================

" ..... I ain't no bandleader!!"