Re: Help with criteria on select query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Graeme:

It seems somewhat unfair to require you to make a dissertation and be
required to relate what you are doing to what is in the real world. Perhaps
those who review your dissertation don't know anything about the real world
anyway. However, if it were up to me, I would make the review based heavily
on one critical capacity, which is difficult to find. This capacity is the
ability to take a specification and turn it into a computer compatible model
that will do what the real world requires. Probably my idea about this is
wildly wrong, as academia rarely has any contact with reality.

But, if this is any part of the project, then the ability to understand the
accounting is fundamental to being able to model it.

Virtually any accounting system you will encounter for receivables is either
open item or balance forward. The concept of matching a payment against a
set of invoices is definitely open item in nature. However, in the real
world, a very small proportion of accounting systems are done this way.
When I receive a statement, I can tell which method has been used. If the
statement shows the balance forward, which is the number at the bottom of
the previous month's statement, and proceeds from there, then it is balance
forward. To this balance forward are added and subtracted all charges and
credits for the month, producing a new balance forward, which will be the
starting point of the next statement.

In this, the most common accounting system, there is no need to assign a
payment so that various amounts from that payment are "applied" against
debits. It is simply all thrown into a big pot and added up. As I said,
this is how the vast majority of businesses do this. Unless you have
specific instructions to do otherwise, there is simply no need to keep track
of "which payments pay for which invoices." It just doesn't come up!

An aging analysis on this system simply removes all payments from the oldest
aging category first, and apply all new chargest to current. It is vastly
simpler than what you're struggling to do.

The question I asked, and whish I did not see you answer, is a fundamental
question of choice between two utterly different systems of accounting. I
recommend against building some type of hybrid out of your imagination. As
I said, the test of your skills in a dissertation would be to be able to
follow instructions (specifications) and produce results from there.

Now, it would be a mistake I suppose to trust that your assignment actually
follows the real world's accounting practices. It's not like you are being
taught to learn how to work in the real world, but in the academic world.
On the other hand, I've been foolishly giving you real world advice. I
suppose the answer is to study your requirements and build something that
meets those requirements without reference to the real world. But I do not
have access to those requirements, and would probably have trouble with them
if they do not match typical real world accounting practices. I would
likely have to study them and adapt to them.

Or are you making up your own requirements for the project as you go? Was
this assigned, or is it your own flight of fancy? I don't mean to be
insulting here, really. I just don't know the basis from which you're
working, and so I don't know well how to advise you.

What do you think? Are my words going to be of any help to you at all?

By the way, I went to a couple of universities for about 6 years myself.
I'm not unacquainted with the side of things in which you're immersed.

Tom Ellison


"graeme34 via AccessMonster.com" <u18757@uwe> wrote in message
news:5e3154fdeca8c@xxxxxx
Hi Tom....

Firstly this 'project' I am working on is for my dissertation.....
I origianally planned to base it on my current employers system......which
is
indeed an accounts receivable system...and....accounts payable...and stock
control...and Sales/Purchase order enrtry (think I under estimated the
task
involved)...I am fine with all the order entry, stock control etc....
but the accounts part has me stumped !!!
The nominal payments etc......accountancy is not for me !!

Back to my point.....I have three weeks left on this project.......it'll
never be the finished
article but.....never mind... :)

My system currently has a customer payment entry form.....

On this form is a sub form that the user ticks (check box) each individual
invoice that is part of the payment....
there is a calcualted control on the form that sums all checked invoices
and
the users compares this with the total payment before verifying receipt of
payment....
This form then updates the tblTransaction..tblSalesInvoice.... I will post
the OnClose event it might make my explanation a bit clearer:

Private Sub Form_Close()

Dim strSQL As String
Dim strSQLUpdate As String
Dim strSQLAddNew As String
Dim rs As Recordset
Dim blPayment As Boolean

If blCancel = True Then Exit Sub

