Re: aggregation

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

From: JG (JG_at_discussions.microsoft.com)
Date: 09/20/04 

Date: Mon, 20 Sep 2004 06:21:05 -0700

Thank you for your prompt responses.

I have tried what you suggest. The problem is that I have many columns that
I also want included in the results, but I don't want to group by them. I
want my resultant rows, with the sums in the dollar column, and for the
miscellaneous columns i need to take only the first occurrence to represent,
for instance, all of Tod's rows.

Did I explain this well?

Here's another example. If I started with:

COMPANY DESCR. COUNTRY SALES (Millions)

GenTech a new tech company Australia 10
BDC -------------- ------ --
GenTech tech comp. Canada 12
Keith's -------------- ------ --
Pansing -------------- ------ --
GenTech computers/AV US 18

Then I would like to eliminate the repetitions by summing in the sales
column and taking the "descr." and "country" data from the first row
encountered:

COMPANY DESCR. COUNTRY SALES (Millions)
GenTech a new tech company Australia 40
BDC -------------- ------ --
Keith's -------------- ------ --
Pansing -------------- ------ --

Maybe that's an easier way to show what i need to do?
The only way I've found so far is to use the "group by" and "sum" method,
but i can't do this and keep the rest of the columns ..

Thanks so much,
JG

"StCyrM" wrote:

> Good afternoon
>
> In your query design, simply GroupBy the Name and Sum on the Owes column
>
> Best regards
>
> Maurice St-Cyr
> Micro Systems Consultants, Inc.
>
> >i am open to doing this through query building or code, but don't have any
> >idea how. can someone point me in the right direction? i can't get it to work
> >
> >with queries.
> >
> >thanks,
> >jg
> >
> >
> >
> >
> >
> >
>
>
>

Relevant Pages

  • Re: Subrange
    ... A without first row". ... Microsoft Excel MVP ... sum of negative increments: ... sum of positive and negative increments. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Filling a matrix with values
    ... For every cell C, make a sum of the cell "just above C" ... the first row and the ... Good luck using APL. ...
    (comp.lang.apl)
  • Re: how to count total record and amount
    ... Put this formula in the first row of your data and suse ... This should deal with variable record numbers. ... > currently i put this function(count and sum) at row 200 as user told me ... >> why use a macro?. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Shorten multiple sumifs
    ... Your code works nicely for one line of the sumed range. ... if A2 was the summed range and the letter "A" was in A2 then it will ... for the first row nothing moreSo if A wer were in the first row it ... would not sum these instances. ...
    (microsoft.public.excel.programming)
  • Re: Showing a sum total in a query for fields based on the year -
    ... Access adds a Total row to the query design grid. ... In the Total row under each one, choose Sum. ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)