Access Sum rows with duplicate fields

bassmandan · Jan 29, 2015

Hi guys,

I've searched high and low to find an answer to this but have so far been unsuccessful.

I have a query which shows skills against certain product categories. It is removing duplicates, but I still end up with a selection of duplicate rows. I would like to get rid of these by summing the values of each set of duplicates but am not sure how. I've posted a screen capture of a sample query (imgur: the simple image sharer) - in this example I would like to end up with three rows, one for storage showing 1,1,1, one for virtualisation showing 0,1,1 and one for wireless showing 1,2,2.

Can I do this using another query, or do I need to use some VBA?

Thanks for any help!

Kyle123 · Jan 29, 2015

what's the sql?

bassmandan · Jan 29, 2015

SQL behind the query looks like this:

SELECT DISTINCT tblProducts.Category, IIf([tblSkillLevelsDesign].[DesignLevel]="None","0","1") AS Design, IIf([tblSkillLevelsInstallation].[InstallationLevel]="None","0","1") AS Installation, IIf([tblSkillLevelsSupport].[SupportLevel]="None","0","1") AS Support
FROM tblSkillLevelsInstallation INNER JOIN (tblSkillLevelsSupport INNER JOIN (tblSkillLevelsDesign INNER JOIN (tblProducts INNER JOIN (tblPeople INNER JOIN tblSkills ON tblPeople.ID = tblSkills.FullName) ON tblProducts.ID = tblSkills.Product) ON tblSkillLevelsDesign.ID = tblSkills.DesignLevel) ON tblSkillLevelsSupport.ID = tblSkills.SupportLevel) ON tblSkillLevelsInstallation.ID = tblSkills.InstallationLevel;

Kyle123 · Jan 29, 2015

Maybe:

SELECT category,
 SUM(design) AS Design,
 SUM(installation) AS Installation,
 SUM(support) AS Support
 FROM (SELECT tblproducts.category,
 Iif([tblskilllevelsdesign].[designlevel] = "none", 0, 1) AS Design,
 Iif([tblskilllevelsinstallation].[installationlevel] = "none", 0, 1) AS Installation,
 Iif([tblskilllevelssupport].[supportlevel] = "none", "0", "1") AS Support
 FROM tblskilllevelsinstallation
 INNER JOIN (tblskilllevelssupport
 INNER JOIN (tblskilllevelsdesign
 INNER JOIN (tblproducts
 INNER JOIN (tblpeople
 INNER JOIN tblskills
 ON tblpeople.id = tblskills.fullname)
 ON tblproducts.id = tblskills.product)
 ON tblskilllevelsdesign.id = tblskills.designlevel)
 ON tblskilllevelssupport.id = tblskills.supportlevel)
 ON tblskilllevelsinstallation.id = tblskills.installationlevel) AS [Raw]
 GROUP BY category


Syntax may be slightly off since I don't use access - the principle will be the same though

bassmandan · Feb 2, 2015

Thanks Kyle. I get a circular reference error 'caused by alias Design in query definition's SELECT list'. If I change the aliases then I don't get the error, but the query doesn't work. Any chance you could give me a rough explanation of what the different sections of SQL are doing (I've no experience with SQL but I can pick that kind of thing up pretty well) and I can try and troubleshoot?

Kyle123 · Feb 2, 2015

Try removing the Aliases from the other most query (ie SUM(design) AS Design remove the As Design). Essentially it is just your query, but grouping by the category (so you only get one line) then summing each of the 1s in the rows. So you get one line per category with a sum of the 1s

bassmandan · Feb 2, 2015

Thanks! I'll have a play.

bassmandan · Feb 2, 2015

Kyle - that's absolutely fantastic, it works perfectly without 'As xxxxx'. Thank you so much for your help!

Dan

Access Sum rows with duplicate fields

bassmandan

New Member

Excel Facts

Kyle123

Well-known Member

bassmandan

New Member

Kyle123

Well-known Member

bassmandan

New Member

Kyle123

Well-known Member

bassmandan

New Member

bassmandan

New Member

Similar threads

Forum statistics

Share this page

Access Sum rows with duplicate fields

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock