Combined Records in Query...Again

[Elvis72]

I have a Query:

SELECT [Process Proposals].BUnit_CostCode, [Process Proposals].[Business Unit], [Process Proposals].[Converted From Small Proposal], [Process Proposals].[Licensing Fee], [Combined Currency Converted Statistics].As_Of, [Process Proposals].[Small Proposal], [Process Proposals].[Business Division], [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, [Process Proposals].Lump_Sum, [Process Proposals].Comments, [Process Proposals].Admin_Comments, [Combined Currency Converted Statistics].[SumOfTotal Budget $ Converted], [Combined Currency Converted Statistics].[SumOfTotal Actual $ Converted]
FROM [Process Proposals] INNER JOIN [Combined Currency Converted Statistics] ON [Process Proposals].[Cost Code FY 05] = [Combined Currency Converted Statistics].[Proposal No]
WHERE ((([Process Proposals].[Small Proposal])=No) AND (([Process Proposals].[Date WO Opened]) Between #1/1/2007# And #12/31/2008#))
ORDER BY [Process Proposals].[Cost Code FY 05];


This actually combines multiple records and gives the converted total sums....

The I have this query:

SELECT [All Active Proposals Joined].[Dept No], [All Active Proposals Joined].[Dept Description], [All Active Proposals Joined].[Proposal No], [All Active Proposals Joined].[Proposal Description], [All Active Proposals Joined].[WO Status], [All Active Proposals Joined].[Small Proposal], [All Active Proposals Joined].[Business Unit], [All Active Proposals Joined].[Cost Code FY 05], [All Active Proposals Joined].[Date WO Opened], [All Active Proposals Joined].CLIENT, [All Active Proposals Joined].DESCRIPTION, [All Active Proposals Joined].[PROPOSAL MANAGER], [All Active Proposals Joined].[PROSPECT STATUS], [All Active Proposals Joined].Proposal_Office, [Combined Currency Converted Statistics].As_Of, [Combined Currency Converted Statistics].[SumOfTotal Budget $ Converted], [Combined Currency Converted Statistics].[SumOfTotal Actual $ Converted]
FROM [All Active Proposals Joined] INNER JOIN [Combined Currency Converted Statistics] ON [All Active Proposals Joined].[Proposal No] = [Combined Currency Converted Statistics].[Proposal No];

Which I have mimiced but can't figure out why its not working the same way!~

here is the output sample.

Dept No Dept Description Proposal No Proposal Description WO Status Small Proposal Business Unit Cost Code FY 05 Date WO Opened CLIENT DESCRIPTION PROPOSAL MANAGER PROSPECT STATUS Proposal_Office As_Of SumOfTotal Budget $ Converted SumOfTotal Actual $ Converted
308304089 Noodles 21004 Ragoo Open FALSE Ragoo New Noodle Type Chef Boyardee 23-Dec-07 6250.6 26550.092
395505089 Rice 21077 Lipton Open FALSE Lipton New Rice Blend Mr Tea 23-Dec-07 8000.01 2532.528
395505089 Noodles 21082 JuJu Bee Open FALSE 21082 11-Jan-07 JuJu Bee New Candy Sweet Tooth Abandoned MK 23-Dec-07 19750.8 1132.732
308304089 Noodles 21082 JuJu Bee Open FALSE 21082 11-Jan-07 JuJu Bee New Candy Sweet Tooth Abandoned MK 23-Dec-07 19750.8 1132.732
395505089 Rice 21083 Thin Mint Open FALSE 21083 17-Jan-07 Thin Mint New Cookie Girl Scout Abandoned MK 23-Dec-07 3200.4 0
308113085 Rice 21084 Blue Bell Open FALSE 21084 23-Jan-07 Blue Bell New Ice Cream Molly Post Proposal MK 23-Dec-07 28249.8 20331.26
395505089 Rice 21084 Blue Bell Open FALSE 21084 23-Jan-07 Blue Bell New Ice Cream Molly Post Proposal MK 23-Dec-07 28249.8 20331.26

I know that looks horrible...but if you copy it and paste it into Excel it formats fine...

What I am looking for is to drop off the extra record as the totas are already for all the records per proposal number.

Hope this helps!~

 

[HarleyQuinn]

How are you deciding which record to drop? It's displaying the way it is because you have multiple Dept No's. My advice would either be to not return the Dept No or to decide on what criteria you excplde them.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Elvis72]

Harley - The record is not of importance we can keep any of them, as the total at the end is the same regardless.

The only information that is different is the department number which will not be on the report I am trying to generate.

If I drop a department number off then the records that only have one record would be dropped?

Thanks!~

 

[HarleyQuinn]

If I drop a department number off then the records that only have one record would be dropped?

Can't see why they would, did you try it?

What you should get back (using a modified cut of your original post) is:

Dept Description    Proposal No    Proposal Description    WO Status    Small Proposal    Business Unit    Cost Code FY 05    Date WO Opened    CLIENT    DESCRIPTION    PROPOSAL MANAGER    PROSPECT STATUS    Proposal_Office    As_Of    SumOfTotal Budget $ Converted    SumOfTotal Actual $ Converted
Noodles    21004    Ragoo    Open    FALSE                Ragoo    New Noodle Type    Chef Boyardee            23-Dec-07    6250.6    26550.092
Rice    21077    Lipton    Open    FALSE                Lipton    New Rice Blend    Mr Tea            23-Dec-07    8000.01    2532.528
Noodles    21082    JuJu Bee    Open    FALSE        21082    11-Jan-07    JuJu Bee    New Candy    Sweet Tooth    Abandoned    MK    23-Dec-07    19750.8    1132.732
Rice    21083    Thin Mint    Open    FALSE        21083    17-Jan-07    Thin Mint    New Cookie    Girl Scout    Abandoned    MK    23-Dec-07    3200.4    0
Rice    21084    Blue Bell    Open    FALSE        21084    23-Jan-07    Blue Bell    New Ice Cream    Molly    Post Proposal    MK    23-Dec-07    28249.8    20331.26]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.