Help with criteria on select query

[graeme34 via AccessMonster.com]

Hello experts

I have the following SELECT query which isused as the control source for my
Monthly Statement report..

SELECT DISTINCT A.AccountIndex, A.AccountName, SO.CustomerOrderNumber, SI.
InvoiceDate, SI.SalesInvoiceNumber, SI.TotalNett, V.VATValue, T.TypeOfPosting,
SI.TotalIncVat AS Debit, IIf([TypeOfPosting]="Customer Payment",[TransCredit],
0) AS IFFCredit, T.DateOfPosting, [Debit]-[IFFCredit] AS ItemBalance, SI.
SalesInvoicePaid
FROM tblVAT AS V INNER JOIN ((tblAccount AS A INNER JOIN tblSalesOrder AS SO
ON A.AccountIndex = SO.AccountIndex) INNER JOIN ((tblDespatch AS D INNER JOIN
tblSalesOrderLine AS SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER
JOIN (tblTransaction AS T RIGHT JOIN tblSalesInvoice AS SI ON T.
TransactionNumber = SI.TransactionNumber) ON D.DespatchNumber = SI.[Despatch
Number]) ON (SO.SalesOrderNumber = SOL.SalesOrderNumber) AND (SO.
SalesOrderNumber = D.SalesOrderNumber)) ON V.VATRate = SOL.VATRate
ORDER BY A.AccountIndex, SI.SalesInvoiceNumber;

The query works fine....but I woyuld like to use some criteria in it to
restrict the number of records.....
I will try and explain the detail line on the report and it might help
someone envisage what I am trying to do..

Detail line...

Ref: Date: Details: Debit: Credit:
Item Balance:

Ihave the following code on the Format event of the subreport (detail line)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Balance <> Me.Debit Then

If Me.Credit > Me.Debit Then
Me.txtItemBalance = 0
Me.txtPostingType = " Payment (Part of " _
& Me.Credit & ")"
Me.txtCredit = Me.Debit
Else
Me.txtItemBalance = 0
Me.txtPostingType = "Payment"
Me.txtCredit = curCredit
End If
Else
Me.txtItemBalance = Me.Debit
Me.txtPostingType = Null
Me.txtCredit = Null
End If
End Sub

The problem I have is there is no criteria in the query to prevent paid
invoices for previous months appearing...

What I need is something Like WHERE NOT(If ItemBalance = 0 AND DatePart("m",
DateOfPosting) <> DatePart("m",Now())

I know this is not allowed, one reason being ItemBalance is an expression in
the query...and I'm sure there are a lot of other reasons...but I'm tryig to
give you's an idea of what I require.....

hope this is enough info...
Thanks in advance
Graeme

 

[Tom Ellison]

Dear Graeme:

Your code doesn't seem that far off.

WHERE NOT(If ItemBalance = 0 AND DatePart("m", DateOfPosting) <>
DatePart("m",Now())

This may work:

WHERE NOT( [Debit]-[IFFCredit] = 0
AND DatePart("m", DateOfPosting) <> DatePart("m",Now())

Note that the ItemBalance must be represented by the expression you used for
it.

Is that what you needed?

Tom Ellison

Hello experts

I have the following SELECT query which isused as the control source for
my
Monthly Statement report..

SELECT DISTINCT A.AccountIndex, A.AccountName, SO.CustomerOrderNumber, SI.
InvoiceDate, SI.SalesInvoiceNumber, SI.TotalNett, V.VATValue,
T.TypeOfPosting,
SI.TotalIncVat AS Debit, IIf([TypeOfPosting]="Customer
Payment",[TransCredit],
0) AS IFFCredit, T.DateOfPosting, [Debit]-[IFFCredit] AS ItemBalance, SI.
SalesInvoicePaid
FROM tblVAT AS V INNER JOIN ((tblAccount AS A INNER JOIN tblSalesOrder AS
SO
ON A.AccountIndex = SO.AccountIndex) INNER JOIN ((tblDespatch AS D INNER
JOIN
tblSalesOrderLine AS SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber)
INNER
JOIN (tblTransaction AS T RIGHT JOIN tblSalesInvoice AS SI ON T.
TransactionNumber = SI.TransactionNumber) ON D.DespatchNumber =
SI.[Despatch
Number]) ON (SO.SalesOrderNumber = SOL.SalesOrderNumber) AND (SO.
SalesOrderNumber = D.SalesOrderNumber)) ON V.VATRate = SOL.VATRate
ORDER BY A.AccountIndex, SI.SalesInvoiceNumber;

The query works fine....but I woyuld like to use some criteria in it to
restrict the number of records.....
I will try and explain the detail line on the report and it might help
someone envisage what I am trying to do..

Detail line...

Ref: Date: Details: Debit:
Credit:
Item Balance:

Ihave the following code on the Format event of the subreport (detail
line)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Balance <> Me.Debit Then

If Me.Credit > Me.Debit Then
Me.txtItemBalance = 0
Me.txtPostingType = " Payment (Part of " _
& Me.Credit & ")"
Me.txtCredit = Me.Debit
Else
Me.txtItemBalance = 0
Me.txtPostingType = "Payment"
Me.txtCredit = curCredit
End If
Else
Me.txtItemBalance = Me.Debit
Me.txtPostingType = Null
Me.txtCredit = Null
End If
End Sub

The problem I have is there is no criteria in the query to prevent paid
invoices for previous months appearing...

What I need is something Like WHERE NOT(If ItemBalance = 0 AND
DatePart("m",
DateOfPosting) <> DatePart("m",Now())

I know this is not allowed, one reason being ItemBalance is an expression
in
the query...and I'm sure there are a lot of other reasons...but I'm tryig
to
give you's an idea of what I require.....

hope this is enough info...
Thanks in advance
Graeme

 

[graeme34 via AccessMonster.com]

Hi Tom yes that worked.....
well I had to create a new query based on the original one as
IFFCredit was also an expression....
IIf([TypeOfPosting]="Customer Payment",[TransCredit],0) AS IFFCredit,

I tried pasting the expression into the subtraction, but I'm guessing that is
not allowed..
But basing the new query on query statement line worked....
While your here Tom....can I pick your brains
I would like in the footer of my report a 'debt analysis'
showing the amount owed for the past three months (including current month),
then anything over (if any) that being shown as overdue...
such as..
Amounts Due: April : = Amount
March: = Amount
February = Amount
Overdue = All previous months outstanding..

Where amount is the sumtotal of the invoices sent for each customer - the
sumtotal of payments made for those invoices...

I have the query for the totaling invoices :

SELECT SI.AccountIndex, Sum(SI.TotalIncVat) AS SumOfTotalIncVat, Format(
[InvoiceDate],"mmmm") AS MonthName, DatePart("m",[InvoiceDate]) AS MonthNum
FROM tblSalesInvoice SI
GROUP BY SI.AccountIndex, Format([InvoiceDate],"mmmm"), DatePart("m",
[InvoiceDate]);

I have also created a query for summing the payments made:

SELECT Sum(T.TransCredit) AS SumOfTransCredit, Format([DateOfPosting],"mmmm")
AS [Month], T.TypeOfPosting, DatePart("m",[DateOfPosting]) AS MonthNum, T.
TransactionDetails
FROM tblTransaction AS T
GROUP BY Format([DateOfPosting],"mmmm"), T.TypeOfPosting, DatePart("m",
[DateOfPosting]), T.TransactionDetails
HAVING (((T.TypeOfPosting)="Customer Payment"))
ORDER BY DatePart("m",[DateOfPosting]) DESC;

(TransactionDetails is the name of the Customer)

The problem I have got is I have got to try and link the Transactions
(Payments) to the Invoices, the Invoice (once paid) must have a
TransactionNumber (PK) in tblTransaction.....although Transaction doesnt have
to have a relationship with Invoice, there are other transaction payments
(pay supplier, wages etc)

so linking the two tables is not a problem the problem occurs because a
payment (transaction) will nearly always be for more than one invoice....so
when linking the two tables say for instance TransactionNumber 1 was a
payment from Customer A for...£1,000. That £1,000 payment could be for 4 *
£250 Invoices.....yet in the query recordset it is showing as 4 transactions
of £1000....hence the summing of the query is getting strange results.....

Also the enterprise rules are a customer is allowed 30 days from the end of
the month for...therefore usually it is approaching two months after the
invoice is sent out that the payment is received....

i.e Invoice Feb....payment beginning of April...

I really need to link the payments with the invoices.....catch 22....

Any suggestions ......
Thanks
Graeme.

the

Dear Graeme:

Your code doesn't seem that far off.

WHERE NOT(If ItemBalance = 0 AND DatePart("m", DateOfPosting) <>
DatePart("m",Now())

This may work:

WHERE NOT( [Debit]-[IFFCredit] = 0
AND DatePart("m", DateOfPosting) <> DatePart("m",Now())

Note that the ItemBalance must be represented by the expression you used for
it.

Is that what you needed?

Tom Ellison

Hello experts

[quoted text clipped - 73 lines]

Thanks in advance
Graeme

 

[Tom Ellison]

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
IFFCredit was also an expression....
IIf([TypeOfPosting]="Customer Payment",[TransCredit],0) AS IFFCredit,

I tried pasting the expression into the subtraction, but I'm guessing that
is
not allowed..
But basing the new query on query statement line worked....
While your here Tom....can I pick your brains
I would like in the footer of my report a 'debt analysis'
showing the amount owed for the past three months (including current
month),
then anything over (if any) that being shown as overdue...
such as..
Amounts Due: April : = Amount
March: = Amount
February = Amount
Overdue = All previous months outstanding..

Where amount is the sumtotal of the invoices sent for each customer - the
sumtotal of payments made for those invoices...

I have the query for the totaling invoices :

SELECT SI.AccountIndex, Sum(SI.TotalIncVat) AS SumOfTotalIncVat, Format(
[InvoiceDate],"mmmm") AS MonthName, DatePart("m",[InvoiceDate]) AS
MonthNum
FROM tblSalesInvoice SI
GROUP BY SI.AccountIndex, Format([InvoiceDate],"mmmm"), DatePart("m",
[InvoiceDate]);

I have also created a query for summing the payments made:

SELECT Sum(T.TransCredit) AS SumOfTransCredit,
Format([DateOfPosting],"mmmm")
AS [Month], T.TypeOfPosting, DatePart("m",[DateOfPosting]) AS MonthNum, T.
TransactionDetails
FROM tblTransaction AS T
GROUP BY Format([DateOfPosting],"mmmm"), T.TypeOfPosting, DatePart("m",
[DateOfPosting]), T.TransactionDetails
HAVING (((T.TypeOfPosting)="Customer Payment"))
ORDER BY DatePart("m",[DateOfPosting]) DESC;

(TransactionDetails is the name of the Customer)

The problem I have got is I have got to try and link the Transactions
(Payments) to the Invoices, the Invoice (once paid) must have a
TransactionNumber (PK) in tblTransaction.....although Transaction doesnt
have
to have a relationship with Invoice, there are other transaction payments
(pay supplier, wages etc)

so linking the two tables is not a problem the problem occurs because a
payment (transaction) will nearly always be for more than one
invoice....so
when linking the two tables say for instance TransactionNumber 1 was a
payment from Customer A for...£1,000. That £1,000 payment could be for 4
*
£250 Invoices.....yet in the query recordset it is showing as 4
transactions
of £1000....hence the summing of the query is getting strange results.....

Also the enterprise rules are a customer is allowed 30 days from the end
of
the month for...therefore usually it is approaching two months after the
invoice is sent out that the payment is received....

i.e Invoice Feb....payment beginning of April...

I really need to link the payments with the invoices.....catch 22....

Any suggestions ......
Thanks
Graeme.

the

Dear Graeme:

Your code doesn't seem that far off.

WHERE NOT(If ItemBalance = 0 AND DatePart("m", DateOfPosting) <>
DatePart("m",Now())

This may work:

WHERE NOT( [Debit]-[IFFCredit] = 0
AND DatePart("m", DateOfPosting) <> DatePart("m",Now())

Note that the ItemBalance must be represented by the expression you used
for
it.

Is that what you needed?

Tom Ellison

Hello experts

[quoted text clipped - 73 lines]

Thanks in advance
Graeme

 

[graeme34 via AccessMonster.com]

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

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]

 

[Tom Ellison]

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

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

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

 

[graeme34 via AccessMonster.com]

Hi Tom,

Firstly I would like to thank you once again for taking your time in trying
to help me out.
The degree I am doing is part time as I work full time, my tutor who is
moderating the project advised me it would be easier if I proposed my own
project based on a system I am familiar with.
That is where this accounting system came into it, my current employers have
an old legacy system, DOS based, which has been in place for approx the last
15 years and apart from a slight upgrade for Y2K, has not been maintained in
any way.
The system is made by a company called Anagram, incase you have ever came
across them, no reason why you should have, just that you seem to know a lot
about accounting packages.
The problems with the current system is it has to be closed down at the end
of each month, there are no facilities for record searching once the month
closes, i.e copy invoices, previous sales/purchase orders etc. have to be
found in a paper based filing system.
So my proposal for my project was to implement a relational database that met
all the functional requirements of the old system, yet improved it by having
the searching capabilities a DBMS (using access2003).
But in hindsight I would have been better off breaking it into subsystems ( I
think) as I didn't realise there would be so much coding involved.
As the system is so old there was no prior documentation to work from, so
your assumption that this project is my own 'flight of fancy' (no offence
taken by the way )
is indeed correct. I have produced the requirements of the system, based on
input screens, output screens and hard copy outputs such as despatch notes,
invoices, statements, remittance advice notes.
So the specifications for the project are all of my own doing, the tutor has
no idea yet of what they are.
Although at this late stage I havent really got time to alter them, as I have
already drawn up the DFD's and the data structure of the system (as I see it).

From your explanation of the two categories of Accounting systems, this is
indeed an open item balance.
I have a couple of questions at this point, Tom. I understand the difficulty
in trying to envisage my system from my description alone.
Firstly, how would I set about adapting what I have so far into a balance
forward system.
I'm assuming a balance forward system is something along the lines of a bank
statement. The enterprise rules of this organisation are payment expected
within 30 days from the end of the month the invoice is sent.
How would I go about having a summary of the debt analysis...such as amount
owed for each month....A simple SELECT query can find the sumtotals for each
customer.
From your explanation of balance forward systems (you'll have to pardon my
ignorance this is all new to me) is the payment just subtracted from the
customer balance

An aging analysis on this system simply removes all payments from the oldest
aging category first, and apply all new chargest to current

Could you expand a bit on this please?

I'll try to paint a picture of how my invoice structure is set out.

tblSalesInvoice:
SalesInvoiceNumber (PK), DespatchNoteNumber (FK, for details of despatch 1:1
relationship with invoice), AccountIndex (FK, for tblAccount, originally left
this out as Academic books suggest using Transative dependencies, but it
makes the queries so much harder to build, but like you say the real world
and academic world are miles apart), DateOf Invoice, TotalNett,
CarriageAmount, TotalIncVAT, SalesInvoicePaid (yes/no flag),
TransactionNumber (may have a relationship with tblTransaction, referential
integrity turned off)

Thats my sales Invoice table, from this would it be possible to adapt it to a
balance forward system, and if so what happens if there are ever any payment
discrepancies if the payments arent linked to the invoice....how do you know
which invoice has been paid ??
Would the debt just carry on forwarding over into the next month?

If you could clear these points up to me Tom I would be grateful...
your time is very much appreciated.

Graeme (e-mail address removed)

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

Hi Tom....

[quoted text clipped - 115 lines]

 

[Tom Ellison]

Dear Graeme:

Indeed, I do recall working with you on several aspects of your requirements
before. I'm sorry in a way to be throwing a monkey wrench into the works
now, but it is my position that it was already there, and that perhaps you
haven't seen it yet.

Now, before getting into the many details of your question, I want to make
clear to you the differences in the accounting systems. It may be that
those who review your work will know this, and there's no sense you looking
as though you don't know this, right?

To be able to tell the difference between an open item receivables system
and a balance forward system, you must find an account that is pretty badly
past due. The question is this: if an invoice has not been paid for
several months, does it continue to appear on each successive month's
statement? Or does it appear only on one statement, for the month it is
issued? In an open item system, it is the case that it will continue to
appear until the invoice is paid. Also, on the statement, there will be no
"beginning balance" for an open item receivables system. It simply adds up
the balance of all "open" items to give the balance for the account, whereas
the balance forward system ignores all previous months' activity and just
starts from the balance on the previous month's statement and shows new
activity.

I want to get this straight before proceeding. There's no sense me giving
two answers to every question you ask.

The reason I'm stressing this is because you (probably without knowing it)
continue to give contradictory information. In your most recent post, you
said:

"The enterprise rules of this organisation are payment expected within 30
days from the end of the month the invoice is sent."

This is very typical of a balance forward system. The rule for open item
systems would typically be:

"The enterprise rules of this organisation are payment expected within 30
days from the date of invoice is sent."

You can perhaps see why I would not be convinced you have an open item
system.

In an open item system, the aging of accounts changes every day. An invoice
is due, and becomes past due, on the 30th and 31st days (actually, the
number of days varies with the actual number of days in a month.
Accountants just use 30 to mean a month, so it can be 28 - 31 days.) In a
balance forward system, it is not an invoice that becomes past due, but just
a balance that becomes past due.

You get statements on credit cards, utilities, bank accounts, and probably
other things. They will likely ALL be balance forward. It is quite
possible you will never have seen an open item statement. It is not hard
for me to understand the confusion.

As I said, I feel I would be doing you a disservice not to make quite
certain of the thing you are doing. If your degree depends on it, you don't
want to look uninformed, right?

If the statement starts with a balance forward, and lists only that month's
activity, it is balance forward.

If the statement lists individual unpaid invoices month after month, it is
open item.

Let's make quite sure what it is you need to be doing before proceeding.
That's like the first rule of any kind of project, especially so for
database.

Tom Ellison

Hi Tom,

Firstly I would like to thank you once again for taking your time in
trying
to help me out.
The degree I am doing is part time as I work full time, my tutor who is
moderating the project advised me it would be easier if I proposed my own
project based on a system I am familiar with.
That is where this accounting system came into it, my current employers
have
an old legacy system, DOS based, which has been in place for approx the
last
15 years and apart from a slight upgrade for Y2K, has not been maintained
in
any way.
The system is made by a company called Anagram, incase you have ever came
across them, no reason why you should have, just that you seem to know a
lot
about accounting packages.
The problems with the current system is it has to be closed down at the
end
of each month, there are no facilities for record searching once the month
closes, i.e copy invoices, previous sales/purchase orders etc. have to be
found in a paper based filing system.
So my proposal for my project was to implement a relational database that
met
all the functional requirements of the old system, yet improved it by
having
the searching capabilities a DBMS (using access2003).
But in hindsight I would have been better off breaking it into subsystems
( I
think) as I didn't realise there would be so much coding involved.
As the system is so old there was no prior documentation to work from, so
your assumption that this project is my own 'flight of fancy' (no offence
taken by the way )
is indeed correct. I have produced the requirements of the system, based
on
input screens, output screens and hard copy outputs such as despatch
notes,
invoices, statements, remittance advice notes.
So the specifications for the project are all of my own doing, the tutor
has
no idea yet of what they are.
Although at this late stage I havent really got time to alter them, as I
have
already drawn up the DFD's and the data structure of the system (as I see
it).

From your explanation of the two categories of Accounting systems, this is
indeed an open item balance.
I have a couple of questions at this point, Tom. I understand the
difficulty
in trying to envisage my system from my description alone.
Firstly, how would I set about adapting what I have so far into a balance
forward system.
I'm assuming a balance forward system is something along the lines of a
bank
statement. The enterprise rules of this organisation are payment expected
within 30 days from the end of the month the invoice is sent.
How would I go about having a summary of the debt analysis...such as
amount
owed for each month....A simple SELECT query can find the sumtotals for
each
customer.
From your explanation of balance forward systems (you'll have to pardon my
ignorance this is all new to me) is the payment just subtracted from the
customer balance

An aging analysis on this system simply removes all payments from the
oldest
aging category first, and apply all new chargest to current

Could you expand a bit on this please?

I'll try to paint a picture of how my invoice structure is set out.

tblSalesInvoice:
SalesInvoiceNumber (PK), DespatchNoteNumber (FK, for details of despatch
1:1
relationship with invoice), AccountIndex (FK, for tblAccount, originally
left
this out as Academic books suggest using Transative dependencies, but it
makes the queries so much harder to build, but like you say the real world
and academic world are miles apart), DateOf Invoice, TotalNett,
CarriageAmount, TotalIncVAT, SalesInvoicePaid (yes/no flag),
TransactionNumber (may have a relationship with tblTransaction,
referential
integrity turned off)

Thats my sales Invoice table, from this would it be possible to adapt it
to a
balance forward system, and if so what happens if there are ever any
payment
discrepancies if the payments arent linked to the invoice....how do you
know
which invoice has been paid ??
Would the debt just carry on forwarding over into the next month?

If you could clear these points up to me Tom I would be grateful...
your time is very much appreciated.

Graeme (e-mail address removed)

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

Hi Tom....

[quoted text clipped - 115 lines]

Thanks in advance
Graeme

 

[graeme34 via AccessMonster.com]

Hi Tom

Thank you for the clarification on Open item / balance forward after your
descriptions of both I am 100% sure it is a open balance system.
Invoices will keep appearing until the Invoice is paid. Then on the same
detail line will be details of the payment, i.e if there was only one Invoice
that month the the detail line will contain Payment.......then in the credit
column will appear the amount paid the end of the detail line has an Item
balance figure (Debit - Credit) , if there are a number of invoices for that
month it will contain Payment.....part of (payment amount) then the credit
column has the amount credited = to the debit, then once again Item balance
at the end of the row.
The summary (Footer) has no mention of an opening balance just a Total Amount
O/S
Then a section containing debt analysis in which each individual outstanding
month's total is shown.
I have the detail section mirroring what is currently happening. But it is
the footer section I am struggling with, again this is probably down to my
design in the fact currently I have one payment (transaction number) for
possibly many Invoices.
But the answer to your question Tom, is yes is seems certain that this is
infact a open item system.
Graeme.

Dear Graeme:

Indeed, I do recall working with you on several aspects of your requirements
before. I'm sorry in a way to be throwing a monkey wrench into the works
now, but it is my position that it was already there, and that perhaps you
haven't seen it yet.

Now, before getting into the many details of your question, I want to make
clear to you the differences in the accounting systems. It may be that
those who review your work will know this, and there's no sense you looking
as though you don't know this, right?

To be able to tell the difference between an open item receivables system
and a balance forward system, you must find an account that is pretty badly
past due. The question is this: if an invoice has not been paid for
several months, does it continue to appear on each successive month's
statement? Or does it appear only on one statement, for the month it is
issued? In an open item system, it is the case that it will continue to
appear until the invoice is paid. Also, on the statement, there will be no
"beginning balance" for an open item receivables system. It simply adds up
the balance of all "open" items to give the balance for the account, whereas
the balance forward system ignores all previous months' activity and just
starts from the balance on the previous month's statement and shows new
activity.

I want to get this straight before proceeding. There's no sense me giving
two answers to every question you ask.

The reason I'm stressing this is because you (probably without knowing it)
continue to give contradictory information. In your most recent post, you
said:

"The enterprise rules of this organisation are payment expected within 30
days from the end of the month the invoice is sent."

This is very typical of a balance forward system. The rule for open item
systems would typically be:

"The enterprise rules of this organisation are payment expected within 30
days from the date of invoice is sent."

You can perhaps see why I would not be convinced you have an open item
system.

In an open item system, the aging of accounts changes every day. An invoice
is due, and becomes past due, on the 30th and 31st days (actually, the
number of days varies with the actual number of days in a month.
Accountants just use 30 to mean a month, so it can be 28 - 31 days.) In a
balance forward system, it is not an invoice that becomes past due, but just
a balance that becomes past due.

You get statements on credit cards, utilities, bank accounts, and probably
other things. They will likely ALL be balance forward. It is quite
possible you will never have seen an open item statement. It is not hard
for me to understand the confusion.

As I said, I feel I would be doing you a disservice not to make quite
certain of the thing you are doing. If your degree depends on it, you don't
want to look uninformed, right?

If the statement starts with a balance forward, and lists only that month's
activity, it is balance forward.

If the statement lists individual unpaid invoices month after month, it is
open item.

Let's make quite sure what it is you need to be doing before proceeding.
That's like the first rule of any kind of project, especially so for
database.

Tom Ellison

Hi Tom,

[quoted text clipped - 183 lines]

 

[Tom Ellison]

Dear Graeme:

Is ii just to keep the confusion you call it an "open balance" system?

Not open item or balance forward. Well, that explains a lot!

Now you have a decision. A truly functional open item system will require
much more software than a balance forward system. Is that what you're going
to build?

If so, then my advise is based on some pretty considerable experience in
building this.

If a payment is accompanied by an "advise" then the payment must be applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

So, there are two methods of distributing each payment among invoices (and
also debit memos!). Also, credit memos must be distributable among all
debits. Any portion of a payment (including all of the payment if no manual
distributions are recorded) must then be automatically distributed. You
will need a set of rules for these automatic distributions.

When you post a payment, then, you must have the ability to distribute this
payment among all the open invoices. An invoice that is already "paid"
using the automatic distribution method can still receive a distribution
manually, which will, in effect, override the automatic distribution. The
subsequent automatic distribution process must re-distribute those payment
portions not manually distributed among the remaining debits.

You need to decide whether you will allow a manual distribution that
overpays a debit. This can happen if the account specifies that payments
are to be applied in such a way. You have to allow for others to make
mistakes, and be able to report this back to them.

This is just a small run-down of the kind of trouble you've bought.

So, you'll need these tables:

ARAccounts
ARDebits (invoices, debit memos, other)
ARCredits (payments, credit memos, other)
ARCreditDistribution

This last table references the ARCredits and ARDebits showing an amount
applied.

You will need a process that finds any undistributed amounts in ARCredits
(after subtracting the amounts in the ARCreditDistribution) and applies this
to unpaid balances in ARDebits. This will be one of your biggest
challenges. Start by studying the problem and compose a list of
requirements for how it must work.

You may have the option to go ahead and build the balance forward system.
If you feel this won't hurt your chances of a positive review, or if you
think the challenges of an open item system are prohibitive, that may be the
way to go.

What do you think?

Tom Ellison

Hi Tom

Thank you for the clarification on Open item / balance forward after your
descriptions of both I am 100% sure it is a open balance system.
Invoices will keep appearing until the Invoice is paid. Then on the same
detail line will be details of the payment, i.e if there was only one
Invoice
that month the the detail line will contain Payment.......then in the
credit
column will appear the amount paid the end of the detail line has an Item
balance figure (Debit - Credit) , if there are a number of invoices for
that
month it will contain Payment.....part of (payment amount) then the credit
column has the amount credited = to the debit, then once again Item
balance
at the end of the row.
The summary (Footer) has no mention of an opening balance just a Total
Amount
O/S
Then a section containing debt analysis in which each individual
outstanding
month's total is shown.
I have the detail section mirroring what is currently happening. But it is
the footer section I am struggling with, again this is probably down to my
design in the fact currently I have one payment (transaction number) for
possibly many Invoices.
But the answer to your question Tom, is yes is seems certain that this is
infact a open item system.
Graeme.

Dear Graeme:

Indeed, I do recall working with you on several aspects of your
requirements
before. I'm sorry in a way to be throwing a monkey wrench into the works
now, but it is my position that it was already there, and that perhaps you
haven't seen it yet.

Now, before getting into the many details of your question, I want to make
clear to you the differences in the accounting systems. It may be that
those who review your work will know this, and there's no sense you
looking
as though you don't know this, right?

To be able to tell the difference between an open item receivables system
and a balance forward system, you must find an account that is pretty
badly
past due. The question is this: if an invoice has not been paid for
several months, does it continue to appear on each successive month's
statement? Or does it appear only on one statement, for the month it is
issued? In an open item system, it is the case that it will continue to
appear until the invoice is paid. Also, on the statement, there will be
no
"beginning balance" for an open item receivables system. It simply adds
up
the balance of all "open" items to give the balance for the account,
whereas
the balance forward system ignores all previous months' activity and just
starts from the balance on the previous month's statement and shows new
activity.

I want to get this straight before proceeding. There's no sense me giving
two answers to every question you ask.

The reason I'm stressing this is because you (probably without knowing it)
continue to give contradictory information. In your most recent post, you
said:

"The enterprise rules of this organisation are payment expected within 30
days from the end of the month the invoice is sent."

This is very typical of a balance forward system. The rule for open item
systems would typically be:

"The enterprise rules of this organisation are payment expected within 30
days from the date of invoice is sent."

You can perhaps see why I would not be convinced you have an open item
system.

In an open item system, the aging of accounts changes every day. An
invoice
is due, and becomes past due, on the 30th and 31st days (actually, the
number of days varies with the actual number of days in a month.
Accountants just use 30 to mean a month, so it can be 28 - 31 days.) In a
balance forward system, it is not an invoice that becomes past due, but
just
a balance that becomes past due.

You get statements on credit cards, utilities, bank accounts, and probably
other things. They will likely ALL be balance forward. It is quite
possible you will never have seen an open item statement. It is not hard
for me to understand the confusion.

As I said, I feel I would be doing you a disservice not to make quite
certain of the thing you are doing. If your degree depends on it, you
don't
want to look uninformed, right?

If the statement starts with a balance forward, and lists only that
month's
activity, it is balance forward.

If the statement lists individual unpaid invoices month after month, it is
open item.

Let's make quite sure what it is you need to be doing before proceeding.
That's like the first rule of any kind of project, especially so for
database.

Tom Ellison

Hi Tom,

[quoted text clipped - 183 lines]

Thanks in advance
Graeme

 

[graeme34 via AccessMonster.com]

Hello again Tom,

Sorry about the "open balance" mistake noticed it after I'd posted the
message, but I thought you'd know what I meant.

Firstly...Open Item systems "WOW", didnt realise I was missing so much from
my system. The current system has a nominal ledger (in my system I created a
tblNominal with the only three atributes, Code, Name and Type (i.e Asset,
Liability, expense, original system had Sales and Purchase but I assumed
these would not be required as well as Sales and Purchase tables, but my
assumptions up to now have been way of the mark) the only nominal account I
have created so far is "BANK", Bank details and Asset (Code, Name, Type).
From my original analysis of the system in place, there are in fact a Debtors
Control Account an Asset account that has a detail section of all invoices as
debits and all Customer payments as credits. In turn there is also a
Creditors control account this being a liability account, that has a detail
section listing all supplier invoices as debits and payments to suppliers as
credits. During the analysis stage this was a bit of a 'grey area' to me
(understatement) so I decided to gloss over this as all payments be it
supplier or customer end up in the Bank nominal, and I didnt see the
reasoning for it, how wrong was I?

If a payment is accompanied by an "advise" then the payment must be applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

By "advise" do you mean a remittance advice, in turn is this a credit memo?
If so would a debit memo be a suppliers Invoice?

On viewing the current system the nominal details have the following
attributes.

Creditors control:

Date, Audit, Type of posting, (a column with no heading), Amount,
Transaction details/reference.
The column with no heading has a sequential number for Supplier Invoices, I'm
asumming this is some sort of receipt number.

The Debitors control account has the same attributes.
I am starting to think that I have bitten of more than I can chew here!
I will try and explain the structure of my database and hopefully you can
tell me if I am anywhere near to a solution. If not I think I'll have to call
it a day at what I have developed so far; order entry, despatch, stock
control, Invoicing and receiving Invoices, into their own tables, where they
get posted to may become a matter for the critical evaluation of myself....
and my project management skills

Here is the structure:

tblAccount 1:M rel with tblPurchaseOrder, tblSalesOrder, tblSalesInvoice and
tblDespatch

tblSalesOrder 1:M rel with tblSalesOrderLine and tblDespatch

tblDespatch 1:M rel with tblDespatchLine and a 1:1 rel with tblSalesInvoice

tblProduct 1:M rel with tblSalesOrderLine, tblPurchaseOrderDetails and
tblProductAssembly (allows for a product be made from a number of other
products)

tblPurchaseOrder 1:M rel with tblPurchaseOrderDetails, tblGoodsReceived and
tblReceiveInvoice.

tblVAT has a 1:M rel with tblSalesOrderLine and tblPurchaseOrderDetails.

now here is my grey area.....

tblTransaction "may" have a relationship with tblSalesInvoice or
tblReceiveInvoice

and tblNominal has a 1:M rel with tblTransaction.

If I am a million miles away from where I need to be, I'd rather you told me
straight and I can start my write up and leave the rest of the development as
"future work".

Once again Tom I can't thank you enough for the time you are taking in
explaining the concepts involved in accountancy systems..........never knew
there was so much to it.

Graeme.

Dear Graeme:

Is ii just to keep the confusion you call it an "open balance" system?

Not open item or balance forward. Well, that explains a lot!

Now you have a decision. A truly functional open item system will require
much more software than a balance forward system. Is that what you're going
to build?

If so, then my advise is based on some pretty considerable experience in
building this.

If a payment is accompanied by an "advise" then the payment must be applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

So, there are two methods of distributing each payment among invoices (and
also debit memos!). Also, credit memos must be distributable among all
debits. Any portion of a payment (including all of the payment if no manual
distributions are recorded) must then be automatically distributed. You
will need a set of rules for these automatic distributions.

When you post a payment, then, you must have the ability to distribute this
payment among all the open invoices. An invoice that is already "paid"
using the automatic distribution method can still receive a distribution
manually, which will, in effect, override the automatic distribution. The
subsequent automatic distribution process must re-distribute those payment
portions not manually distributed among the remaining debits.

You need to decide whether you will allow a manual distribution that
overpays a debit. This can happen if the account specifies that payments
are to be applied in such a way. You have to allow for others to make
mistakes, and be able to report this back to them.

This is just a small run-down of the kind of trouble you've bought.

So, you'll need these tables:

ARAccounts
ARDebits (invoices, debit memos, other)
ARCredits (payments, credit memos, other)
ARCreditDistribution

This last table references the ARCredits and ARDebits showing an amount
applied.

You will need a process that finds any undistributed amounts in ARCredits
(after subtracting the amounts in the ARCreditDistribution) and applies this
to unpaid balances in ARDebits. This will be one of your biggest
challenges. Start by studying the problem and compose a list of
requirements for how it must work.

You may have the option to go ahead and build the balance forward system.
If you feel this won't hurt your chances of a positive review, or if you
think the challenges of an open item system are prohibitive, that may be the
way to go.

What do you think?

Tom Ellison

Hi Tom

[quoted text clipped - 113 lines]

 

[Tom Ellison]

Dear Graeme:

An "advice" in the context I mentioned is generally a list of invoices
printed on the "other page" of the check (like a paycheck stub) showing
which invoices are being paid. The payee may specify what is being paid
with the check. Each check may pay several invoices (debit memos too,
occasionally) in part or in full. So, you must record the breakout of each
payment into a set of invoices. At the same time, the system must then
account for the balance of each invoice. Likely your statement would list
each invoice and the payments accounted against it, then its current
balance. That's what Open Item Receivables does. It keeps a running
balance of every invoice, not just for a whole account. Indeed, you may
need to be able to associate a debit memo against a specific invoice, as
well as to an account.

Most of what we're doing here will not be of much interest to others in the
newsgroup. It's more a matter of accounting than database, although the
process of modeling the real world into a database is probably where the
real challenge lies. It's certainly where the development of skill is
involved.

May I suggest you email me at (e-mail address removed). I think we should talk.
I get concerned about leading you down the wrong path. This is obviously
critically important to you. I'm no CPA, but I've worked with them.

Tom Ellison

Hello again Tom,

Sorry about the "open balance" mistake noticed it after I'd posted the
message, but I thought you'd know what I meant.

Firstly...Open Item systems "WOW", didnt realise I was missing so much
from
my system. The current system has a nominal ledger (in my system I created
a
tblNominal with the only three atributes, Code, Name and Type (i.e Asset,
Liability, expense, original system had Sales and Purchase but I assumed
these would not be required as well as Sales and Purchase tables, but my
assumptions up to now have been way of the mark) the only nominal account
I
have created so far is "BANK", Bank details and Asset (Code, Name, Type).
From my original analysis of the system in place, there are in fact a
Debtors
Control Account an Asset account that has a detail section of all invoices
as
debits and all Customer payments as credits. In turn there is also a
Creditors control account this being a liability account, that has a
detail
section listing all supplier invoices as debits and payments to suppliers
as
credits. During the analysis stage this was a bit of a 'grey area' to me
(understatement) so I decided to gloss over this as all payments be it
supplier or customer end up in the Bank nominal, and I didnt see the
reasoning for it, how wrong was I?

If a payment is accompanied by an "advise" then the payment must be
applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

By "advise" do you mean a remittance advice, in turn is this a credit
memo?
If so would a debit memo be a suppliers Invoice?

On viewing the current system the nominal details have the following
attributes.

Creditors control:

Date, Audit, Type of posting, (a column with no heading), Amount,
Transaction details/reference.
The column with no heading has a sequential number for Supplier Invoices,
I'm
asumming this is some sort of receipt number.

The Debitors control account has the same attributes.
I am starting to think that I have bitten of more than I can chew here!
I will try and explain the structure of my database and hopefully you can
tell me if I am anywhere near to a solution. If not I think I'll have to
call
it a day at what I have developed so far; order entry, despatch, stock
control, Invoicing and receiving Invoices, into their own tables, where
they
get posted to may become a matter for the critical evaluation of
myself....
and my project management skills

Here is the structure:

tblAccount 1:M rel with tblPurchaseOrder, tblSalesOrder, tblSalesInvoice
and
tblDespatch

tblSalesOrder 1:M rel with tblSalesOrderLine and tblDespatch

tblDespatch 1:M rel with tblDespatchLine and a 1:1 rel with
tblSalesInvoice

tblProduct 1:M rel with tblSalesOrderLine, tblPurchaseOrderDetails and
tblProductAssembly (allows for a product be made from a number of other
products)

tblPurchaseOrder 1:M rel with tblPurchaseOrderDetails, tblGoodsReceived
and
tblReceiveInvoice.

tblVAT has a 1:M rel with tblSalesOrderLine and tblPurchaseOrderDetails.

now here is my grey area.....

tblTransaction "may" have a relationship with tblSalesInvoice or
tblReceiveInvoice

and tblNominal has a 1:M rel with tblTransaction.

If I am a million miles away from where I need to be, I'd rather you told
me
straight and I can start my write up and leave the rest of the development
as
"future work".

Once again Tom I can't thank you enough for the time you are taking in
explaining the concepts involved in accountancy systems..........never
knew
there was so much to it.

Graeme.

Dear Graeme:

Is ii just to keep the confusion you call it an "open balance" system?

Not open item or balance forward. Well, that explains a lot!

Now you have a decision. A truly functional open item system will require
much more software than a balance forward system. Is that what you're
going
to build?

If so, then my advise is based on some pretty considerable experience in
building this.

If a payment is accompanied by an "advise" then the payment must be
applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

So, there are two methods of distributing each payment among invoices (and
also debit memos!). Also, credit memos must be distributable among all
debits. Any portion of a payment (including all of the payment if no
manual
distributions are recorded) must then be automatically distributed. You
will need a set of rules for these automatic distributions.

When you post a payment, then, you must have the ability to distribute
this
payment among all the open invoices. An invoice that is already "paid"
using the automatic distribution method can still receive a distribution
manually, which will, in effect, override the automatic distribution. The
subsequent automatic distribution process must re-distribute those payment
portions not manually distributed among the remaining debits.

You need to decide whether you will allow a manual distribution that
overpays a debit. This can happen if the account specifies that payments
are to be applied in such a way. You have to allow for others to make
mistakes, and be able to report this back to them.

This is just a small run-down of the kind of trouble you've bought.

So, you'll need these tables:

ARAccounts
ARDebits (invoices, debit memos, other)
ARCredits (payments, credit memos, other)
ARCreditDistribution

This last table references the ARCredits and ARDebits showing an amount
applied.

You will need a process that finds any undistributed amounts in ARCredits
(after subtracting the amounts in the ARCreditDistribution) and applies
this
to unpaid balances in ARDebits. This will be one of your biggest
challenges. Start by studying the problem and compose a list of
requirements for how it must work.

You may have the option to go ahead and build the balance forward system.
If you feel this won't hurt your chances of a positive review, or if you
think the challenges of an open item system are prohibitive, that may be
the
way to go.

What do you think?

Tom Ellison

Hi Tom

[quoted text clipped - 113 lines]

Thanks in advance
Graeme

 

[Tom Ellison]

Dear Graeme:

I have received your email and responded to it. My response was blocked at
your end. The message is:

521 The IP 63.227.158.253 is Blacklisted by dul.dnsbl.sorbs.net. Dynamic IP
Addresses See: http://www.sorbs.net/lookup.shtml?63.227.158.253

I have bumped into SORBS before. They have no reason to suspect me. They
just block a large portion of all privately held email addresses.

I don't know if there is some other email possibility. What I wrote you is:

Dear Graeme:

Not knowing you were in the U.K. I had thought we might speak on the phone.

If I am to look at your database, you'll need to do something first. Make a
copy of your MDB file on your computer and rename it so the file extension
is XXX instead of MDB. The MDB file extension is blocked on Micorosoft
operating systems as an email attachment for security reasons. I can rename
the extension after it gets here. Make sense?

We might also speak long distance for free if you have VOIP. Don't know if
you do, or care to set it up.

At least we likely both speak English! Well, I guess we knew that already,
although I'm not so sure about some with whom I have contact in the
newsgroups.

Tom Ellison

Hello again Tom,

Sorry about the "open balance" mistake noticed it after I'd posted the
message, but I thought you'd know what I meant.

Firstly...Open Item systems "WOW", didnt realise I was missing so much
from
my system. The current system has a nominal ledger (in my system I created
a
tblNominal with the only three atributes, Code, Name and Type (i.e Asset,
Liability, expense, original system had Sales and Purchase but I assumed
these would not be required as well as Sales and Purchase tables, but my
assumptions up to now have been way of the mark) the only nominal account
I
have created so far is "BANK", Bank details and Asset (Code, Name, Type).
From my original analysis of the system in place, there are in fact a
Debtors
Control Account an Asset account that has a detail section of all invoices
as
debits and all Customer payments as credits. In turn there is also a
Creditors control account this being a liability account, that has a
detail
section listing all supplier invoices as debits and payments to suppliers
as
credits. During the analysis stage this was a bit of a 'grey area' to me
(understatement) so I decided to gloss over this as all payments be it
supplier or customer end up in the Bank nominal, and I didnt see the
reasoning for it, how wrong was I?

If a payment is accompanied by an "advise" then the payment must be
applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

By "advise" do you mean a remittance advice, in turn is this a credit
memo?
If so would a debit memo be a suppliers Invoice?

On viewing the current system the nominal details have the following
attributes.

Creditors control:

Date, Audit, Type of posting, (a column with no heading), Amount,
Transaction details/reference.
The column with no heading has a sequential number for Supplier Invoices,
I'm
asumming this is some sort of receipt number.

The Debitors control account has the same attributes.
I am starting to think that I have bitten of more than I can chew here!
I will try and explain the structure of my database and hopefully you can
tell me if I am anywhere near to a solution. If not I think I'll have to
call
it a day at what I have developed so far; order entry, despatch, stock
control, Invoicing and receiving Invoices, into their own tables, where
they
get posted to may become a matter for the critical evaluation of
myself....
and my project management skills

Here is the structure:

tblAccount 1:M rel with tblPurchaseOrder, tblSalesOrder, tblSalesInvoice
and
tblDespatch

tblSalesOrder 1:M rel with tblSalesOrderLine and tblDespatch

tblDespatch 1:M rel with tblDespatchLine and a 1:1 rel with
tblSalesInvoice

tblProduct 1:M rel with tblSalesOrderLine, tblPurchaseOrderDetails and
tblProductAssembly (allows for a product be made from a number of other
products)

tblPurchaseOrder 1:M rel with tblPurchaseOrderDetails, tblGoodsReceived
and
tblReceiveInvoice.

tblVAT has a 1:M rel with tblSalesOrderLine and tblPurchaseOrderDetails.

now here is my grey area.....

tblTransaction "may" have a relationship with tblSalesInvoice or
tblReceiveInvoice

and tblNominal has a 1:M rel with tblTransaction.

If I am a million miles away from where I need to be, I'd rather you told
me
straight and I can start my write up and leave the rest of the development
as
"future work".

Once again Tom I can't thank you enough for the time you are taking in
explaining the concepts involved in accountancy systems..........never
knew
there was so much to it.

Graeme.

Dear Graeme:

Is ii just to keep the confusion you call it an "open balance" system?

Not open item or balance forward. Well, that explains a lot!

Now you have a decision. A truly functional open item system will require
much more software than a balance forward system. Is that what you're
going
to build?

If so, then my advise is based on some pretty considerable experience in
building this.

If a payment is accompanied by an "advise" then the payment must be
applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

So, there are two methods of distributing each payment among invoices (and
also debit memos!). Also, credit memos must be distributable among all
debits. Any portion of a payment (including all of the payment if no
manual
distributions are recorded) must then be automatically distributed. You
will need a set of rules for these automatic distributions.

When you post a payment, then, you must have the ability to distribute
this
payment among all the open invoices. An invoice that is already "paid"
using the automatic distribution method can still receive a distribution
manually, which will, in effect, override the automatic distribution. The
subsequent automatic distribution process must re-distribute those payment
portions not manually distributed among the remaining debits.

You need to decide whether you will allow a manual distribution that
overpays a debit. This can happen if the account specifies that payments
are to be applied in such a way. You have to allow for others to make
mistakes, and be able to report this back to them.

This is just a small run-down of the kind of trouble you've bought.

So, you'll need these tables:

ARAccounts
ARDebits (invoices, debit memos, other)
ARCredits (payments, credit memos, other)
ARCreditDistribution

This last table references the ARCredits and ARDebits showing an amount
applied.

You will need a process that finds any undistributed amounts in ARCredits
(after subtracting the amounts in the ARCreditDistribution) and applies
this
to unpaid balances in ARDebits. This will be one of your biggest
challenges. Start by studying the problem and compose a list of
requirements for how it must work.

You may have the option to go ahead and build the balance forward system.
If you feel this won't hurt your chances of a positive review, or if you
think the challenges of an open item system are prohibitive, that may be
the
way to go.

What do you think?

Tom Ellison

Hi Tom

[quoted text clipped - 113 lines]

Thanks in advance
Graeme

 

[Vincent Johns]

Graeme,

I'm sorry I wasn't able to give you better answers to your
accounting questions, but you're in excellent hands with Tom Ellison, so
perhaps it's best that I wasn't able to get back to you right away. (We
had electrical storms, and I'm superstitious about them -- believing
they're bad luck for computers, I usually unplug everything for a while.
When I saw that Tom was taking care of you, I figured you didn't need
much more from me.)

Anyway, as I looked at the design of your Tables and Queries -- and
perhaps by now you've changed them -- I noticed a couple of infelicitous
features that I thought I might mention. Since you're bearing down
quickly on the due date for your dissertation, perhaps these comments
won't interest you, but I figure they do no good just sitting on my
computer, so here they are.

- In [tblAccount], it makes little sense to record a [CurrentOwed] or
[CurrentBalance] amount of money without also recording when the amount
was valid, as it's likely to change. I recommend keeping those changes
elsewhere. For tracking changes to accounts, you might use the Database
Wizard to create a Ledger database, which allows you to post
transactions to multiple accounts. Use File --> New --> Databases tab
--> Ledger to create it. You may not want to use it in your project,
but it will give an example of a way to organize the information in an
Access database.

- In [tblAccount] you probably need to add a field identifying the
date at which [CurrentBalance] is valid, as I expect you don't want to
keep updating [DateAccountOpened]. (But keeping just one number here
seems more consistent with a balance-forward system, as Tom describes
it, than with an open-items-receivable system. Maybe this field is
inconsistent with the system you're using.)

- I don't understand the rationale behind calling [tblVAT].[VATRate] a
primary key, when you elsewhere claim that it contains a value (such as
17.5%) used in calculating tax. You can probably make any field that
has unique values serve as a primary key, but why do it? The purpose of
a primary key is to identify a record. In this case, I set up another
field, which I called [tblVAT].[tblVAT_ID], to do that. I added a
reference to it in [tblSalesOrderLine], called [tblVAT_ID]. I suppose
you did that to allow you to apply a different tax rate to each item.
The thought occurred to me that you'd want to keep track of some quality
attached to some specific tax rate, but I can't imagine what that
quality would be. (Different taxing authorities, maybe?) I'd expect it
would be dangerously easy to have two different taxes (perhaps in 2
cities) come in at the same rate, and you'd then have no effective way
to distinguish them.

- Although you called [tblSalesOrderLine].[SalesOrderNumber] a primary
key, I think you meant it to be a foreign key matching
[tblSalesOrder].[SalesOrderNumber], and that [tblSalesOrderLine] would
contain details of orders from customers, or of orders to suppliers.
The Table would probably need a primary key, and I suggest calling that
key [tblSalesOrderLine].[SalesOrderLine_ID] so that it will be easy to
remember the Table to which it belongs -- in some other Table, a
matching field called [SalesOrderLine_ID] will pretty obviously match
the primary key of the [tblSalesOrderLine] Table, because of its name,
and you won't have to consult a Relationships diagram to determine that.

- Although you called [tblSalesOrderLine].[ProductCode] a primary key,
I think it's poorly named. Its name suggests that it should be a
foreign key to some kind of catalog, perhaps in a Table called [Products].

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hello again Tom,

Sorry about the "open balance" mistake noticed it after I'd posted the
message, but I thought you'd know what I meant.

Firstly...Open Item systems "WOW", didnt realise I was missing so much from
my system. The current system has a nominal ledger (in my system I created a
tblNominal with the only three atributes, Code, Name and Type (i.e Asset,
Liability, expense, original system had Sales and Purchase but I assumed
these would not be required as well as Sales and Purchase tables, but my
assumptions up to now have been way of the mark) the only nominal account I
have created so far is "BANK", Bank details and Asset (Code, Name, Type).
From my original analysis of the system in place, there are in fact a Debtors
Control Account an Asset account that has a detail section of all invoices as
debits and all Customer payments as credits. In turn there is also a
Creditors control account this being a liability account, that has a detail
section listing all supplier invoices as debits and payments to suppliers as
credits. During the analysis stage this was a bit of a 'grey area' to me
(understatement) so I decided to gloss over this as all payments be it
supplier or customer end up in the Bank nominal, and I didnt see the
reasoning for it, how wrong was I?

If a payment is accompanied by an "advise" then the payment must be applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

By "advise" do you mean a remittance advice, in turn is this a credit memo?
If so would a debit memo be a suppliers Invoice?

On viewing the current system the nominal details have the following
attributes.

Creditors control:

Date, Audit, Type of posting, (a column with no heading), Amount,
Transaction details/reference.
The column with no heading has a sequential number for Supplier Invoices, I'm
asumming this is some sort of receipt number.

The Debitors control account has the same attributes.
I am starting to think that I have bitten of more than I can chew here!
I will try and explain the structure of my database and hopefully you can
tell me if I am anywhere near to a solution. If not I think I'll have to call
it a day at what I have developed so far; order entry, despatch, stock
control, Invoicing and receiving Invoices, into their own tables, where they
get posted to may become a matter for the critical evaluation of myself....
and my project management skills

Here is the structure:

tblAccount 1:M rel with tblPurchaseOrder, tblSalesOrder, tblSalesInvoice and
tblDespatch

tblSalesOrder 1:M rel with tblSalesOrderLine and tblDespatch

tblDespatch 1:M rel with tblDespatchLine and a 1:1 rel with tblSalesInvoice

tblProduct 1:M rel with tblSalesOrderLine, tblPurchaseOrderDetails and
tblProductAssembly (allows for a product be made from a number of other
products)

tblPurchaseOrder 1:M rel with tblPurchaseOrderDetails, tblGoodsReceived and
tblReceiveInvoice.

tblVAT has a 1:M rel with tblSalesOrderLine and tblPurchaseOrderDetails.

now here is my grey area.....

tblTransaction "may" have a relationship with tblSalesInvoice or
tblReceiveInvoice

and tblNominal has a 1:M rel with tblTransaction.

If I am a million miles away from where I need to be, I'd rather you told me
straight and I can start my write up and leave the rest of the development as
"future work".

Once again Tom I can't thank you enough for the time you are taking in
explaining the concepts involved in accountancy systems..........never knew
there was so much to it.

Graeme.

Dear Graeme:

Is ii just to keep the confusion you call it an "open balance" system?

Not open item or balance forward. Well, that explains a lot!

Now you have a decision. A truly functional open item system will require
much more software than a balance forward system. Is that what you're going
to build?

If so, then my advise is based on some pretty considerable experience in
building this.

If a payment is accompanied by an "advise" then the payment must be applied
as specified by the client. Payments without such and "advise" are posted
using some rules against any unpaid

So, there are two methods of distributing each payment among invoices (and
also debit memos!). Also, credit memos must be distributable among all
debits. Any portion of a payment (including all of the payment if no manual
distributions are recorded) must then be automatically distributed. You
will need a set of rules for these automatic distributions.

When you post a payment, then, you must have the ability to distribute this
payment among all the open invoices. An invoice that is already "paid"
using the automatic distribution method can still receive a distribution
manually, which will, in effect, override the automatic distribution. The
subsequent automatic distribution process must re-distribute those payment
portions not manually distributed among the remaining debits.

You need to decide whether you will allow a manual distribution that
overpays a debit. This can happen if the account specifies that payments
are to be applied in such a way. You have to allow for others to make
mistakes, and be able to report this back to them.

This is just a small run-down of the kind of trouble you've bought.

So, you'll need these tables:

ARAccounts
ARDebits (invoices, debit memos, other)
ARCredits (payments, credit memos, other)
ARCreditDistribution

This last table references the ARCredits and ARDebits showing an amount
applied.

You will need a process that finds any undistributed amounts in ARCredits
(after subtracting the amounts in the ARCreditDistribution) and applies this
to unpaid balances in ARDebits. This will be one of your biggest
challenges. Start by studying the problem and compose a list of
requirements for how it must work.

You may have the option to go ahead and build the balance forward system.
If you feel this won't hurt your chances of a positive review, or if you
think the challenges of an open item system are prohibitive, that may be the
way to go.

What do you think?

Tom Ellison

Hi Tom

[quoted text clipped - 113 lines]

Thanks in advance
Graeme