Solved

Merge duplicate records in Access however sum total

Posted on 2009-03-29
3
1,550 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

733 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