An overview of advanced fixes for data problems

In addition to the techniques shown in the previous sections, there are some additional possibilities for dealing with data structure issues. It is outside the scope of this book to develop these concepts fully. However, given some familiarity with these approaches, you broaden your ability to deal with challenges as they arise:

• Custom SQL: This can be used in data connection to resolve some data problems. Custom SQL is not an option for all data sources but is for many relational databases and for legacy JET driver connections. Consider a custom SQL script that takes the wide table of country populations mentioned earlier in this chapter and restructures it into a tall table:

  SELECT [Country Name],[1960] AS Population, 1960 AS Year
  FROM Countries
  
  UNION ALL
  
  SELECT [Country Name],[1961] AS Population, 1961 AS Year
  FROM Countries
  
  UNION ALL 
  
  SELECT [Country Name],[1962] AS Population, 1962 AS Year
  FROM Countries
  ...
  ...

  It might be a little...