Help with an account balancing database

[Nicholas Scarpinato]

I have a database which I've built to store invoice data for customer
invoices that could not be applied to the books for various reasons (i.e.,
the account was already closed, the vendor overpaid, etc.), and now I'm
working on an account balancing for this database to tell me which invoices
have been cleared off from this particular holding account (i.e., the closed
account was transferred to another account number, the vendor took their
overpayment back, etc.). The problem is that our invoicing system doesn't
have a 1-to-1 match. Our invoice numbers are built as follows:

1234567-0-ext1-ext2-ext3

where 1234567 is the invoice number, -0 is the invoice line number, and
-ext1-ext2-ext3 are the various optional identifiers. These identifiers
change for almost every invoice line, and a payment on 1234567-0 might not
have the same identifiers as the billing for 1234567-0. The only way I have
of getting any sort of a balancing for these accounts is to trim off the
identifiers and get a sum of the resulting trimmed invoices. What I've been
running into is that when I do this, a sum query will return a sum of zero,
even though there might be a balance on the invoice! If I have the following
list:

1234567-0: $200
1234567-0: -$200
1234567-0: $15,

my Sum query is doing one of two things: it either kicks the invoice out
completely (the query only looks for zero balances), or it returns zero for
the balance on 1234567-0. Neither option is acceptable.

I've also tried splitting my main data table into a table of credits and
debits and running a join on those tables to get only the invoice numbers
which have an exact opposite amount in the opposing table, i.e.:

1234567-0: $-10 <---> 1234567-0: $10

When I do this query, I make sure to include the entire invoice number and
all it's identifiers on each side of the query so I know exactly which
invoice I'm looking at. Then I take those lists (the one for credits and the
one for debits, from that query) and find those invoices on my main data
table and tag those as being cleared off. When I get to the end of all of
this, I run a sum on the records in the main data table that have been tagged
as cleared. The sum SHOULD be zero, but it never is, it's always a couple
thousand dollars off. I know the database is tagging extra records when they
show cleared (sometimes there are duplicate invoices/identifier
combinations), but I don't know how to prevent this, and quite honestly I
don't trust the database's results anyway. (It seems to be tagging way too
many lines as being cleared.) I don't really know how to go about making this
work, and I'm not sure I've explained it well enough to make myself
understood, but if anybody can help me with ideas or suggestions on how this
can be done, I would greatly appreciate the help. My boss needs this
information by close of business tomorrow, lol.

 

[Ken Sheridan]

Firstly I don't see any mileage in having separate credit and debit tables.
I can't see why you should be having problems with the existing single table.
Taking your example of:

1234567-0: $200
1234567-0: -$200
1234567-0: $15

This gives a credit balance of 15USD so a query which returns only zero
balances, e.g.

SELECT LEFT(FullInvoiceNumber,9) AS ShortInvoiceNumber,
SUM(Amount) As Balance
FROM Invoices
GROUP BY LEFT(FullInvoiceNumber,9)
HAVING SUM(Amount) = 0;

would not return this invoice number. Leaving out the HAVING clause should
return a balance of 15 USD.

I'm puzzled, however, by the line number digit. How come these can all be
zero for each row? I'd have thought they'd have incrementing numbers per
row, in which case to balance each invoice you'd GROUP BY the first 7 digits,
so for zero balances only:

SELECT LEFT(FullInvoiceNumber,7) AS ShortInvoiceNumber,
SUM(Amount) As Balance
FROM Invoices
GROUP BY LEFT(FullInvoiceNumber,7)
HAVING SUM(Amount) = 0;

Again, leaving out the HAVING clause should give you all balances, zero or
otherwise.

Ken Sheridan
Stafford, England

 

[Nicholas Scarpinato]

You would think that they would increment each -0/-1/-2 for each line, but...
our company doesn't work that way. They increment the -0/-1/-2 for each new
item on an invoice, but there are sub items that go with each -0/-1/-2. I
work for an online cell phone retailer, the different -ext1-ext2-ext3
identifiers for each -0/-1/-2 line are used to differentiate between the
commissions, the features, the bonuses, the spiffs, and etc. paid to us by
the carriers. The structure is set up like this:

1234567: Main customer order number
-0/-1/-2: Each new phone or accessory item
-ext1-ext2-ext3: Each identifier for any additional sub-items for each phone

