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

    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,


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    your example query is invalid but i understand what you meant
    select person, dept, sum(wage) as WageSum
      from table1 
        by person, dept
    union all
    select person, null, sum(wage) 
      from table1 
        by person
        by 1, 2

Posting Permissions

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