Solved

Merge duplicate records in Access however sum total

Posted on 2009-03-29
3
1,549 Views
Last Modified: 2013-11-27
Hello,

I think I have a fairly simple query however have been struggling for a while so hope you can help. I have a query which displays 4 columns. In some instances the columns are exactly the same except for one column ie. the cost. The query may return as follows;

Name | CC | Nom | Cost
A | 100 | 008 | 38475.89
A | 100 | 008 | 22.56

What I would like is the query to return;

Name | CC | Nom | Cost
A | 100 | 008 | 38498.45

I thought the code posted below would work however has yet to merge any of the data.

Any help would be great. Thanks!
SELECT Query8.Type, Query8.dCostCode, Query8.dNominalCode, SUM([Query8.F7]) AS Budget
FROM Query8 LEFT JOIN SummaryTotal ON (Query8.dNominalCode = SummaryTotal.dNominalCode) AND (Query8.dCostCode = SummaryTotal.dCostCode)
GROUP BY Query8.Type, Query8.dCostCode, Query8.dNominalCode, Query8.F7;

Open in new window

0
Comment
Question by:Noobie1
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 24015324
You were very close:

SELECT Query8.Type, Query8.dCostCode, Query8.dNominalCode, SUM([Query8.F7]) AS Budget
FROM Query8 LEFT JOIN SummaryTotal ON (Query8.dNominalCode = SummaryTotal.dNominalCode) AND (Query8.dCostCode = SummaryTotal.dCostCode)
GROUP BY Query8.Type, Query8.dCostCode, Query8.dNominalCode
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24015328
Not sure where SummaryTotal comes in but for the data above, this would give you the result you need.
SELECT Query8.Type, Query8.dCostCode, Query8.dNominalCode, SUM([Query8.F7]) AS Budget
FROM Query8 
GROUP BY Query8.Type, Query8.dCostCode, Query8.dNominalCode;

Open in new window

0
 

Author Closing Comment

by:Noobie1
ID: 31564146
Many Thanks!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question