No results returned SUM IIF SUM SQL Query

[Mari111]

I have an Excel report that I am trying to re-create in MS Access with a SQL query. I have used a IIF SUM function but it is not returning the expected results ("YES") in particular fields, instead just returning blank cells. I have checked the SQL tables and based on the raw data, we should expect some "YES" results. What is wrong with my script?
I have not used this IIF SUM function before, so I need some help. The SQL query below has been re-constructed with example table and field names.


SELECT DISTINCT [Table 1].[Paint ID], [Table 1].[Paint Colour], [Table 1].[Production Date], [Table 1].[Price], [Table 1].[VAT], [Table 1].[Total Units], IIF(Sum(IIF([Table 2].[Company Name] IN("Paintbox Ltd", "ColourSplash plc", "HomeDIY"),1,0))>0,"YES",NULL) AS UK Sellers, IIF(Sum(IIF([Table 2].[Company Name] IN("BrightWalls", "PerfectHome", "PaintIt"),1,0))>0,"YES",NULL) AS Europe Sellers, IIF(SUM(IIF([Table 2].[Company Name]="Habari",1,0))>0,"YES",NULL) as Africa Sellers, IIF(SUM(IIF([Table 2].[Company Name]="Malay Paint",1,0))>0,"YES",NULL) as Asia Sellers FROM [Tale 1] LEFT JOIN [Table 2] ON [Table 1].[Paint ID] = [Table 2].[Paint ID] GROUP BY [Table 1].[Paint ID], [Table 1].[Paint Colour], [Table 1].[Production Date], [Table 1].[Price], [Table 1].[VAT], [Table 1].[Total Units];

 

[GinaWhipp]

Since it looks like you want to check if something exists in the table then I would think DCount() would work better, especially because you can't Sum() on TEXT.

 

[isladogs]

First of all this query will be very slow due to the many clauses you are using.
The way to get something like this working is to test one section at a time.

You have an error in the JOIN. It should be [Table 1] not [Tale 1]
It would also make your life far easier if there were no spaces in table or field names

However it can and should be simplified
Your IIF SUM IIF can be reduced to IIf(Count(...))>0"Yes",Null). However DCount may be better as Gina suggests

Try this as a starting point. It can probably be improved further

SELECT DISTINCT [Table 1].[Paint ID],[Table 1].[Paint Colour],[Table 1].[Production Date],[Table 1].[Price], [Table 1].[VAT], [Table 1].[Total Units], IIF(Count([Table 2].[Company Name] IN("Paintbox Ltd", "ColourSplash plc", "HomeDIY"))>0,"YES",NULL) AS UK Sellers, IIF(Count([Table 2].[Company Name] IN("BrightWalls", "PerfectHome", "PaintIt"))>0,"YES",NULL) AS Europe Sellers, IIF(Count([Table 2].[Company Name]="Habari"))>0,"YES",NULL) as Africa Sellers, IIF(Count([Table 2].[Company Name]="Malay Paint"))>0,"YES",NULL) as Asia Sellers    
FROM [Table 1] LEFT JOIN [Table 2] ON [Table 1].[Paint ID] = [Table 2].[Paint ID]    
GROUP BY [Table 1].[Paint ID], [Table 1].[Paint Colour], [Table 1].[Production Date], [Table 1].[Price], [Table 1].[VAT], [Table 1].[Total Units];

Note that this is air code. i'm answering on a tablet so there may be bracketing errors above

 

[Mari111]

Thank you both, the COUNT function worked.

 

[isladogs]

Glad you have a solution. Hope its not too slow in use

 

[Pat Hartman]

I would suggest a change in your table design so that in the vendor table you mark each vendor as to which category they will be counted in. i.e. AfricaSellers, UKSellers, AsiaSellers, etc. Then the query becomes a simple crosstab with no nested IIf()s.