Summing fields in two ways

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kardos Balázs (kab_at_cowi.hu)
Date: 05/04/04 

Date: Tue, 4 May 2004 05:34:36 -0700

Hi!

Could anybody help me, how to sum up a column two ways in
one query. I mean:

Code Date(M.D) Weight
EUT 05.15 0,5
COM 05.16 8
COR 05.21 2
SAR 05.22 1

SAR 06.03 0,5
COM 06.03 6
EUT 06.16 6,5
COR 06.16 1

Now I would like to make a query, in which I can sum ip
weights for the whle period and for just the month June.

Code Sum(whole period) Sum(just in June)
EUT 7 0,5
COM 14 6
COR 3 6,5
SAR 1,5 1

I tried with the following code (another tables):

SELECT `Sheet1$`.F1, Sum(`Sheet1$`.F4) AS 'Sum all', CASE
When `Sheet1$`.F3>{ts 2004-04-01 00:00:00} THEN Sum(`Sheet1
$`.F4) ELSE 0 END
FROM...
WHERE...
GROUP BY `Sheet1$`.F1

But CASE WHEN statement does not work in MSQuery (in an
Excel Query Table).

The error message was: `Sheet1$` cannot be after SELECT
(syntact error - wrong formula).

Could anybody help me, where can I find informarion about
MSQuery language opportunities?

Thanks:

pontitt

Relevant Pages

  • Re: Simple calculated field problem
    ... This seems to work nicely, however when I go into the query's Design View, I get an error message that reads: ... My database is to track weight loss. ... I would like to be able to track weight loss from one day to the next, so am trying to make a query that shows, for each day, the values in the Weight and Fat% fields, and a calculated field that shows the previous day's weight value. ...
    (microsoft.public.access.forms)
  • Re: query needed please help
    ... You didn't ask for the sum of the weights to show up. ... individual records that made up the sum. ... total weight, perhaps we can modify it to give you what you want. ... Another method would be to use a sub query in the from clause (probably ...
    (microsoft.public.access.queries)
  • Re: Aggregate Queries hassles
    ... I'll try using the Query By Example tool first. ... > some samples of the data for, say, one student, could be most helpful. ... >>the sum of all the scores times the group weight times the activity ... >>group's weight needs to first be divided by the sum of all the group ...
    (microsoft.public.access.queries)
  • Re: Selecting records using criteria as arguments for a mathematical operation
    ... Show us your query and the data types of the fields involved. ... error message seems to indicate you're doing something that violates ... In the Totals row of the query builder, have you considered changing the Sales column from GroupBy to Sum? ...
    (comp.databases.ms-access)
  • Re: Aggregate Queries hassles
    ... >> the sum of all the scores times the group weight times the activity ... >> group's weight needs to first be divided by the sum of all the group ... >> in the course and the activity weight needs to be divided by the sum of ... >> having trouble getting the aggregate query to cooperate with me. ...
    (microsoft.public.access.queries)