Listing 3-5
TRANSFORM
Count(Models.ModelID) AS CountOfModelID
SELECT
Models.Name, Count(Models.ModelID) AS [Total Of ModelID]
FROM Models
GROUP BY Models.Name
PIVOT Format([Year],"yyyy");
If you upsize the database to SQL Server, including the data, and try to run
this query, you will find that the syntax isn’t supported. In particular, Access’
use of Transform and Pivot are not supported in the SQL engine. But, you can
run this query against linked SQL Server tables. So, if you have need of a quick
crosstab querying capability, Access is a handy tool to have.
Duplicates and Unmatched Queries
There are two additional Query Wizards available for use. Both can be found by
clicking the New toolbar button on the Query window. These final two Query
Wizards allow us to analyze our data further to ensure it has appropriate referential
integrity.
CHAPTER 3: Building Advanced Queries with the Query Wizard
63
Win&Mac / Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Chapter 3
FIGURE 3-16
Models by Year crosstab query
P:\010Comp\Win&Mac\965-9\ch03.vp
Wednesday, August 06, 2003 10:13:21 AM
Color profile: Generic CMYK printer profile
Composite Default screen
|
|