So a single item sold could have three or four additional lines included in
the same 1234567-0 sequence:

1234567-0: The item itself (i.e. a phone)
1234567-0-B: Bonus paid by the carrier
1234567-0-FEA: Any features that the customer has set up on their account
which the carrier also pays us commission on
1234567-0-CH: Churn bonus (SPIF)

Also, if the carrier is deacting the phone, you can have lines like this:

1234567-0-DEA
1234567-0-B-DEA
1234567-0-FEA-DEA
1234567-0-CH-DEA

The same for returns:

1234567-0-B-RTN

etc., etc., etc. So it's not a simple matter of matching a 1-to-1 invoice
line. The actual full invoice numbes from the previous example might very
well look like this:

1234567-0: $200
1234567-0-B: $15
1234567-0-DEA: $-200

Or they might also look like this:

1234567-0: $200
1234567-0-FEA: $15
1234567-0-RTN: $200

There's no set pattern to how the invoices are booked on our system, or how
they are paid from the carrier. It all depends on whoever is working on
applying the payments, and whoever booked the billings into the system. It's
extremely confusing just trying to gather all the data together each month,
much less trying to balance it, which is why I've been charged with working
on a solution for doing so. (It hasn't been done in... 9 months, and even
when it was being done it was all being done manually.)

The main reason I split the main table into two is to try and get a 1-to-1
relationship of balances that cancel eachother out so I can tag those as
cleared. So if table A has the following invoices:

1234567-0: $200
1234567-0-B: $15

and table B has this invoice:

1234567-0-RTN: $-200

I want to match the $200 to the $-200 and mark them both as cleared, leaving
the $15 open. Is it likely that the only way to do this effectively is
through a vBA function, using one table as the lookup table and the other as
the target, and finding matches across the two that way?

 

[Ken Sheridan]

I don't see any way you can cancel out matching lines in the credit and debit
tables, as to join the tables would depend on there being only one match per
amount per invoice line. So if you have 2 debits for 1234567-0 each of 200
USD and 1 credit for 1234567-0 of 200 USD then there is no way in the absence
of any other data value to join on of joining only one credit row to one
debit row and leaving the other debit row outstanding.

Even if it were possible to match row-for-row, having separate credit and
debit tables wouldn't make this any easier. With a single table you can
simply join one subset of the same table (credits) to another subset (debits).

The overall balance per #######-# (e.g. 1234567-0) should be achievable with
a simple aggregating query on the one table, with credits and debits as
values in the same column, debits being negative values, but to match
individual rows and marked them cleared purely on the basis of the #######-#
and amount values does not seem to be possible from the data. The matches
would seem to be arbitrary in any case as far as I can see. What happens if
for a set of debits for one #######-# and a set of debits for the same
#######-# the sums of each equate, but the individual amounts all differ? No
individual rows would match but the credit and debit sums would, so the
balance is zero, which I'd have thought would imply that all the rows should
be cleared, even though there are no one-for-one matches.

I have to admit I'm at a loss to see how anything other than identifying the
overall balances per #######-# can be done. This would, to my thinking, mean
that sets of credit and debit rows can be marked cleared on the basis of
their having zero balances, but where the balances are not zero I don't see
any way of clearing individual rows other than in an unrealistic scenario
where there would always be unique pairs of matching credit/debit values per
#######-#.

Essentially, you can 'clear' subsets of rows in the table (or tables if you
have separate credits and debits, but I don't see any advantage in that), but
not individual rows, or so it appears to me from the information you've
given.

Ken Sheridan
Stafford, England

 

[Nicholas Scarpinato]

I've actually cleared off a few accounts where the total sum of all the lines
is zero but the individual lines are not equal. But the way this data is set
up, I'm in agreement with you that there just isn't enough data to get a
clear-cut balance when the total sum doesn't come out to zero. I guess the
only thing I can do is try to write a vBA code that goes down line by line
and skips anything that's already been cleared. At least that way even if two
or more lines match up on both sides at the same amount for the same
1234567-0 number, the code will skip over the ones it has already matched up
(supposedly). I tried this same approach before in another database, but the
data set was 100 times larger than what this will be, so maybe I'll have more
success this time. Thanks for the help though!