Access Query equation

Mariyka1

New Member
Joined
Nov 1, 2016
Messages
44
Hello I have the following table formatted like this:

Owner ID Total Count Type Count
ABC 100 Blue 12
ABC 100 Red 11
ABC 100 Green 10
ABD 55 Blue 4
ABD 55 Red 6
ABD 55 Green 10


How would I create a query to count the rest. For example for ABC - there is a total of 100, 33 colors, so the rest would be 67. So for every Owner ID insert a row for the rest?
I really hope this makes sense.
Here's a view of what I'm trying to achieve:

Owner ID Total Count Type Count
ABC 100 Blue 12
ABC 100 Red 11
ABC 100 Green 10
ABC 100 Rest 67
ABD 55 Blue 4
ABD 55 Red 6
ABD 55 Green 10
ABD 55 Rest 35

any guidance on this is much appreciated!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
L

Legacy 456155

Guest
This should do it if I understand you correctly. Replace 'MyTable' with the name of your table.

SQL:
INSERT INTO MyTable ( [Owner ID], [Total Count], [Type], [Count] )
SELECT [Owner ID], [Total Count], "Rest", [Total Count] - Sum([Count])
FROM MyTable
GROUP BY [Owner ID], [Total Count];
 
Last edited by a moderator:

Mariyka1

New Member
Joined
Nov 1, 2016
Messages
44
Thank you so much!! It worked - but I should've included my actual data - I was trying to simplify but realized that I should've mentioned a subtype category. it inserted the rows but added to the subtypes as well. Is there anyway around this. I really appreciate any help, and apologies for the misinformation.



INSERT INTO SFDC_Activities ( [ACT_Off Code], ACT_Cnt, Type, SubType, [Count] )
SELECT [ACT_Off Code], [ACT_Cnt], "Not Contacted", [ACT_Cnt]-Sum([Count]) AS Count1
FROM SFDC_Activities
GROUP BY SFDC_Activities.[ACT_Off Code], [Type], [SubType], [ACT_Cnt];






This should do it if I understand you correctly. Replace 'MyTable' with the name of your table
SQL:
INSERT INTO MyTable ( [Owner ID], [Total Count], [Type], [Count] )
SELECT [Owner ID], [Total Count], "Rest", [Total Count] - Sum([Count])
FROM MyTable
GROUP BY [Owner ID], [Total Count];
SFDC_Activities

ACT_Off CodeACT_CntTypesubtypeCount
ABX
58​
DiscoveryYTD
11​
ABX
58​
Portfolio MeetingYTD
0​
ABX
58​
Present\AnchorYTD
51​
ABX
58​
TotalYTD
51​
ABX
58​
Not ContactedYTD
47​
ABX
58​
Not ContactedYTD
58​
ABX
58​
Not ContactedYTD
7​
ABX
58​
Not ContactedYTD
7​
 
L

Legacy 456155

Guest

ADVERTISEMENT

Ok, I'm back and a bit lost. Show me a before and after example please. Same as in your initial post. :)
 

Mariyka1

New Member
Joined
Nov 1, 2016
Messages
44
Hi yes of course!
so for example below - with a subtype - it will be the same in the group by like Owner ID and Total Count.

Owner ID-Total Count-Type- Subtype- Count
ABC 100 Blue YTD 12
ABC 100 Red YTD 11
ABC 100 Green YTD 10
ABD 55 Blue QTD 4
ABD 55 Red QTD 6
ABD 55 Green QTD 10


Owner ID Total Count Type Count
ABC 100 Blue YTD 12
ABC 100 Red YTD 11
ABC 100 Green YTD 10
ABC 100 Rest YTD 67
ABD 55 Blue QTD 4
ABD 55 Red QTD 6
ABD 55 Green QTD 10
ABD 55 Rest QTD 35

Reply
Report
 
L

Legacy 456155

Guest
You were close.

SQL:
INSERT INTO SFDC_Activities ( [ACT_Off Code], ACT_Cnt, Type, SubType, [Count] )
SELECT [ACT_Off Code], [ACT_Cnt], "Not Contacted", [SubType], [ACT_Cnt]-Sum([Count]) 
FROM SFDC_Activities
GROUP BY [ACT_Off Code], [ACT_Cnt], [SubType];
 

Watch MrExcel Video

Forum statistics

Threads
1,113,878
Messages
5,544,832
Members
410,638
Latest member
Satishj013
Top