Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    foxpro 206/access based query to get subtoal and totals of groups

    D/friends

    i need a query (foxpro 26 based or access based) to get subtotal and total s of row.. e.g

    item field1 field2 field3

    a
    a
    a
    subtotal subtotal subtotal


    b
    b
    b

    subtotal subtotal subtotal

    total total total

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I wouldn't do subtotals with a query. I'd build the query to feed the line items of the report, then build the subtotaling into the report itself (using groups).

    -PatP

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    ok dear plz give the query

  4. #4
    Join Date
    Apr 2008
    Posts
    3
    Quote Originally Posted by Pat Phelan
    I wouldn't do subtotals with a query. I'd build the query to feed the line items of the report, then build the subtotaling into the report itself (using groups).

    -PatP
    plz give thr query

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Code:
    SELECT item, field1, field2, field3
       FROM myTable
       WHERE ?
    You'll need to change the table and column names to match your existing schema, and replace the question mark with the appropriate criteria for your reporting needs.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access doesn't have anything native that will get you that. You can "spoof" it with three union queries. SQL Server has some syntax for this (Pat - I'm thinking this is a requirement met by CUBE). No idea re Foxpro.

    If this is for a report then it is A LOT easier and more efficient to do this in the report - forget the query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Given the two applications available (Access and FoxPro) I would guess that this is a reporting need; in which case they can both do it very eeasily if you simply select the fields and group them on the report!

    field1, field2 and field3 can go in the report deail section and then you can use aggregate expressions in the group section or footer sections as necessay.

    In fact, yo may have to use a group by clause in your select statement...
    George
    Home | Blog

  8. #8
    Join Date
    May 2008
    Posts
    1
    Hi,

    You can use sum() function in foxpro to accomplish
    In this example you are adding the values in column in_stock and on_order for
    all the records and saving into two variables respectively.
    ex:

    SUM in_stock, on_order

    TO gnInStock, gnOnOrder


    * Printing to the screen the calculated values in the variables
    CLEAR

    ?

    ? 'Total in stock : ', gnInStock && Displays 3119.00

    ? 'Total on order: ', gnOnOrder && Displays 780.00

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •