Access Query equation

[Mariyka1]

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!

 

[Legacy 456155]

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

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];

 

[Mariyka1]

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

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 Code	ACT_Cnt	Type	subtype	Count
ABX	58​	Discovery	YTD	11​
ABX	58​	Portfolio Meeting	YTD	0​
ABX	58​	Present\Anchor	YTD	51​
ABX	58​	Total	YTD	51​
ABX	58​	Not Contacted	YTD	47​
ABX	58​	Not Contacted	YTD	58​
ABX	58​	Not Contacted	YTD	7​
ABX	58​	Not Contacted	YTD	7​

 

[Legacy 456155]

I really appreciate any help, and apologies for the misinformation.

Not a problem. I'll try to get back to this later today.

 

[Legacy 456155]

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

 

[Mariyka1]

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

 

[Legacy 456155]

You were close.

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];