Re: Help with criteria on select query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.

graeme34 via AccessMonster.com wrote:
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.




Tom Ellison wrote:

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


.

Relevant Pages

  • Re: Help with criteria on select query
    ... Control Account an Asset account that has a detail section of all invoices ... tblAccount 1:M rel with tblPurchaseOrder, tblSalesOrder, tblSalesInvoice ... If a payment is accompanied by an "advise" then the payment must be ... using the automatic distribution method can still receive a distribution ...
    (microsoft.public.access.queries)
  • Re: Help with criteria on select query
    ... Sorry about the "open balance" mistake noticed it after I'd posted the ... Control Account an Asset account that has a detail section of all invoices as ... If a payment is accompanied by an "advise" then the payment must be applied ... using the automatic distribution method can still receive a distribution ...
    (microsoft.public.access.queries)
  • Re: Help with criteria on select query
    ... An "advice" in the context I mentioned is generally a list of invoices ... not just for a whole account. ... If a payment is accompanied by an "advise" then the payment must be ... using the automatic distribution method can still receive a distribution ...
    (microsoft.public.access.queries)
  • Re: Really bad table design...
    ... It sounds very much like you want the simpler, and more common, Balance ... the case that a payment is attributed to a specific charge. ... In the activities table, put all the dues, payments, credit memos, debit ... Balance Forward row for any account. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: make money on the internet using paypal
    ... money online? ... $50,000 dollars in your PayPal account ... and include in the e-mail: "Payment for Internet Marketing ... lists of email addresses from a reputable mailing list company. ...
    (sci.math)