Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: SQL Syntax Problem

    Hi All,

    I am having a little problem with an SQL statement.
    The development being done is in Access not SQL Server

    The table looks something like this

    id Person Dept WageSegment
    ----------- ---------- ---------- ----------------------
    1 John Veg 10
    2 John Veg 5
    3 John Veg 1
    4 Mary Cash 15
    5 Mary Cash 4
    6 Mary Cash 2

    I want to return each individual Person, department and wage record and then the total for each individual combination of person and department. I can do this in T-SQL as follows:



    Using 'select person, dept, sum(wage) as Wage from table1
    group by person, dept,wage with rollup'
    I can achieve the following results:

    person dept Wage
    ---------- ---------- -----------
    John Veg 1
    John Veg 5
    John Veg 10
    John Veg 16
    John NULL 16
    Mary Cash 2
    Mary Cash 4
    Mary Cash 15
    Mary Cash 21
    Mary NULL 21
    NULL NULL 37

    This gives me the individual records and the total for each individual/departmental combination. However as far as I know rollup is not available in Access.

    Does anyone have any idea of an alternative syntax which is Access friendly and will return all the relevant data?

    Any help appreciated,

    JJC

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your example query is invalid but i understand what you meant
    Code:
    select person, dept, sum(wage) as WageSum
      from table1 
    group 
        by person, dept
    union all
    select person, null, sum(wage) 
      from table1 
    group 
        by person
    order 
        by 1, 2
    rudy
    http://r937.com/

Posting Permissions

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