Query showing duplicate values

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have the following situation. I have a certain set of values (ex:
account code list) that I need to show. I have a query (query 1) to show me
these values. A secondary query (query 2) gathers all of a certain kind of
expense for each value. The third query (SQL below) attempts to combine the
two. It is currently, giving me duplicate values. In this exaples columns
1 and 2 are derived from query 1 and column 3 comes from query 2.

For example it reads:
Newspaper Ads $500 $25
Radio Ads $750 $25
Magazine Ads $900 $25

But I want it to read:
Newspaper Ads $500 $0
Radio Ads $750 $25
Magazine Ads $900 $0

SELECT [Q- Cash Flow_Committed Costs 01].[CCP No], [Q- Cash Flow_Committed
Costs 01].[Account Code], [Q- Cash Flow_Committed Costs 02].Type, [Q- Cash
Flow_Committed Costs 01].[Budget Code], [Q- Cash Flow_Committed Costs
01].[Budget Code Desc], Sum([Q- Cash Flow_Committed Costs 01].[Line Amount])
AS [SumOfLine Amount], Sum(Nz([Q- Cash Flow_Committed Costs 02]![Committed
Costs TD],0)) AS [Non Contract Costs]
FROM [Q- Cash Flow_Committed Costs 01] INNER JOIN [Q- Cash Flow_Committed
Costs 02] ON ([Q- Cash Flow_Committed Costs 01].[CCP No] = [Q- Cash
Flow_Committed Costs 02].[CCP No]) AND ([Q- Cash Flow_Committed Costs
01].[Account Code] = [Q- Cash Flow_Committed Costs 02].[Account Code])
GROUP BY [Q- Cash Flow_Committed Costs 01].[CCP No], [Q- Cash Flow_Committed
Costs 01].[Account Code], [Q- Cash Flow_Committed Costs 02].Type, [Q- Cash
Flow_Committed Costs 01].[Budget Code], [Q- Cash Flow_Committed Costs
01].[Budget Code Desc];

I don’t know if anyone can help. But if you want to try I’ll be grateful!
 
Your data example does not match the SQL. Example has three fields but SQL
has seven.
 
Karl:

I just deleted all my queries and started from scratch. It works ok now.
Thanks, though!

Stacey
 
Back
Top