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