Overview of advanced fixes for data problems
In addition to the techniques previously mentioned in this chapter, 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, if you have some familiarity with these approaches, you broaden your ability to deal with challenges as they arise.
- Custom SQL: This can be used in the data connection to resolve some data problems. Beyond giving a field for a cross database join, as we saw previously, custom SQL can be used to radically reshape the data retrieved from the source. Custom SQL is not an option for all data sources but is for many relational databases and for the legacy JET driver connections for Excel and text files. 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...
