how to connect the receipt to the purchase items?

[hugz]

I'm a beginner of access.. CAn sumbdy help me in constructing my tables that
connects the receipt number to the items purchased if the receipt number is
set to Autonumber...pls help... i nid d answer asap...

[Allen Browne]

This is a big question, and there is not a simple answer.

The core issue is to get the tables set up correctly. Presumably you already
have the typical tables set up, such as:
- Client table (one record for each customer, with ClientID primary key)
- Invoice table (one record for each invoice, with InvoiceID primary key)
- InvoiceDetail table (line items for the invoice, relating to
Invoice.InvoiceID.

Now you want to handle payments received.
Could any of these scenarios arise:
a) A client writes out a check that covers 2 or 3 invoices.
b) A client pays part of an invoice now, and the rest later.
c) A client prepays for something that has not yet been invoiced.

If (a) arises, you have a one-to-many relation between payments and
invoices.

If (b) could arise also, you now have a many-to-many relation between
payments and invocies.

If (c) could happen, you have payments that are not actually tied to any
invoice.

The best suggestion, therefore, is to NOT link payments to invoices. You
just accept the payment, and calculate what the client owes you as the sum
of invoices, less the sum of payments. If negative, that's the balance owed.
If positive, that's the amount prepaid. If zero, we're square.

A double-entry accounting system takes this a step further. Instead of
summing all invoices ever written out to the client, and summing all
payments ever received, they close off the finances periodically (typically
at the end of each month), and calculate the closing balance, which is also
the opening balance for the next financial period. It is absolutely crucial
than no changes are permitted to records once the period is closed; if you
find a mistake, you must put a reversal through in the current period. In
this system, the amount owed is the opening balance + invoices in this
period, - payments received in this period. (That's a poor overview: you
probably need to study accounting if you plan to program this properly.)

So, the question is, how far do you need to go?
Do you need to handle (a), (b), and (c)?
Do you want to go as far as double-entry accounting?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hugz" <u33625@uwewrote in message news:7129e0d7e9670@uwe...

I'm a beginner of access.. CAn sumbdy help me in constructing my tables
that
connects the receipt number to the items purchased if the receipt number
is
set to Autonumber...pls help... i nid d answer asap...

[Tom van Stiphout]

On Tue, 24 Apr 2007 12:32:46 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

Even if (c) is the case, you can still relate payments to invoices.
Not in all situations do clients find it acceptable that their
payments are applied to the oldest invoices rather than a specific
invoice.

Also: the further you go down the road of writing an accounting
program, the more you should think about integrating your code with a
real one.

-Tom.

>This is a big question, and there is not a simple answer.

The core issue is to get the tables set up correctly. Presumably you already
have the typical tables set up, such as:
- Client table (one record for each customer, with ClientID primary key)
- Invoice table (one record for each invoice, with InvoiceID primary key)
- InvoiceDetail table (line items for the invoice, relating to
Invoice.InvoiceID.

Now you want to handle payments received.
Could any of these scenarios arise:
a) A client writes out a check that covers 2 or 3 invoices.
b) A client pays part of an invoice now, and the rest later.
c) A client prepays for something that has not yet been invoiced.

If (a) arises, you have a one-to-many relation between payments and
invoices.

If (b) could arise also, you now have a many-to-many relation between
payments and invocies.

If (c) could happen, you have payments that are not actually tied to any
invoice.

The best suggestion, therefore, is to NOT link payments to invoices. You
just accept the payment, and calculate what the client owes you as the sum
of invoices, less the sum of payments. If negative, that's the balance owed.
If positive, that's the amount prepaid. If zero, we're square.

A double-entry accounting system takes this a step further. Instead of
summing all invoices ever written out to the client, and summing all
payments ever received, they close off the finances periodically (typically
at the end of each month), and calculate the closing balance, which is also
the opening balance for the next financial period. It is absolutely crucial
than no changes are permitted to records once the period is closed; if you
find a mistake, you must put a reversal through in the current period. In
this system, the amount owed is the opening balance + invoices in this
period, - payments received in this period. (That's a poor overview: you
probably need to study accounting if you plan to program this properly.)

So, the question is, how far do you need to go?
Do you need to handle (a), (b), and (c)?
Do you want to go as far as double-entry accounting?

[mapzoid]

On Apr 24, 12:32?am, "Allen Browne" <[e-mail]>
wrote:

This is a big question, and there is not a simple answer.

The core issue is to get the tables set up correctly. Presumably you already
have the typical tables set up, such as:
- Client table (one record for each customer, with ClientID primary key)
- Invoice table (one record for each invoice, with InvoiceID primary key)
- InvoiceDetail table (line items for the invoice, relating to
Invoice.InvoiceID.

Now you want to handle payments received.
Could any of these scenarios arise:
a) A client writes out a check that covers 2 or 3 invoices.
b) A client pays part of an invoice now, and the rest later.
c) A client prepays for something that has not yet been invoiced.

If (a) arises, you have a one-to-many relation between payments and
invoices.

If (b) could arise also, you now have a many-to-many relation between
payments and invocies.

If (c) could happen, you have payments that are not actually tied to any
invoice.

The best suggestion, therefore, is to NOT link payments to invoices. You
just accept the payment, and calculate what the client owes you as the sum
of invoices, less the sum of payments. If negative, that's the balance owed.
If positive, that's the amount prepaid. If zero, we're square.

Mr. Brown,

This might be an acceptable solution for a class project or limited
accounting system.
This will not permit you to build an "Account Receivable By Age"
report, where you show
the customer and amount owed by past due dates. In addition, some
business give
a discount to bills that are payed quickly with terms like "2% 10
days, net 30 days".
As you pointed out, a double entry accounting system is required for
this information.
These are old concepts, my source also showed how to automate sales
with punch cards.