Combined Records in Query 1

[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!~

 

[HarleyQuinn]

why its not working the same way!

Could you elaborate on that slightly? How is it not working the same way? What does it do compared to what you're expecting?

Cheers

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]

Sooo...sorry...

Let's say I have three records for proposal number 23063.

Well the first query shows one record but totals all three.

The second query shows all three and gives the same total three times.

Thanks!~

 

[HarleyQuinn]

Are all of the rows displaying exactly the same data for ALL fields in your second query?

If they do you can use SELECT DISTINCT instead of SELECT.

If they're not then post back (you could even show us a sample of your output from the second query if you wanted).

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!

Worked great!~

thanks!~

 

[Elvis72]

Harley...thought it worked...it didn't...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]

Glad I could help, thanks for the star

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.