Help AND

KJP0422

New Member
Joined
Apr 28, 2016
Messages
21
I have a table that displays country code as a numeric value. I need to summarize for USA - Code 104, CAN - Code 102, MEX - Code 209

All other countries have different codes. I have tried numerous ways to get the count of countries where <> "104" AND <>"102" AND <> "209" and keep getting errors. Can someone please tell me how I can get the count of records where the country does equal the ones I listed


Thanks in Advance
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Are those country codes really number data types? If so, they get rid of the double-quotes around each one (double-quotes are only used for strings, not numbers).
Also, I think you can use the following criteria to get what you want:
Code:
Not In (102,104,209)
 
Upvote 0
Yes they are short text data types. I followed your suggestion and its giving me values but not the correct ones. Thank you for your prompt response. I am impressed!

SELECT tbl_Deletes.Katashiki, tbl_Business_Entity.PlantName, Sum(IIf([tbl_Deletes]![Country]="104",1,0)) AS [USA Qty], Sum(IIf([tbl_Deletes]![Country]="102",1,0)) AS [CAN Qty], Sum(IIf([tbl_Deletes]![Country]="209",1,0)) AS [MEX Qty], Count(tbl_Deletes.Country) AS Other
FROM tbl_Deletes INNER JOIN tbl_Business_Entity ON tbl_Deletes.NAMC = tbl_Business_Entity.BusEntity
GROUP BY tbl_Deletes.Katashiki, tbl_Business_Entity.PlantName
HAVING (((Count(tbl_Deletes.Country)) Not In (102,104,209)));
 
Upvote 0
By putting your Not In () criteria in a HAVING clause you are checking against the COUNT, not against the country numbers.

You need to put your criteria in a WHERE clause.

Not really a fan of all those Sum(IIF()) constructs. Better to use a simple group by query.
 
Upvote 0
SELECT tbl_Deletes.Katashiki, tbl_Business_Entity.PlantName, Sum(IIf([tbl_Deletes]![Country]="104",1,0)) AS [USA Qty], Sum(IIf([tbl_Deletes]![Country]="102",1,0)) AS [CAN Qty], Sum(IIf([tbl_Deletes]![Country]="209",1,0)) AS [MEX Qty], Count(tbl_Deletes.Country) AS Other
FROM tbl_Deletes INNER JOIN tbl_Business_Entity ON tbl_Deletes.NAMC = tbl_Business_Entity.BusEntity
GROUP BY tbl_Deletes.Katashiki, tbl_Business_Entity.PlantName
HAVING (((Count(tbl_Deletes.Country)) Not In (102,104,209)));
And another thing. If those Country codes are numeric like we mentioned earlier, remove the double-quotes from the IIF statements up in the SELECT clause.
 
Upvote 0
Sample query:
Code:
SELECT 
	t1.Katashiki, 
	t2.PlantName, 
	t1.Country
FROM 
	tbl_Deletes t1
	INNER JOIN 
	tbl_Business_Entity t2
	ON t1.NAMC = t2.BusEntity
WHERE 
	t1.Country In ('102','104','209') 
GROUP BY 
	t1.Katashiki, 
	t2.PlantName

UNION

SELECT 
	t1.Katashiki, 
	t2.PlantName, 
	"Other" AS t1.Country
FROM 
	tbl_Deletes t1
	INNER JOIN 
	tbl_Business_Entity t2
	ON t1.NAMC = t2.BusEntity
WHERE 
	t1.Country Not In ('102','104','209') 
GROUP BY 
	t1.Katashiki, 
	t2.PlantName


I'm not sure why you have included the join to tbl_Business_Entity or how that affects the results. It is going to create totals that are grouped by something other than country code only.
 
Upvote 0
I have been pulled off this task for the remainder of today. I will look at this from home over the weekend and let you know what I did. Thank you all your help is very much appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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