Board index » VBA

All times are UTC



 Select first rows. 
Author Message
Sanjay Meht
#1 / 6
 Select first rows.

i have the following results of a query:

number  customer    company     amount

  1      smith       msft       100.00 **
  2      smith       msft       100.00
  3      smith       msft       100.00
  8      smith       at&t       250.00 **
  9      smith       at&t       250.00
 11      smith       gte        150.00 **
 12      smith       gte        150.00
 13      smith       gte        150.00
  1      jones       msft       500.00
  2      jones       msft       500.00
  5      jones       adobe      300.00
  6      jones       adobe      300.00

 i want the sum of the amounts of the FIRST occurence
 of the amount for each company pertaining to the  
particular  customer.

 from above total sum of amount for smith would be 500.00.

 i can use the SELECT statement with the FIRST
(table.field) as  FirstAmount etc... but can i get the sum
of the amounts as above.

thanks for your help  

sm



Wed, 30 Mar 2005 03:58:28 GMT  
Dirk Goldga
#2 / 6
 Select first rows.
I *think* this may do what you want:

    SELECT Firsts.Customer, Sum(Firsts.FirstAmount) AS Amount
    FROM
        [SELECT DISTINCT TestData.Customer, TestData.Company,
            (SELECT TOP 1 T.Amount FROM TestData AS T
             WHERE (T.Customer=TestData.Customer
                   AND T.Company=TestData.Company)
             ORDER BY T.Number)
            AS FirstAmount
         FROM TestData]. AS Firsts
    GROUP BY Firsts.Customer;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:

> i have the following results of a query:

> number  customer    company     amount

>   1      smith       msft       100.00 **
>   2      smith       msft       100.00
>   3      smith       msft       100.00
>   8      smith       at&t       250.00 **
>   9      smith       at&t       250.00
>  11      smith       gte        150.00 **
>  12      smith       gte        150.00
>  13      smith       gte        150.00
>   1      jones       msft       500.00
>   2      jones       msft       500.00
>   5      jones       adobe      300.00
>   6      jones       adobe      300.00

>  i want the sum of the amounts of the FIRST occurence
>  of the amount for each company pertaining to the
> particular  customer.

>  from above total sum of amount for smith would be 500.00.

>  i can use the SELECT statement with the FIRST
> (table.field) as  FirstAmount etc... but can i get the sum
> of the amounts as above.

> thanks for your help

> sm



Wed, 30 Mar 2005 04:50:41 GMT  
Viktor Umanski
#3 / 6
 Select first rows.
SELECT MyTable.Customer, SUM(MyTable.Amount) AS SumAmount
FROM MyTable INNER JOIN
(SELECT Customer, Min(Number) AS MinOfNumber
FROM MyTable
GROUP BY Customer, Company) AS First
ON MyTable.Customer = First.Customer
AND MyTable.Number = First.MinOfNumber
GROUP BY MyTable.Customer;

Viktor


Quote:

> i have the following results of a query:

> number  customer    company     amount

>   1      smith       msft       100.00 **
>   2      smith       msft       100.00
>   3      smith       msft       100.00
>   8      smith       at&t       250.00 **
>   9      smith       at&t       250.00
>  11      smith       gte        150.00 **
>  12      smith       gte        150.00
>  13      smith       gte        150.00
>   1      jones       msft       500.00
>   2      jones       msft       500.00
>   5      jones       adobe      300.00
>   6      jones       adobe      300.00

>  i want the sum of the amounts of the FIRST occurence
>  of the amount for each company pertaining to the
> particular  customer.

>  from above total sum of amount for smith would be 500.00.

>  i can use the SELECT statement with the FIRST
> (table.field) as  FirstAmount etc... but can i get the sum
> of the amounts as above.

> thanks for your help

> sm



Wed, 30 Mar 2005 07:25:35 GMT  
Dirk Goldga
#4 / 6
 Select first rows.
Ah, this looks better than mine.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> SELECT MyTable.Customer, SUM(MyTable.Amount) AS SumAmount
> FROM MyTable INNER JOIN
> (SELECT Customer, Min(Number) AS MinOfNumber
> FROM MyTable
> GROUP BY Customer, Company) AS First
> ON MyTable.Customer = First.Customer
> AND MyTable.Number = First.MinOfNumber
> GROUP BY MyTable.Customer;

> Viktor



> > i have the following results of a query:

> > number  customer    company     amount

> >   1      smith       msft       100.00 **
> >   2      smith       msft       100.00
> >   3      smith       msft       100.00
> >   8      smith       at&t       250.00 **
> >   9      smith       at&t       250.00
> >  11      smith       gte        150.00 **
> >  12      smith       gte        150.00
> >  13      smith       gte        150.00
> >   1      jones       msft       500.00
> >   2      jones       msft       500.00
> >   5      jones       adobe      300.00
> >   6      jones       adobe      300.00

