cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jmichiels
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
TsysIPLocations isn't linked to any other tables. It should be linked to tblAssets as explained here.

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
TsysIPLocations isn't linked to any other tables. It should be linked to tblAssets as explained here.