by Peter Wone - GUI Computing

The TRANSFORM statement is probably unique to Microsoft Access. Probably you know it as the crosstab query. Check out the following:

  TRANSFORM Sum([Quarterly Sales Figures (table)].Sales)
  SELECT Format([Date],"yyyy") AS Year
  FROM [Quarterly Sales Figures (table)]
  GROUP BY Format([Date],"yyyy")
  PIVOT "Quarter " & Format([Date],"q");

That’s dead easy in Jet SQL. Check it out without PIVOT: what a mess!

  SELECT DISTINCT Format([Date], "yyyy") AS Year, 
    (SELECT Sales FROM [Quarterly Sales Figures (table)] 
        WHERE Format([Date], "q") = "1"
			AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q1,
    (SELECT Sales FROM [Quarterly Sales Figures (table)]
        WHERE Format([Date], "q") = "2"
              AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q2,
    (SELECT Sales FROM [Quarterly Sales Figures (table)]
        WHERE Format([Date], "q") = "3"
              AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q3,
    Format([Date], "q") = "4"
     AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q4
    FROM [Quarterly Sales Figures (table)] AS T1;

Worse, what if, unlike this (thoroughly cooked) example, the number of categories isn’t fixed? In that case you have to use an N-way self-join. I can’t be bothered writing up the N-way self-join solution (as I can’t remember offhand how to do it).

Come on guys, I’m not the only bloke in the country with a knowledge of SQL. Write an article on N-way self-joins and get your name up in lights. Or in print, at any rate. And save me hours of research.

If you’re using Jet as middleware for VB/SQL Server (or VB/Oracle etc.) you can use your view or stored procedure to do the restriction and then let Jet do its funky thang. This is quite effective. The crux of efficient client/server strategy is to perform all restriction at the server. This limits traffic, which is the real killer.

I use this strategy all the time to work around the fact that SQL Server won’t let me have an ORDER BY or GROUP BY clause in a view. I could use a stored procedure, but then I wouldn’t be able to attach it to a Jet database in any way other than using a passthrough query like EXEC sp_name.

And that’s no fun — I can’t use all Jet’s goodies to play with the result unless I wrap yet another query around it. Nor can I have a look at field definitions. Blurk.

[HOME] [TABLE OF CONTENTS] [SEARCH]