Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Cornwall, England
    Thanked 0 Times in 0 Posts

    subquery hell (access 97)

    I have to create a report for our department managers to list staff time (and therefore costs) that has been incurred by staff that work in other departments.
    ie i work in the ICT Dept , John works in the Cornwall Dept and Mary works in the Devon Dept.
    we all charge time to project codes, these codes have a prefix which relates to the department.
    therefore there are codes 113100 - ICT Coffee Making, 101100 - Cornwall coffee making and 102100 Devon Coffee making.
    accounts need to have a report that lists , for the cornwall department manager, all people who have charged time to their department, eg Mary doing 4 hours on project 102100 and me doing 1 hours work on project 102100.

    jeez, that took some time, hope you havent given up on me yet....? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    i have created a report that gets this data together but , having created a report on the query i get an error relating to my grouping in the report:- "Multi-level GROUP BY clause is not allowed in a subquery."

    is there a way around this without re-writing the query , it has taken several hours to get it sorted.
    would it be worth posting the SQL ?

    TIA <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: subquery hell (access 97)

    Often, it's possible to do most or all of the grouping in the report itself instead of in a query. But we'd have to have more info to be able to help you.

    You can post the SQL. If it's very long (I hope not...), you might save it in a text file and attach that.

    Or, if it's possible to create a stripped-down version of the database (just the table(s) with a few records, sensitive data removed or replaced by dummy values, the query and the report), you can compact, zip and attach it.

Posting Permissions

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