Set rs = CurrentDb.OpenRecordset("tblTransaction")

With rs
.AddNew
!TransactionNumber = Me.txtTransactionNumber
!NominalIndex = Me.cboPaymentTo
!DateOfPosting = Me.txtDateOfPosting
!TypeOfPosting = "Customer Payment"
!TransCredit = Me.txtAmount
!TransactionDetails = Me.txtAccName
.Update
End With

strSQL = "SELECT * FROM tblTempReceivePayment TRP " _
& "WHERE TRP.AccountName = " & "'" & Me.txtAccName _
& "'" & ";"

Set rs = CurrentDb.OpenRecordset(strSQL)
Do Until rs.EOF = True
If rs!SalesInvoicePaid = True Then
strSQLUpdate = "UPDATE tblSalesInvoice SI SET " _
& "SI.SalesInvoicePaid = True, SI.TransactionNumber = " _
& Me.txtTransactionNumber _
& " WHERE SI.SalesInvoiceNumber = " & _
rs!SalesInvoiceNumber & ";"
CurrentDb.Execute strSQLUpdate, dbFailOnError
blPayment = True
End If
rs.MoveNext
Loop

If blPayment = True Then
strSQLUpdate = "UPDATE tblAccount A SET " _
& " A.CurrentBalance = A.CurrentBalance - " & Me.txtAmount _
& " WHERE A.AccountName = " & "'" & Me.txtAccName & "'" & ";"
CurrentDb.Execute strSQLUpdate, dbFailOnError
End If

End Sub


I dont know whether you can make out form this but.......when an Invoice
is
ticked, it is flagged as paid and given the TransactionNumber.....but my
problem is (n) number of invoices are having the same transaction
number...

If I am far of the mark of what I am asking for....i.e accounts
receivable...
could you let me know...as the clock is ticking for my hand in
date.....and I
'm getting to the point of anything will do now :)

Thanks again Tom


Tom Ellison wrote:
Dear Graeme:

The fact that you are breaking out a payment into multiple invoices would
be
indicative of an open item accounts receivable system, rather than of a
balance forward system. Have you made some specific decision in this
respect?

An open item receivables system is a more complex thing to build and to
operate. If this is your assignment, then there needs to be a method to
distribute each payment among invoices, and this must be something the
user
can specifically control.

If you do not intend an open item receivables accounting, then the details
of trying to know exactly how a payment is distributed among invoices
should
not be a concern at all.

Let me know which it is you want to do.

Tom Ellison

Hi Tom yes that worked.....
well I had to create a new query based on the original one as
[quoted text clipped - 99 lines]
Thanks in advance
Graeme

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200604/1


.

Relevant Pages

  • Re: Help with criteria on select query
    ... That is where this accounting system came into it, ... how would I set about adapting what I have so far into a balance ... The enterprise rules of this organisation are payment expected ... I'll try to paint a picture of how my invoice structure is set out. ...
    (microsoft.public.access.queries)
  • Re: Help with criteria on select query
    ... Sorry about the "open balance" mistake noticed it after I'd posted the ... Control Account an Asset account that has a detail section of all invoices as ... If a payment is accompanied by an "advise" then the payment must be applied ... using the automatic distribution method can still receive a distribution ...
    (microsoft.public.access.queries)
  • Re: Help with criteria on select query
    ... appear until the invoice is paid. ... "beginning balance" for an open item receivables system. ... "The enterprise rules of this organisation are payment expected within 30 ... That is where this accounting system came into it, ...
    (microsoft.public.access.queries)
  • Re: Null value for query criteria.
    ... The initial question makes me guess that your accounting is "open ... invoices, with a specific amount from the payment entered. ... a balance of 0 to see which are paid. ...
    (microsoft.public.access.queries)
  • Re: Invoicing of regular services
    ... They had been sending invoices out ... because they would have to account for the VAT early. ... payment until afterwards... ... It just makes the accounting more ...
    (uk.business.accountancy)