Creating an End of Year total Query.

[glnbnz]

I am trying without much luck to create an end of year totals query. I have a
table that looks like this:
Month Lease BblsRun PricePerBarrel
1 Home 10 65.00
2 Home 12 68.00
3 Home 15 72.00
and so on.
I can easily make a query to multiply the BblsRun by PricePerBarrel by the
month, but since the price changes every month, I need a query that will give
1 grand total for the year, just one entry for each Lease is all I need.
Thanks in advance

 

[Arvin Meyer [MVP]]

Build your query as per your design below and save it. The build a second
query with the first as the source, using the totals (Sigma) button. Leave
out the price per barrel and Month, and add the calculated column. Sum the
calculated column and the Bbls Run columns. Something like:

SELECT Query1.Lease, Sum(Query.BblsRun ) AS TotalRun, Sum(Quey1.CalcField)
AS SumOfCalcField
FROM Query1
GROUP BY Query1.Lease;

 

[John Spencer]

SELECT Lease, Sum(BblsRun * PricePerBarrel) as TotalCost
FROM YourTable
GROUP BY Lease

In the query design view (the query grid)
-- Add Lease
-- Create a calculated field
Field: TotalCost: [BblsRun] * [PricePerBarrel]
-- Select View: Totals from the menu
-- Change GROUP to SUM under the calculate field
-- Run the query

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[glnbnz]

Thanks that really help get me in the right direction.
glnbnz