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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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:
Upvote 0
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​
 
Upvote 0
Ok, I'm back and a bit lost. Show me a before and after example please. Same as in your initial post. :)
 
Upvote 0
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
 
Upvote 0
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];
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top