Creating an Invoice System / Report

[duffy1807]

Hi guys, Am having trouble creating an Invoice System, I have just started a new small bussiness and would like to keep records of products that we sell as well as print invoice's.



I have already created my system but thier is a couple of problems.

• I am manually entering VAT figures is their a way for access to calculate this for me.
• I am only able to select 1 product, what if the customer orders 2 different products, i dont want to create 2 different invoice's
• I need a button that will print of the report (already created) for the current selected record on the form.

After I have fixed these 3 problems I think my system will be pretty much complete. Thanks everybody for your support Much aprreciated

Craig.

[Robeen]

Hi Duffy!
1.
There IS a way for Access to calculate your VAT for you.
Where are you currently entering your VAT figures?
Into a Table?
Or are you entering them using a Form?

2.
Is your Invoice being printed off a report that you have created?
Or is it being printed off a Form?
Either way, you should be able to pull up all items that the customer has ordered and print them onto the Invoice.
Tell us how you are doing this now.
You might only need to make a small change to get this going.

[duffy1807]

Hi Duffy!
1.
There IS a way for Access to calculate your VAT for you.
Where are you currently entering your VAT figures?
Into a Table?
Or are you entering them using a Form?

2.
Is your Invoice being printed off a report that you have created?
Or is it being printed off a Form?
Either way, you should be able to pull up all items that the customer has ordered and print them onto the Invoice.
Tell us how you are doing this now.
You might only need to make a small change to get this going.

Hi Robeen, Thanks for the reply,


1. The VAT figures are in my products table (probably not the best way).

2. My Invoice is being printed off from a report.

I will explain my setup a little bit more.

I have 3 Tables / 1 Report / 1 Query / 1 Form

• Products Table
• Customers Table
• Invoice Table

Products Table

1. Stockcode (primary key)
2. Product type
3. Products Details
4. Price
5. Price inc VAT
6. VAT%
7. VAT

(My VAT figures are entered manually because i dont know how to do the calculations automatically. if that makes sence)

Invoice Table

1. InvoiceNum - (AutoNum primary Key)
2. InvoiceDate
3. InvoicePaid
4. StockCode
5. Quantity

Customers Table

1. Invoice Num - (Autonum primary Key)
2. Name
3. Address

I Am not sure if i have done this the correct way, i dont think I have, because I have limitation. such as how a customer will buy 2 different products, as I am only able to select 1.

Their is a couple of issues am working on,


Thanks guys

[Robeen]

To address your question about calculating the VAT and the Price Including VAT.

You can do this calculation in a Query - as follows:

Create a Query off your Products Table and pull the following fields into the query:
Stockcode
Product type
Products Details
Price
VAT%

You will calculate VAT & Price inc VAT IN your query:

[You might have to modify the calculation depending on how your VAT% field is defined in your Table - but this is the general idea.]

In a new field in your query, type [or paste] something like this:
VAT_Amount: ([VAT_Percent]/100)*[Price]

In another new field in your query, type [or paste] something like this:
Price_Inc_VAT: (([VAT_Percent]/100)*[Price])+[Price]

Let me know if this gives you the values you need.

The next step would be to use a similar query to build your Invoice.
The invoice query will do something like pull all orders for a particular Customer [how do you identify all the 'current' orders that need to go on the invoice for a customer?].
Then you use this query as the data source for your Invoice report.

Once you've got the first part of your problem resolved [the VAT calculation], we can work on the invoice part if you need help with that.

[duffy1807]

To address your question about calculating the VAT and the Price Including VAT.

You can do this calculation in a Query - as follows:

Create a Query off your Products Table and pull the following fields into the query:
Stockcode
Product type
Products Details
Price
VAT%

You will calculate VAT & Price inc VAT IN your query:

[You might have to modify the calculation depending on how your VAT% field is defined in your Table - but this is the general idea.]

In a new field in your query, type [or paste] something like this:
VAT_Amount: ([VAT_Percent]/100)*[Price]

In another new field in your query, type [or paste] something like this:
Price_Inc_VAT: (([VAT_Percent]/100)*[Price])+[Price]

Let me know if this gives you the values you need.

The next step would be to use a similar query to build your Invoice.
The invoice query will do something like pull all orders for a particular Customer [how do you identify all the 'current' orders that need to go on the invoice for a customer?].
Then you use this query as the data source for your Invoice report.

Once you've got the first part of your problem resolved [the VAT calculation], we can work on the invoice part if you need help with that.

Haha Robeen nice one, it works fantastic all my calculations, Ive now deleted all the static calculatiosn I had before. Thanks a lot. I didnt know you could do it like that, Nice one.

As to the bit you mentioned about [how do you identify all the 'current' orders that need to go on the invoice for a customer?].

The way I have my Tables set up, I dont think their is a way to indentify all current orders for a particular invoice. Something that I will have to try and figure out.

Thanks alot Robeen

Really appreciate it.

Craig.

[orange]

duffy1807

You may find this helpful.
http://forums.aspfree.com/attachment...achmentid=4712

Good luck

[duffy1807]

duffy1807

You may find this helpful.
http://forums.aspfree.com/attachment...achmentid=4712

Good luck

Hi Orange, Thanks for the reply I know the basics about Relationship's and have set them in my database already.

I will have a read through it to jog my memory. thanks mate

[Robeen]

Orange - thanks for that link.
I find that re-reading the basics of databases . . . and normalization etc 'keeps me honest'.
I tend to forget to apply the basics from time to time.
Appreciate it!

[orange]

Orange - thanks for that link.
I find that re-reading the basics of databases . . . and normalization etc 'keeps me honest'.
I tend to forget to apply the basics from time to time.
Appreciate it!

I think we all do, and I found that this article is an easy read.

[RayMilhon]

Duffy, I would suggest you Modify your design slightly split your Invoice table into 2 tables. 1 For the Invoice Information and a 2nd for the detail.

Invoice
Invoice#
CustomerID
InvoiceDte
InvoicePayedDate

Invoice_Detail
Invoice#
StockCode
Quantity

Customers
CustomerID
Customerdata

In your design the customer would have a new record everytime you created an invoice. Splitting your invoice table gives you the ability to print the report you need.