Group By on more than one columns

[ShanVel]

Folks,
I need your help on GROUP BY clause. I have been struggling to get his report in a way I wanted for some time now. Here's what I like to do:

I am writing some project related data into a temp table (tblAppendForRskTblReport) by clicking a command button on a form. The data in tblAppendForRskTblReport looks as shown in the excel file tblFile1.xls (attached).

I wanted to make a report in Access 2003 which looks like another excel file rptFile2.xls(attached). As noticed, I wanted group the records into two main groups based on column "fCurrentlyOffered". Then for one group which reads "Yes" (column "fCurrentlyOffered") divided into several sub-groups based on column "chrCategoryID1" like Category A, category B, ....

I tried to use the following query:

SELECT A.chrProjectName, A.chrAqcsPropNum, A.memGuranItem, A.memFuel, A.memOpertgCondn, A.memPred, A.memGuar, A.memMargin, A.memCustReqst, A.memPermitValues, A.memRiskLevel, A.memLDs, A.memBasis, A.memCorrectAction, A.memComments, A.intCategoryRowID, A.chrCategoryID2, A.fCurrentlyOffered, Count(*) AS RecordCount
FROM tblAppendForRskTblReport AS A
GROUP BY A.chrCategoryID1, A.fCurrentlyOffered;

I could get a report based on column "chrCategoryID1" only (see attached sample excel file rptFile3.xls), but don’t know how to separate the rows (as new group called ‘Not Offered’) which reads 'No' under the column "fCurrentlyOffered".

Any attempt I make by modifying the above SQL leads to an error msg as follows:

"You tried to execute a query that does not include the specified expression 'chrProjectName' as part of an aggregate funtion."

I have Count(*) as an aggregate funtion in my SQL but I don't know why it's still complaining?

My question is: can I get the report as I mentioned above (as shown on rptFile2.exls) using SQL or do I need to handle this via VBA?

Can someone help me on this or offer any tips/idea?

PS: Some of the column data may looks like redundant info (normalization), but they are purposely replaced with 'deleted' or 'N/A' words for privacy.

Thanks/Shan.

 

[pdx_man]

This is a report issue, not a query. You are looking to have the data grouped physically on a report, not grouped for aggregate reasons. Look at Sorting and Grouping in reports.
That is what you are looking for, I'm guessing.