Results 1 to 2 of 2
Thread: subquery hell (access 97)
2002-06-19, 08:36 #1
- 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>
2002-06-19, 08:52 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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.