Help with criteria on select query

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1

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


"graeme34 via AccessMonster.com" u18757@uwe wrote in message
news:5e2edfe124bec@uwe...
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1

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

Tom Ellison wrote:
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

--
Message posted via http://www.accessmonster.com

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


"graeme34 via AccessMonster.com" u18757@uwe wrote in message
news:5e30b4b53a740@uwe...
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

Tom Ellison wrote:
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

--
Message posted via http://www.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


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/For...eries/200604/1

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@uwe...
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/For...eries/200604/1

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

Tom Ellison wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1

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


"graeme34 via AccessMonster.com" u18757@uwe wrote in message
news:5e395d0a8428d@uwe...
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

Tom Ellison wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1

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.

Tom Ellison wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1

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


"graeme34 via AccessMonster.com" u18757@uwe wrote in message
news:5e3b88f8fd894@uwe...
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.

Tom Ellison wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200604/1