Query calcualting total sum only

[kabir_hussein]

Hi i asked for some help on making a report with three tables and would like ask if anyone knows of a way of making a query simple showing the total sum.

Here is an example

I am trying to calcualte quantity * unit price of 20 items which i then need to show on a report. Is there any way i can simple just make a report which would show the total price of 20 items.

Many thanks

 

[neileg]

If I understand correctly what you want, then:
- Calculate the cost per line in the query by adding a calculated field that holds quantity * unit price
- Calculate the overall cost in the report by adding a text box that sums the cost from each line. Don't try and do this in the query.

 

[kabir_hussein]

Hi many thanks for the reply but i dont want to show any items on the report simple the final sum. I have tried doing the calcualting query, but this has meant i have 26 pages of report as each item is shown even though i clicked on visibilty NO


Regards

 

[neileg]

Can't you just make this a totals query, then? Click the sigma button in design view. Or is this more complex than that?

 

[Rich]

You could also use DSum or just =Sum([YourField]) on the Report

 

[kabir_hussein]

hi many thanks for replys

this is what i am doin at the moment

SELECT Order.[Quantity Ordered], Order.[Unit Price Paid], Order.[Total Cost], Purchase.ProjectID, ([quantity ordered]*[unit price paid]) AS Expr1
FROM Purchase INNER JOIN [Order] ON Purchase.PurchaseOrderID = Order.PurchaseOrderID
WHERE (((Purchase.ProjectID)=[number]));

however this only calculates each item not the total of all the sums. So when i do a query i get 20 or so items with the the total amout of each.

if you know what i mean

regards

thanks for all the help so far

 

[DJN]

Can you not run a sum query using your first query as the basis ? Or am I missing something?

David

 

[kabir_hussein]

Hi

i have attached a screen shot of the four tables i am trying to calculate. At present when i do a query for one of the tables. i get a list of all the items and the total for each item. However i am trying to do a query where i get a overall total for each table. If you know what i mean

Once again many many thanks everyone helping very much appreciate it.

 

[neileg]

Yes, you need to make it a totals query, like I said. If you don't understand, then say so, don't just ignore it!

 

[kabir_hussein]

How do i do a total query.

 

[neileg]

Click the sigma button, that's the one with the Σ on it.

On second thoughts, you need to look up totals queries in the Access help files, first. A totals query will group the records by certain fields, and calculate the aggregate values of others. You can specify the grouping and the aggregate function which in your case will be SUM

 

[Thecherub]

querys of querys

Click the sigma button, that's the one with the Σ on it.

On second thoughts, you need to look up totals queries in the Access help files, first. A totals query will group the records by certain fields, and calculate the aggregate values of others. You can specify the grouping and the aggregate function which in your case will be SUM

It does work as neileg says i've used it my-self for a Sum of a Count in a Different Query. It should work for your totals as well.

 

[kabir_hussein]

Problem

Hi i have tried what you said and i have looked at the help sheets and i still am no closer to trying to get a query to give me a total sum.

At present the query i have done only calcualtes two fields from one item and this is then the result is shown.

Here is my SQL

SELECT ((([tender quantity]*[tender unit price]))) AS Expr1, tenderlink.TenderlinkID, tender.ProjectID, Sum(([expr1])) AS Expr2
FROM tender INNER JOIN tenderlink ON tender.TenderID = tenderlink.TenderID
GROUP BY ((([tender quantity]*[tender unit price]))), tenderlink.TenderlinkID, tender.ProjectID, tenderlink.New
HAVING (((tender.ProjectID)=[Please type in number]) AND ((tenderlink.New)=Yes));

I have tried many other methods and i can seem to be able to get a total calcualtion for 20 items or so

regards

 

[rvd48]

help, i have almost the same problem as kabir has.


i have two main fields: 'price' and 'Quantity' (and 'Item')
i have times them together and have made this statement:
Line Total: Sum(([quantity]*[price]+[Valet Price])) (set as a 'Expresion') which works


now, if i choose more than 1 item than i need a total figure, therefore i made another statement:
Sum: Sum(([line total])) (set as a 'Sum')
when i go and run it, it comes up with an error saying ***subqueries cannot be used in the expresion (Sum([line total]))***, can anyone show me the error of my ways ???

 

[neileg]

Kabir
Try SELECT Sum([tender quantity]*[tender unit price]) AS Expr1, tenderlink.TenderlinkID, tender.ProjectID

rvd48
You are using a reserved word, SUM, as a field name. Try GrandTotal: Sum(([line total]))