NON-Programmer/Dumb Question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: NON-Programmer/Dumb Question

  1. #1
    BWN Guest

    NON-Programmer/Dumb Question


    I am using a FoxPro 2.5 dbf containing multiple records indexed on ID number.
    Have field containing quarterly wages and another field containing the year/quarter
    in format YYYYQ. Have sorted on ID and on matching year/quarter (Descending).
    Want to SUM wages for YR/Quarter using SUBSTR(quarter,1,4) = "1999". Then
    place this SUM into new field for ANNUAL wage. Desire to do this for each
    ID, some of which will have ZERO wages in a given quarter. Help me with
    the Loop concept and placement of this new ANNUAL SUM into the new field
    for each record of a given ID.

    Thanks.

  2. #2
    Barbara Peisch Guest

    Re: NON-Programmer/Dumb Question

    I would create a sum for each employee with a SQL Select statement, then
    loop through that file, creating a new record in the original file. I'm not
    sure what you want to put in the year/quarter field for the annual sum,
    though. Here's the code (untested) to get the sum for each employee and
    create a new record in the original table:

    SELECT ID, SUM(Wage) AS AnnualWage ;
    FROM WageData ;
    WHERE LEFT(YrQtr,4) = "1999" ;
    INTO CURSOR TempWages
    GROUP BY ID

    SCAN
    INSERT INTO WageData (ID, YrQtr, Wage) VALUES (TempWages.ID, "1999 ",
    TempWages.AnnualWage)
    ENDSCAN

    --


    -BP
    www.peisch.com

    "BWN" <bneal@flash.net> wrote in message news:3a637d5f$1@news.devx.com...
    >
    > I am using a FoxPro 2.5 dbf containing multiple records indexed on ID

    number.
    > Have field containing quarterly wages and another field containing the

    year/quarter
    > in format YYYYQ. Have sorted on ID and on matching year/quarter

    (Descending).
    > Want to SUM wages for YR/Quarter using SUBSTR(quarter,1,4) = "1999".

    Then
    > place this SUM into new field for ANNUAL wage. Desire to do this for each
    > ID, some of which will have ZERO wages in a given quarter. Help me with
    > the Loop concept and placement of this new ANNUAL SUM into the new field
    > for each record of a given ID.
    >
    > Thanks.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center