How do i sum a column before a specified date?

[katzone]

Hi,
I have the number of units in a column and my task is to compute the total
number of units before a specified date (i have another column that states
all the available dates). Right now, I am able to to compute the total number
of units for each date only, not the grand total of units before a specified
date.
Help pls?

 

[raskew via AccessMonster.com]

Hi -

Use a Totals query. Here's an example using Northwind's Orders table, which
totals the Freight field for all records with dates less than 30-Aug-94. You
should
be able to adapt it to your needs.

SELECT Sum(Orders.Freight) AS SumOfFreight
FROM Orders
WHERE (((Orders.OrderDate)<#8/30/1994#));

HTH - Bob

 

[katzone]

Hi Bob,
Thanks for the reply.
But I'm not exactly sure what your sentence means. Do you mean to select the
option "Where" from the drop box at the totals column of under the date field?
Because when I selected "Where" and has input <[Date] at the criteria bar
(since I wanted to find the total number of units before a specified date),
the query fails to run.
Originally, when I put "Group by" at the totals column under the date field,
the query has given the total number of each date, before the specified date
which I have input in earlier. But since my data has many transactions on one
date, it would mean that I was given a summation of the units of individual
dates before my specified date. But in fact what I wanted is a grand total
number of units computed before my specified date, ie to sum up all the units
of the individual dates as one number before my specified date, instead of
showing many total numbers of the individual dates before my specified dates?

Hope you can understand my words? Kind of hard to explain.
Thanks once again.

 

[John Spencer]

Your query should be constructed like
-- Add your table
-- Add the Units field and the date field
-- Select View: Totals from the menu
-- Change Group by to SUM under units
-- Change Group by to WHERE under the date field
-- enter something like the following in the criteria under your date field
-- <#2008-12-31#

If you have named the date field "DATE" then you could have a problem
eventually. Date() is a function that returns the current system date.
So it is possible in some situations for Date (the field) and Date
(the function) to become confused. If possible rename your field to
PurchaseDate or InvoiceDate or some other name that better describes
what the date is.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Bob,
Thanks for the reply.
But I'm not exactly sure what your sentence means. Do you mean to select the
option "Where" from the drop box at the totals column of under the date field?
Because when I selected "Where" and has input <[Date] at the criteria bar
(since I wanted to find the total number of units before a specified date),
the query fails to run.
Originally, when I put "Group by" at the totals column under the date field,
the query has given the total number of each date, before the specified date
which I have input in earlier. But since my data has many transactions on one
date, it would mean that I was given a summation of the units of individual
dates before my specified date. But in fact what I wanted is a grand total
number of units computed before my specified date, ie to sum up all the units
of the individual dates as one number before my specified date, instead of
showing many total numbers of the individual dates before my specified dates?

Hope you can understand my words? Kind of hard to explain.
Thanks once again.

Hi -

Use a Totals query. Here's an example using Northwind's Orders table, which
totals the Freight field for all records with dates less than 30-Aug-94. You
should
be able to adapt it to your needs.

SELECT Sum(Orders.Freight) AS SumOfFreight
FROM Orders
WHERE (((Orders.OrderDate)<#8/30/1994#));

HTH - Bob

 

[raskew via AccessMonster.com]

The query provided returns one record which displays the sum of all [Freight]
for orders prior to 8/30/1994.

John Vinson provided step-by-step instructionsto creating a similar query.
If
you have problems with this, suggest you copy/paste the SQL I provided to a
new query, then change table/field names to agree with your situation. Once
done, switch to design view to see how this query would have been developed.

Bob

Hi Bob,
Thanks for the reply.
But I'm not exactly sure what your sentence means. Do you mean to select the
option "Where" from the drop box at the totals column of under the date field?
Because when I selected "Where" and has input <[Date] at the criteria bar
(since I wanted to find the total number of units before a specified date),
the query fails to run.
Originally, when I put "Group by" at the totals column under the date field,
the query has given the total number of each date, before the specified date
which I have input in earlier. But since my data has many transactions on one
date, it would mean that I was given a summation of the units of individual
dates before my specified date. But in fact what I wanted is a grand total
number of units computed before my specified date, ie to sum up all the units
of the individual dates as one number before my specified date, instead of
showing many total numbers of the individual dates before my specified dates?

Hope you can understand my words? Kind of hard to explain.
Thanks once again.

Hi -

[quoted text clipped - 16 lines]