Problems Calulating Fields (Probably Simple) 1

[smspeace]

Dear All,
Seems to be another thread about calculating fields but i hope you arn't all too bored by them to answer! I'm creating an invoicing database. I have created 5 tables and their structures are below;

Client(Client_ID; Name Address etc...)
Invoices{Invoice_ID;Client_ID; Date, Total Price IncVAT, Total Price ExVAT)
InvoiceInformation(Invoice_ID;Product_ID;Quantity, Price IncVAT, Price ExVAT)
Product/ServiceS(Product_ID;Manufacturer_ID, Supplier_ID, Description,
Unit Price IncVAT, Unit Price ExVAT)
Manufacturer(Manufacturer_ID; Name, Address etc)
Supplier(Supplier_ID; Name, Address etc)

I've input all the prices for the products and services into that table. I have created a form which has the CLIENT, INVOICE and INVOICE INFORMATION tables in it (the latter two as subforms/tables).
How do i get it to calculate "PriceIncVAT" in InvoiceInformation from "Unit Price IncVAT" in Products/Services x "Quantity" in Invoice Information ?
Similarly how do i get "Total Price IncVAT" in Invoices to be the sumtotal of all the "PriceIncVAT"s in InvoiceInformation.....


Probably very simple, hope you can understand what i've written above. Thanks in advance all..

Ali

 

[allanon]

Here is what I have for you.

I built 3 tables:

Product/ServiceS
Key Product_ID Unit Price IncVAT Unit Price ExVAT
1 1 $1.55 $2.55
2 2 $8.65 $10.00
3 3 $1.00 $2.00

InvoiceInformation
Key Invoice_ID Product_ID Quantity Price IncVAT Price ExVAT
1 1 1 3 $0.00 $0.00
2 1 1 10 $0.00 $0.00
3 2 1 1 $0.00 $0.00
4 2 2 10 $0.00 $0.00
5 2 3 10 $0.00 $0.00
6 3 1 5 $0.00 $0.00
7 3 2 10 $0.00 $0.00
8 3 3 1 $0.00 $0.00

Invoices
Key Invoice_ID Total Price IncVAT Total Price ExVAT
EMPTY TABLE

I ran this update query first:

UPDATE InvoiceInformation INNER JOIN [Product/ServiceS] ON InvoiceInformation.Product_ID = [Product/ServiceS].Product_ID SET InvoiceInformation.[Price IncVAT] = InvoiceInformation.[Quantity]*[Product/ServiceS].[Unit Price IncVAT], InvoiceInformation.[Price ExVAT] = InvoiceInformation.[Quantity]*[Product/ServiceS].[Unit Price ExVAT];

RESULT:
InvoiceInformation
Key Invoice_ID Product_ID Quantity Price IncVAT Price ExVAT
1 1 1 3 $4.65 $7.65
2 1 1 10 $15.50 $25.50
3 2 1 1 $1.55 $2.55
4 2 2 10 $86.50 $100.00
5 2 3 10 $10.00 $20.00
6 3 1 5 $7.75 $12.75
7 3 2 10 $86.50 $100.00
8 3 3 1 $1.00 $2.00

Then I run an append query:

INSERT INTO Invoices ( Invoice_ID, [Total Price IncVAT], [Total Price ExVAT] )
SELECT InvoiceInformation.Invoice_ID, Sum(InvoiceInformation.[Price IncVAT]) AS Expr1, Sum(InvoiceInformation.[Price ExVAT]) AS Expr2
FROM InvoiceInformation
GROUP BY InvoiceInformation.Invoice_ID;

RESULT:

Invoices
Key Invoice_ID Total Price IncVAT Total Price ExVAT
5 1 $20.15 $33.15
6 2 $98.05 $122.55
7 3 $95.25 $114.75

Hope this helps.

 

[smspeace]

Thankyou so much. That has been such a great help, you wouldn't believe! I was half way there, but that really helps. Thanks for taking the time.