Add Group By Statement

[Elvis72]

I have this query:

SELECT [Process Proposals].BUnit_CostCode, [Process Proposals].[Small Proposal], [Process Proposals].[Business Unit], [Process Proposals].[Business Division], [Process Proposals].[Business Sector], [Process Proposals].[Business SubSector], [Process Proposals].Valid_Box, [Process Proposals].Business_Plant_Type, [Process Proposals].[Cost Code FY 05], [Process Proposals].[FY 05 BUnit], [Process Proposals].[FY 05 BUnit 2nd], [Process Proposals].[FY 05 BUnit 3rd], [Process Proposals].Contract_Type, [Process Proposals].[FY 04 BUnit], [Process Proposals].[Cost Code FY 04], [Process Proposals].[MSCRM NUM], [Process Proposals].CLIENT, [Process Proposals].DESCRIPTION, [Process Proposals].[PROPOSAL MANAGER], [Process Proposals].[PROSPECT STATUS], [Process Proposals].Proposal_Budget, [Process Proposals].[Proposal_Budget_Date Closed], [Process Proposals].Proposal_Budget_Closure_Request_By, [Process Proposals].Scope, [Process Proposals].Pricing_Approval, [Process Proposals].Duration_months, [Process Proposals].Proposal_Office, [Process Proposals].Execution_Office, [Process Proposals].Hours, [Process Proposals].Revenue, [Process Proposals].Royalty, [Process Proposals].[Gross Margin], [Process Proposals].Budget_Cost, [Process Proposals].Original_Budget_Cost, [Process Proposals].Budget_Adj_1_Date_Approved, [Process Proposals].Budget_Adj_1_Amount, [Process Proposals].Budget_Adj_2_Date, [Process Proposals].Budget_Adj_2_Amount, [Process Proposals].Budget_Adj_3_Date, [Process Proposals].Budget_Adj_3_Amount, [Process Proposals].Budget_Adj_4_Date, [Process Proposals].Budget_Adj_4_Amount, [Process Proposals].TIC, [Process Proposals].[Date WO Opened], [Process Proposals].[Proposal Submitted Date], [Process Proposals].ITB_Received, [Process Proposals].PNP_Approved, [Process Proposals].PNP_Approved_Box, [Process Proposals].BNB_Review, [Process Proposals].BNB_Review_Box, [Process Proposals].Bid_Bond_Required, [Process Proposals].Bid_Bond_Received, [Process Proposals].Risk_Review, [Process Proposals].Proposal_Team_Review, [Process Proposals].Business_Line_Review, [Process Proposals].Div_Mgmnt_Review, [Process Proposals].Corp_Mgmnt_Review, [Process Proposals].Tech_Proposal_Due, [Process Proposals].Comm_Proposal_Due, [Process Proposals].Bid_Validity, [Process Proposals].Bid_Validity_Expires, [Process Proposals].Award_Date, [Process Proposals].[JIF Date], [Process Proposals].Job_Number, [Process Proposals].BNB_Required, [Process Proposals].Risk_Review_Reporting, [Process Proposals].Outcome, [Process Proposals].Archive, [Combined Currency Converted Statistics Query].As_Of, [Combined Currency Converted Statistics Query].[Proposal No], [Combined Currency Converted Statistics Query].[SumOfTotal Budget $ Converted], [Combined Currency Converted Statistics Query].[SumOfTotal Actual $ Converted], [Combined Currency Converted Statistics Query].[SumOfExpense Actual $ Converted], [Combined Currency Converted Statistics Query].[SumOfExpense Budget $ Converted], [Combined Currency Converted Statistics Query].[SumOfTotal Labor Budget $ Converted], [Combined Currency Converted Statistics Query].[SumOfTotal Labor Actual $ Converted], [Combined Currency Converted Statistics Query].[2008 Fiscal Year Activity], [Combined Currency Converted Statistics Query].[Total Budget Remaining], [Process Proposals].Lump_Sum, [Process Proposals].Comments, [Process Proposals].Admin_Comments, [Process Proposals].[Converted From Small Proposal], [Process Proposals].[Licensing Fee], [Combined Currency Converted Statistics Query].[Total MHR Remaining], [Combined Currency Converted Statistics Query].[SumOfTotal Budget MHR], [Combined Currency Converted Statistics Query].[SumOfTotal Actual MHR]
FROM [Process Proposals] INNER JOIN [Combined Currency Converted Statistics Query] ON [Process Proposals].[Cost Code FY 05] = [Combined Currency Converted Statistics Query].[Proposal No]
WHERE ((([Process Proposals].[Date WO Opened]) Is Null Or ([Process Proposals].[Date WO Opened]) Between #9/1/2007# And #8/31/2008#) AND (([Combined Currency Converted Statistics Query].[2008 Fiscal Year Activity]) Is Not Null))

I need to Group it by the [Process Proposals].[Cost Code FY 05] Field.

I cannot figure out how to do this with out an error?

 

[lespaul]

you know, it might be a little easier if you provide some scaled down sample data and what your expected results would be from that data (it's easier to see what you need when you show us a "picture" of what's going on).

Additionally, if you use an Alias for your table names (and check out the Process TGML link below for more on the [ignore]

[/ignore] tags) it makes your queries (and posts) much easier to read:

SELECT P2.BUnit_CostCode, P2.[Small Proposal], P2.[Business Unit], P2.[Business Division], P2.[Business Sector], P2.[Business SubSector], P2.Valid_Box, P2.Business_Plant_Type, P2.[Cost Code FY 05], P2.[FY 05 BUnit], P2.[FY 05 BUnit 2nd], P2.[FY 05 BUnit 3rd], P2.Contract_Type, P2.[FY 04 BUnit], P2.[Cost Code FY 04], P2.[MSCRM NUM], P2.CLIENT, P2.DESCRIPTION, P2.[PROPOSAL MANAGER], P2.[PROSPECT STATUS], P2.Proposal_Budget, P2.[Proposal_Budget_Date Closed], P2.Proposal_Budget_Closure_Request_By, P2.Scope, P2.Pricing_Approval, P2.Duration_months, P2.Proposal_Office, P2.Execution_Office, P2.Hours, P2.Revenue, P2.Royalty, P2.[Gross Margin], P2.Budget_Cost, P2.Original_Budget_Cost, P2.Budget_Adj_1_Date_Approved, P2.Budget_Adj_1_Amount, P2.Budget_Adj_2_Date, P2.Budget_Adj_2_Amount, P2.Budget_Adj_3_Date, P2.Budget_Adj_3_Amount, P2.Budget_Adj_4_Date, P2.Budget_Adj_4_Amount, P2.TIC, P2.[Date WO Opened], P2.[Proposal Submitted Date], P2.ITB_Received, P2.PNP_Approved, P2.PNP_Approved_Box, P2.BNB_Review, P2.BNB_Review_Box, P2.Bid_Bond_Required, P2.Bid_Bond_Received, P2.Risk_Review, P2.Proposal_Team_Review, P2.Business_Line_Review, P2.Div_Mgmnt_Review, P2.Corp_Mgmnt_Review, P2.Tech_Proposal_Due, P2.Comm_Proposal_Due, P2.Bid_Validity, P2.Bid_Validity_Expires, P2.Award_Date, P2.[JIF Date], P2.Job_Number, P2.BNB_Required, P2.Risk_Review_Reporting, P2.Outcome, P2.Archive, C.As_Of, C.[Proposal No], C.[SumOfTotal Budget $ Converted], C.[SumOfTotal Actual $ Converted], C.[SumOfExpense Actual $ Converted], C.[SumOfExpense Budget $ Converted], C.[SumOfTotal Labor Budget $ Converted], C.[SumOfTotal Labor Actual $ Converted], C.[2008 Fiscal Year Activity], C.[Total Budget Remaining], P2.Lump_Sum, P2.Comments, P2.Admin_Comments, P2.[Converted From Small Proposal], P2.[Licensing Fee], C.[Total MHR Remaining], C.[SumOfTotal Budget MHR], C.[SumOfTotal Actual MHR]
FROM [Process Proposals] P2 
INNER JOIN [Combined Currency Converted Statistics Query] C ON P2.[Cost Code FY 05] = C.[Proposal No]
WHERE (((P2.[Date WO Opened]) Is Null Or (P2.[Date WO Opened]) Between #9/1/2007# And #8/31/2008#) AND ((C.[2008 Fiscal Year Activity]) Is Not Null))

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com