How to report the nations where the average yield of the shares exceeds the average yield of all shares using SQLAccess

To report the nations where the average yield of the shares exceeds the average yield of all shares using SQL (Access), you can follow these steps:

1. Create a query that joins the tables containing the share data and the countries data. For example, if you have a table called Shares with columns ShareName, ShareYield, and CountryCode, and a table called Countries with columns CountryCode and CountryName, you can join these tables on the CountryCode column.

2. Calculate the average yield of all shares by using the AVG() function on the ShareYield column. For example, you can add a new column to your query called AvgYield that calculates the average yield of all shares like this: AVG(Shares.ShareYield).

3. Calculate the average yield of shares for each country by using the AVG() function and grouping the results by the CountryName column. For example, you can add a new column to your query called CountryAvgYield that calculates the average yield of shares for each country like this: AVG(Shares.ShareYield) GROUP BY Countries.CountryName.

4. Add a filter to your query that compares the CountryAvgYield column to the AvgYield column, and only includes countries where the CountryAvgYield is greater than the AvgYield. For example, you can add a WHERE clause to your query like this: WHERE CountryAvgYield > AvgYield.

5. Run the query to see the results. The query should return a list of countries where the average yield of shares exceeds the average yield of all shares.

Here's an example SQL query that you can use as a starting point:

SELECT Countries.CountryName, AVG(Shares.ShareYield) as CountryAvgYield, AVG(Shares.ShareYield) OVER() as AvgYield FROM Shares INNER JOIN Countries ON Shares.CountryCode = Countries.CountryCode GROUP BY Countries.CountryName HAVING AVG(Shares.ShareYield) > AVG(Shares.ShareYield) OVER();