# Access Query equation

#### Mariyka1

##### New Member
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
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
I really appreciate any help, and apologies for the misinformation.

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

L

#### Legacy 456155

##### Guest

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

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

Replies
2
Views
2K
Replies
15
Views
322
Replies
13
Views
2K
Replies
1
Views
119
Replies
4
Views
62