Access 2007 Database - Invoicing form

Greetings,

I'm trying to develop / design a very simple Access 2007 database that will allow me to create invoices.

Invoices:
1. Auto numbered invoices.
2. Ability to select the customer code (and then display the customer name / balance of 'open' invoices).
3. Able to select inventory info (rows) with item unit price, quantity, row total etc.
4. Invoice info (and total) should be able to be viewed under a tab in the Customer Master.
5. Able to key in a payment receipt details in a seperate tab under each invoice (multiple rows) which will allow for printing of a Receipt document (vs total invoice, if there is an example of partial payments). Payment receipt can not exceed the invoice total.
6. All invoices in total should be able to be updated backwards in regards to $ total on the Customer Master.


I'm able to create the 'master data' like Customer Master, Employee Master, Inventory Master, but I have no idea on where and how to start with the Invoice area.

Any help would be great.

 

Greetings,

Well I can just save a sample into Access 2003, so we can review / discuss the tables / function and features?

Thank you in advance!

Dav

 

Ok good call on the CustID and InventoryID.

Changed those accordingly, also create a table for the Invoice.

Since the Invoice table is the "header data", should I create another table called InvoiceRows that will contain the fields / values:

• Item Code
• Item Details
• Unit Price (sales)
• Quantity
• Row total (Unit price x Quantity)
• Remarks

Updated the Access file with v1_1.

Thank you.

Dav

 

I think to be on the safe side, data updated into each individual invoice should remain 'individual' and any changes on the master data (IE: Customer master or inventory master) should not roll back and reflect Invoices created before the changes were made.

I would not want a case where I've posted Invoice #1 for $100 and then amend an Item's Price, then 6 months later realise that Invoice #1 has changed to $110

 

I've been searching online for a sample of how to do an invoice with Access (2003 / 2007) but I cant seem to find a database to review / look at.

Of course, if I had a basic DB to look at, I can then add-on to that in regards to the Customer/Inventory Table.

As mentioned, I'm trying to create an Invoice that will allow me to select a customer, then in the row level, select a number of stock items (drawing the current price etc of the stock) and maybe having a few formula's to calculate the row total (lets say there's tax on the row level, maybe even calculate that) and then another formula to calculate the entire Invoice total (if there are discounts, to deduct that value as well).

I'm not concerned with stock quantity (like in a warehouse etc) since to me all my items are 'non-stock' and if they are stock, I'll manage the availability manually anyway.

Thank you for responding, looking at how we can work this out together (so at least I learn something! )

 

Here we go, think i have the InvoiceRow table (not too sure if its right though)

 

Woah, this is looking pretty close to what I was aiming for.
I deal with individual / companies but I'm pretty ok with what I have right now. What I could do is that when we're able to link the Customer code to the Invoice, we could have a button that says 'Print Invoice' (for the current selected invoice).


Only a few things:

1. I'd like to be able to select the Customer code (maybe a drop down that show the Customer Code and Customer Name?).
2. For the Invoice rows, is it possible for the Item Sales Price (Unit Price) to show up automatically when I select the Item Code, and then have the Quantity x Unit Price = Row Total?
3. The individual Row Total's would then be totaled up for Invoice Total.