Exclude certain records

[adamlaing]

I currently have a union query that combines my data by year and Region:

SELECT
[Year], "UK" as Region, UK as Data from [4 Premium Analysis]
UNION SELECT [Year], "US", US from [4 Premium Analysis]
UNION SELECT [Year], "Other North America", Other_north_america from [4 Premium Analysis]
UNION SELECT [Year], "Western Europe", Western_europe from [4 Premium Analysis]
UNION SELECT [Year], "Eastern Europe", Eastern_europe from [4 Premium Analysis]
UNION SELECT [Year], "Asia", Asia from [4 Premium Analysis]
UNION SELECT [Year], "Australia & New Zealand", Australia_new_zealand from [4 Premium Analysis]
UNION SELECT [Year], "Bermuda", Bermuda from [4 Premium Analysis]
UNION SELECT [Year], "Rest of World", Rest_world from [4 Premium Analysis];

I then call this Union query in a crosstab query to get a summary of premiums by year and region for the past 5 years:

TRANSFORM Sum([Union Query - Five Year Geographic Region].Data) AS SumOfData
SELECT [Union Query - Five Year Geographic Region].Region AS Region
FROM [Union Query - Five Year Geographic Region]
WHERE ((([Union Query - Five Year Geographic Region].Year)>=(SELECT max([Year]) - 4 from [Union Query - Five Year Geographic Region]) And ([Union Query - Five Year Geographic Region].Year)<=(SELECT max([Year]) from [Union Query - Five Year Geographic Region])))
GROUP BY [Union Query - Five Year Geographic Region].Region
ORDER BY [Union Query - Five Year Geographic Region].Year
PIVOT [Union Query - Five Year Geographic Region].Year;

My problem is that there are some companies which do not have current data and I wish to exclude them from the summary result of the crosstab query. e.g. Companys A,B and C all submitted data for the current year 2005. Company D did not submit data for 2005 but its data from 2004, 2003, 2002 and 2001 are included in those year's totals in the crosstab query. How can I get the query (either the union or crosstab) to only pull data from companies who have current year data in the database.

 

[adamlaing]

Ok, one way I figured how to do this was to create a query of all Companies(called C/Y Companies) wher the year field equals the max year. I was fortunate enough to have another query that contained all of the data I needed in my union query, so I addedthe C/Y Companies query table to the other query and linked the 'company' field, thus only data for the companies contained in the C/Y companies table would be returned.

However, I would be interested in anyone knows how to do this another way as I need to do this exact same thing for other data for which I am not fortunate enough to have already built other queries for that contain the data that I need.