> >  i want the sum of the amounts of the FIRST occurence
> >  of the amount for each company pertaining to the
> > particular  customer.

> >  from above total sum of amount for smith would be 500.00.

> >  i can use the SELECT statement with the FIRST
> > (table.field) as  FirstAmount etc... but can i get the sum
> > of the amounts as above.

> > thanks for your help

> > sm



Wed, 30 Mar 2005 20:29:01 GMT  
Viktor Umanski
#5 / 6
 Select first rows.
I like JOINs :-), they are fast.

Viktor


Quote:
> Ah, this looks better than mine.

> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com

> (please reply to the newsgroup)



> > SELECT MyTable.Customer, SUM(MyTable.Amount) AS SumAmount
> > FROM MyTable INNER JOIN
> > (SELECT Customer, Min(Number) AS MinOfNumber
> > FROM MyTable
> > GROUP BY Customer, Company) AS First
> > ON MyTable.Customer = First.Customer
> > AND MyTable.Number = First.MinOfNumber
> > GROUP BY MyTable.Customer;

> > Viktor



> > > i have the following results of a query:

> > > number  customer    company     amount

> > >   1      smith       msft       100.00 **
> > >   2      smith       msft       100.00
> > >   3      smith       msft       100.00
> > >   8      smith       at&t       250.00 **
> > >   9      smith       at&t       250.00
> > >  11      smith       gte        150.00 **
> > >  12      smith       gte        150.00
> > >  13      smith       gte        150.00
> > >   1      jones       msft       500.00
> > >   2      jones       msft       500.00
> > >   5      jones       adobe      300.00
> > >   6      jones       adobe      300.00

> > >  i want the sum of the amounts of the FIRST occurence
> > >  of the amount for each company pertaining to the
> > > particular  customer.

> > >  from above total sum of amount for smith would be 500.00.

> > >  i can use the SELECT statement with the FIRST
> > > (table.field) as  FirstAmount etc... but can i get the sum
> > > of the amounts as above.

> > > thanks for your help

> > > sm



Wed, 30 Mar 2005 22:01:42 GMT  
Dirk Goldga
#6 / 6
 Select first rows.
I agree, I just wasn't thinking clearly when I came up with mine.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> I like JOINs :-), they are fast.

> Viktor



> > Ah, this looks better than mine.

> > --
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com

> > (please reply to the newsgroup)



> > > SELECT MyTable.Customer, SUM(MyTable.Amount) AS SumAmount
> > > FROM MyTable INNER JOIN
> > > (SELECT Customer, Min(Number) AS MinOfNumber
> > > FROM MyTable
> > > GROUP BY Customer, Company) AS First
> > > ON MyTable.Customer = First.Customer
> > > AND MyTable.Number = First.MinOfNumber
> > > GROUP BY MyTable.Customer;

> > > Viktor



> > > > i have the following results of a query:

> > > > number  customer    company     amount

> > > >   1      smith       msft       100.00 **
> > > >   2      smith       msft       100.00
> > > >   3      smith       msft       100.00
> > > >   8      smith       at&t       250.00 **
> > > >   9      smith       at&t       250.00
> > > >  11      smith       gte        150.00 **
> > > >  12      smith       gte        150.00
> > > >  13      smith       gte        150.00
> > > >   1      jones       msft       500.00
> > > >   2      jones       msft       500.00
> > > >   5      jones       adobe      300.00
> > > >   6      jones       adobe      300.00

> > > >  i want the sum of the amounts of the FIRST occurence
> > > >  of the amount for each company pertaining to the
> > > > particular  customer.

> > > >  from above total sum of amount for smith would be 500.00.

> > > >  i can use the SELECT statement with the FIRST
> > > > (table.field) as  FirstAmount etc... but can i get the sum
> > > > of the amounts as above.

> > > > thanks for your help

> > > > sm



Thu, 31 Mar 2005 04:06:29 GMT  
 
 Page 1 of 1
  [ 6 post ] 

 Relevant Pages 

1. ListView selects first row

2. select only first rows

3. Select all except the first row

4. Updating the R first rows of a SELECT containing an ORDER BY statement

5. Getting the first row from a select

6. Full row selection in MSHflexgrid does not select first column

7. Visdata app corrupts first col of first row in table

8. Getting row number variable from a selected row in Excel

9. Help: DBgrid, how to select a row and this will select info for other form

10. Populating multiple combo boxes with the first row of query

11. Syntax for referencing the first row from a recordset in an If Then statement

12. REPOST: Detecting First and Last Row in RecordsetClone.

  

 
Powered by phpBB® Forum Software