Create a database for invoicing

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

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

"N" wrote in message
...
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

Hi N!

Consider the following tables:

TblCustomer
CustomerID
Fields to identify (name) a customer and his contact info

TblProduct
ProductID
ProductName
Price
Inventory

TblOrder
OrderID
OrderDate
CustomerID

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Quantity

If a customer purchases via a purchase order, add a PONum field to TblOrder.

To create an invoice, create a report based on a query based on TblOrder and
TblCustomer. Add to it a subreport based on a query based on
TblOrderLineItem and TblProduct.


Steve



"N" wrote in message
...
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

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

"Steve" wrote:

Hi N!

Consider the following tables:

TblCustomer
CustomerID
Fields to identify (name) a customer and his contact info

TblProduct
ProductID
ProductName
Price
Inventory

TblOrder
OrderID
OrderDate
CustomerID

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Quantity

If a customer purchases via a purchase order, add a PONum field to TblOrder.

To create an invoice, create a report based on a query based on TblOrder and
TblCustomer. Add to it a subreport based on a query based on
TblOrderLineItem and TblProduct.


Steve



"N" wrote in message
...
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

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

"N" wrote:

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

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.


"Ken Sheridan" wrote:

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

"N" wrote:

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

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

"N" wrote:

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