Group By/Sum Query Help Needed!

Ok, here's what I've got.

I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.

I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.

IIf([ProdCode],[ProdCode],[WorkCode])

Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.

Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.

Here's the SQL from the 'TotalHoursPerCode' query.

SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs
FROM Main
GROUP BY Main.Code;

Any ideas??

Thanks!


http://i45.photobucket.com/albums/f73/VolcomGrrrl79/access.jpg