table g has 3 columns Column_Sort_Heading (Text20), Beg_dt and End_dt.
It has 12 rows which will serve as the column headings of the crosstab
as well as containing the date ranges of the respective headings (used
in the query). This works great except in the cases that no matches are
found for a particular quarter. The column will naturally not appear in
the report. Makes sense but I would like to include all columns I
define, all 12 (regardless of whether there's any data). I'm using
Access7.0 as my database. Does anyone have a smart solution for this? I
imagine that stuffing some dummy rows, hopefully 1 for each period is
the solution, but since I'm grouping do I need a row for each group...?
Grateful for your replies! Ken

SELECT c.Region,
b.Country,
g.Column_Sort_Heading,
Sum(a.USDAmt) AS Amt
FROM BEL AS a,
LOOKUP_Country AS b,
LOOKUP_Region AS c,
Cross_Tab_Heading_Table AS g
WHERE (((a.LaunchDate)>=[g].[Beg_dt] And (a.LaunchDate)<=[g].[End_dt])
AND ((a.iso_code)=[b].[iso_code]) AND ((b.Region_code)=
[c].[region_code]))
GROUP BY c.Region, b.Country, g.Column_Sort_Heading
ORDER BY c.Region, b.Country, g.Column_Sort_Heading;

Sent via Deja.com http://www.*-*-*.com/
Before you buy.