Create a database for invoicing

[N]

Hi Everyone

I'm trying to create a simple database to do invoicing. So far I have
created 3 tables, Customer - for customer records, Product - for products,
invoice table for invoices

1. What I'm trying to achieve is, using a form (Invoice Form) enter invoices
to the invoice table and in the same form use a command button to print that
particular invoice which I'm working on.

And the other thing is when I enter a particular value in the Invoice Form
for an example a customer ID, is it possible to automatically get the other
relevant data from the customer table to the invoice table Like customer
name, address etc so that I can use these to print the invoice

I'm new to database so Please I would like any in this matter coz currently
I'm lost!!

Thanks

 

[Jeff Boyce]

Making an easy-to-use-and-understand Access application for creating
Invoices would be an excellent way to learn about relational database
design, Access tricks and user-interface design.

If you are more interested in having an invoicing system than in building
it, you might want to look at some of the inexpensive commercially-available
software already out there... (or do some on-line searching for an
Access-based solution that someone's already written).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Steve:

You are missing a crucial column from the TblOrderLineItem table, Price.
Its not sufficient to have a Price column solely in the TblProduct table as
this would mean that when the unit price of a product changes, all previous
invoices would reflect that change. They should of course retain the unit
price at the time the invoice was raised.

In more formal terms the Price column in TblOrderLineItem is functionally
dependent on the whole of the key of the table (a composite one of OrderID
and ProductID – a surrogate OrderLineItemID is unnecessary). Consequently
the table is in Third Normal Form (3NF). The Price column in TblProduct is
similarly functionally dependent on its key, OrderID.

For a means of handling this see the AfterUpdate event procedure of the
ProductID control in the Order Details Subform in the sample Northwind
database.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Also, take a look at the sample Northwind database which comes with Access.
It does what you want, plus other things. A similar database, but with
annotations, is the Orders database which can be downloaded (along with
Solutions.mdb) from:


http://www.mvps.org/access/resources/downloads.htm


Scroll down to Orders.mdb and Solutions.mdb

Ken Sheridan
Stafford, England

 

[Fred]

Actually, even everyday small business requirements and low end enterprise
softwares go a few steps beyond the above and beyond the Northwind example.
But implementing such in Access can get pretty complicated pretty quickly.

On "one level up", the above posts and Northwind treat an invoice as a
report which shows and order plus items from the order with their pricing.
In business, invoicing is a (one-time) event, and systems treat and enforce
it as such.

Another level up from that is that order items have a status (e.g. "open".
"shipped", "Invoiced" and the invoicing event changes that status.

Another level up from that is that it is / and does transactions with other
databased items such as GL and inventory.

 

[N]

Hi everyone

Thanks for all your comments

I'm still working on it and if worse come to worse I shall try to find
another option

Thanks again
N