Newbie in Access wanting to SUM totals

  • Thread starter Thread starter Mark \(InWales\)
  • Start date Start date
M

Mark \(InWales\)

Dear All

I have a database which returns 717 records with Dates,Names & Quantities.
What I would like to do is total the Quantities based on the
Dates(month/year) and Names. How oh great ones do you achieve this? I know
how to do this in Excel but how on earth do you calculate this query in
Access?

Many thanks for any help or a nudge in the right direction...

Mark (InWales)
 
Create a query that returns the 3 fields. With the query open in Design
mode, select View | Totals from the menu. That'll add a new row labelled
Total to your query grid, with Group By under each field. Change the Group
By to Sum under Quantities, and you're done.
 
That's marvellous and I believe it would work for the criteria I stated for
the date (mm/yy) however, I have omitted to state the actual date format is
(dd/mm/yy) and I would like to return monthly totals based on the actual
date. Do I need to break the totals down manually "Between (01/01/2004) and
(31/01/2004)" or is there a clever way to do it?

What I am trying to do is...

Take the data from Excel sheet (over a network) via a query, apply the
filters and then send it back to Excel for the ever popular pretty graphs,
whilst retaining the monthly totals in the same Excel workbook by Filler.

Thank you Douglas for your help.

Mark (InWales)
 
Instead of having your date field in the query, replace it with MonthYear:
Format([MyDateField], "mm/yyyy") in the query grid.
 
Many thanks for your time Douglas - will give it a go

Mark (InWales)

Douglas J. Steele said:
Instead of having your date field in the query, replace it with MonthYear:
Format([MyDateField], "mm/yyyy") in the query grid.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Mark (InWales) said:
That's marvellous and I believe it would work for the criteria I stated
for
the date (mm/yy) however, I have omitted to state the actual date format
is
(dd/mm/yy) and I would like to return monthly totals based on the actual
date. Do I need to break the totals down manually "Between (01/01/2004)
and
(31/01/2004)" or is there a clever way to do it?

What I am trying to do is...

Take the data from Excel sheet (over a network) via a query, apply the
filters and then send it back to Excel for the ever popular pretty
graphs,
whilst retaining the monthly totals in the same Excel workbook by Filler.

Thank you Douglas for your help.

Mark (InWales)
Click to expand...
Click to expand...
 
Back
Top