Calculation problem

[kev88]

Hi there,

I was wondering if someone could help me.

I have two tables - one which contains the Invoice with details such as the Sales Rep who made the sale and the transaction date. It will also contain a total invoice cost - which is what I'm having trouble with.

The other table contains each line of the invoice (i.e. the products that are sold)

http://tinypic.com/view.php?pic=99nh9e&s=5

I want to add each unit cost for every product in the invoice to give an overall total for the invoice to be displayed in the first table.

For example, in the link above, I want to add up the unit cost's for invoice 10264 and place the total in the "Invoice Total" in the 1st table which corresponds to that invoice number

How would I go about making a calculation to do this?

Thanks very much for your help and sorry if my explanation is poor. I really appreciate your help with this

 

[plog]

You shouldn't save calculated values in tables, you should simply calculate them in queries and use those queries when you need that data. That means you shouldn't have a Total column in either table. This query will calculate the Total per invoice:

SELECT InvoiceID, SUM(Quantity*UnitCost) AS InvoiceTotal FROM InvoiceLineItemTableNameHere GROUP BY InvoiceID;

Replace InvoiceLineItemTableNameHere with the name of the table that contains the line items.

 

[kev88]

ok thanks...I'm not very experienced with this so I'd appreciate if you could simplify this for me thanks for your help so far.

 

[plog]

You need to expand on your request for me to simplify. What are you having problem with? My query should give you the total per invoice which is what you asked for.

 

[kev88]

How do I put the query in? Once going to "Query Design", do I copy and paste the line above into "criteria"?

Sorry for the lack of clarity

 

[vbaInet]

Just fyi (nothing related to your OP because plog is helping you out), you can actually upload images on here. Have a look at the forum's FAQs:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[kev88]

Thanks for the suggestion. I have attached an image of my query design page where I was thinking about putting the line above

 

[vbaInet]

In plog's absence he mentions that you shouldn't be saving calculated values so have a look at this tutorial to see what he suggested:

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

This is done in a SELECT query.

 

[kev88]

Ok, I understand how to get a select query now thanks
But how do I enter this

SELECT InvoiceID, SUM(Quantity*UnitCost) AS InvoiceTotal FROM InvoiceLineItemTableNameHere GROUP BY InvoiceID;

I have attached what I have so far.

Sorry about my lack of knowledge - but I'm keen to learn though

 

[vbaInet]

Replace this:

Expr1: [InvoiceTotal]

with this:

InvoiceTotal: [Quantity] * [SellPrice]

Then under this field, change Group By to Count.

 

[kev88]

I tried to do this but Access modified the query to what is displayed below and produced the number of products in the invoice rather than the total of each invoice.

Have I done something wrong?

Thanks again

 

[Kiwiman]

Howzit

you have used Count rather than Sum in your invoicetotal expression.

 

[kev88]

Ok thanks I've managed to do that

I am now trying to create a query like before but now with profitability. I've given it a shot and I set it up (query4.jpg), but when I try and run it, it comes up what is shown on query5.jpg.

I think I've got the actual query itself right but not sure about the error message that comes up. Any suggestions? Thanks for all your help so far - its much appreciated

 

[Kiwiman]

Howzit

The query says that Selling Price could be obtained from more than 1 table. You therefore need to specify which table the selling price should come from - eg.

[tblItemsOnInvocie]![SellPrice]

 

[kev88]

Ok I tried that.

Does the formula look right to you? (problem2.jpg)

When I try and run the query, it comes up with the screen on problem1.jpg

 

[vbaInet]

Check the spelling.