Query showing duplicate values

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

 

[KARL DEWEY]

Your data example does not match the SQL. Example has three fields but SQL
has seven.

 

[Stacey Crowhurst]

Karl:

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

Stacey