If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Create a database for invoicing
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 |
#3
|
|||
|
|||
Create a database for invoicing
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 |
#4
|
|||
|
|||
Create a database for invoicing
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 |
#5
|
|||
|
|||
Create a database for invoicing
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 |
#6
|
|||
|
|||
Create a database for invoicing
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 |
#7
|
|||
|
|||
Create a database for invoicing
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 |
Thread Tools | |
Display Modes | |
|
|