Chart: Counts of status based on IP location

[jmichiels]

Hello,

I want to make a chart where I can see counts of status (Stock, Loan, ..) have in every country.
Example of result:
STOCK
5 Belgium
3 Germany
.
.
.

My query that I tried to make but I get counts of total from EMEA and not country (ip locations)

Select Top 1000000 tblState.Statename,
Count(tblAssets.AssetID) As Count,
Case tsysIPLocations.IPLocation When 'PAPanama' Then 'PAP'
When 'BEMechelen, overall subet' Then 'Belgium'
When 'BEMechelen, clients' Then 'Belgium' When 'BEMechelen' Then 'Belgium'
End As Country,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State,
tsysIPLocations
Group By tblState.Statename,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblAssetCustom.Manufacturer
Having tblState.Statename Like 'stock' And tblAssets.Domain = 'cochlear' And
tblAssetCustom.Manufacturer = 'lenovo'
Order By tblState.Statename

[Susan_A · Accepted solution]

TsysIPLocations isn't linked to any other tables. It should be linked to tblAssets as explained here.