Summing data while eliminating duplicates

[NickC]

Hello all,

So I am having an issue finding any way to do this in Access and have checked all the forums on this site and several others. If anyone has an idea of how I might do this it would be greatly appreciated. I currently have a table that I will simplify under this for you guys to see. So what I am trying to do with that table is get a sum of all assets, a sum of all Customers, and a sum of all Employees only counting each Comp ID once. Each company that we deal with carries multiple of our products. So as you can see I currently have it in the table where it shows the different data for each product over 2011, 2012, and 2013. Obviously for this I cannot just sum the entire column, as I would have the same company summed multiple times. The thing that is making this challenging for me is that I need it summed for each individual year (2011, 2012, 2013), with the end result being a total number of employees, assets, and customers. I was thinking that to do this I would need a Sumif formula with several other formulas inside of it, but cannot figure out what exactly I would need to do. If anyone has an idea of how I could do this it would be awesome. I know that if I took it into excel I would be able to do it, but we have over 7000 different customers, each having anywhere from 3-20 different products, and for each of the years 2011-2013... so the data is just too massive to try to convert and manipulate in excel.

The reason that I am hoping to get a count of their customers, assets, and employees is so that we could then take the info from individual customers and find out that particular customer's portion of the total assets, customers, or employees. I guess the mock table that I made doesn't keep the formatting when I add spaces, so imagine that each pair of l l is a column and match that with the columns above please.

I hope this makes sense to anyone reading, if not I can try to explain better and answer any questions you might have. Thanks for taking time to read this and have a great day everyone.

I also need another column that has the 2012 and 2013 results

l Comp ID l Year l Prod Fam l Prod Group l Prod Code l Assets l Customers l Employees l Assets for 2011 l Customers for 2011 l Employees for 2011 l
l 1101232 l 2011 l Family A l Group A l Prod A l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group A l Prod A l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group A l Prod A l 51000 l 39 l 25 l
l 1101232 l 2011 l Family A l Group C l Prod B l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group C l Prod B l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group C l Prod B l 51000 l 39 l 25 l
l 1101232 l 2011 l Family B l Group B l Prod C l 59000 l 33 l 28 l
l 1101232 l 2012 l Family B l Group B l Prod C l 55000 l 36 l 23 l
l 1101232 l 2013 l Family B l Group B l Prod C l 51000 l 39 l 25 l
l 1000001 l 2011 l Family C l Group A l Prod A l 83000 l 215 l 72 l
l 1000001 l 2012 l Family C l Group A l Prod A l 86000 l 222 l 74 l
l 1000001 l 2013 l Family C l Group A l Prod A l 81000 l 226 l 78 l
l 1000001 l 2011 l Family A l Group A l Prod A l 83000 l 215 l 72 l


l 1000001 l 2012 l Family A l Group A l Prod A l 86000 l 222 l 74 l
l 1000001 l 2013 l Family A l Group A l Prod A l 81000 l 226 l 78 l
l 1000001 l 2011 l Family A l Group A l Prod A l 83000 l 215 l 72 l
l 1000001 l 2012 l Family A l Group A l Prod A l 86000 l 222 l 74 l
l 1000001 l 2013 l Family A l Group A l Prod A l 81000 l 226 l 78 l

[ItsMe]

Perhaps you can use the query builder to create a Totals query. I would start there and worry about GROUP BY and WHERE criteria second. Isolate the fields that will be the minimum needed to get the SUM. Then add fields for the WHERE criteria. Then add fields to build a dataset that the User can understand (intuitive).

As you start with your SUM, adding fields will cause your query to break. When it breaks, move one step back and save that query to use as a subquery. Make sure your subquery has a Key field to JOIN to its parent.

[recyan]

A small question, about the data :

Are the data below the way you have given i.e. Duplicate Records having the same values across all fields ?

 Comp ID
 Year
 Prod  Fam
 Prod  Group
 Prod  Code
 Assets
 Customers
 Employees

1000001
2011
 Family A
 Group A
 Prod A
83000
215
72

1000001
2011
 Family A
 Group A
 Prod A
83000
215
72





Comp ID
Year
Prod  Fam
Prod  Group
Prod  Code
Assets
Customers
Employees

1000001
2012
Family A
Group A
Prod A
86000
222
74

1000001
2012
Family A
Group A
Prod A
86000
222
74






Comp ID
Year
Prod  Fam
Prod  Group
Prod  Code
Assets
Customers
Employees

1000001
2013
Family A
Group A
Prod A
81000
226
78

1000001
2013
Family A
Group A
Prod A
81000
226
78

Thanks

[hapm]

As ItsMe suggested create subqueries for each of your sums and join them using the year column at the end.