Problems with Crosstab Query

This should be so simple, but I'm about to tear my hair out.

I have a crosstab query based on a table, left joined to a query which
selects distinct FYs from another table, so that all FYs will show as
column headings. Pretty standard stuff. The strange thing about this
is that the crosstab query returns a single blank record, in addition
to the records I expect. But even stranger is that, if I add a WHERE
clause to the query, the query no longer returns all the fields from
the left-joined FY query, so column headings which have no data are
now missing.

Details:

Using Access 2003 SP3, with Jet 4.0, running on Win PR Pro. Database
has been compact/repaired, and decompiled.

qryFY:
SELECT DISTINCT tblMonthlyDatasets.FY, getFYString([FY]) AS DisplayFY
FROM tblMonthlyDatasets
ORDER BY tblMonthlyDatasets.FY;

qryFY returns the expected dataset (no blank record). The crosstab
uses the FY field (long integer) to link to tblBudgets, and uses
DisplayFY (a string showing FY in a format such as "2009-10") for the
column headings; however, using FY itself as the column heading does
not fix the problem.

tblBudgets:
Country - text
Item - text
FY - long integer
Amount - currency
IsCurrent - yes/no

XTab which returns blank record:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

XTab which fails to return all column headings:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE (((tblBudgets.IsCurrent)=True))
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

Any ideas?

TIA,

Rob

Try:

TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item,
Sum(tblBudgets.Amount) AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE Nz(tblBudgets.IsCurrent,-1)= -1
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

--
Duane Hookom
Microsoft Access MVP


"Rob Parker" wrote:

This should be so simple, but I'm about to tear my hair out.

I have a crosstab query based on a table, left joined to a query which
selects distinct FYs from another table, so that all FYs will show as
column headings. Pretty standard stuff. The strange thing about this
is that the crosstab query returns a single blank record, in addition
to the records I expect. But even stranger is that, if I add a WHERE
clause to the query, the query no longer returns all the fields from
the left-joined FY query, so column headings which have no data are
now missing.

Details:

Using Access 2003 SP3, with Jet 4.0, running on Win PR Pro. Database
has been compact/repaired, and decompiled.

qryFY:
SELECT DISTINCT tblMonthlyDatasets.FY, getFYString([FY]) AS DisplayFY
FROM tblMonthlyDatasets
ORDER BY tblMonthlyDatasets.FY;

qryFY returns the expected dataset (no blank record). The crosstab
uses the FY field (long integer) to link to tblBudgets, and uses
DisplayFY (a string showing FY in a format such as "2009-10") for the
column headings; however, using FY itself as the column heading does
not fix the problem.

tblBudgets:
Country - text
Item - text
FY - long integer
Amount - currency
IsCurrent - yes/no

XTab which returns blank record:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

XTab which fails to return all column headings:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE (((tblBudgets.IsCurrent)=True))
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

Any ideas?

TIA,

Rob

.

Thanks Duane,

That solves the missing headings problem. I don't understand why, though;
is it caused by the fact that in the blank record (which I assume is arising
from the new record in the table, which has a composite primary key of FY,
Country and Item) the IsCurrent field is null? And even so, why does that
cause the left join to fail?

Rob


Duane Hookom wrote:
Try:

TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item,
Sum(tblBudgets.Amount) AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE Nz(tblBudgets.IsCurrent,-1)= -1
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;


This should be so simple, but I'm about to tear my hair out.

I have a crosstab query based on a table, left joined to a query
which selects distinct FYs from another table, so that all FYs will
show as column headings. Pretty standard stuff. The strange thing
about this is that the crosstab query returns a single blank record,
in addition to the records I expect. But even stranger is that, if
I add a WHERE clause to the query, the query no longer returns all
the fields from the left-joined FY query, so column headings which
have no data are now missing.

Details:

Using Access 2003 SP3, with Jet 4.0, running on Win PR Pro. Database
has been compact/repaired, and decompiled.

qryFY:
SELECT DISTINCT tblMonthlyDatasets.FY, getFYString([FY]) AS DisplayFY
FROM tblMonthlyDatasets
ORDER BY tblMonthlyDatasets.FY;

qryFY returns the expected dataset (no blank record). The crosstab
uses the FY field (long integer) to link to tblBudgets, and uses
DisplayFY (a string showing FY in a format such as "2009-10") for the
column headings; however, using FY itself as the column heading does
not fix the problem.

tblBudgets:
Country - text
Item - text
FY - long integer
Amount - currency
IsCurrent - yes/no

XTab which returns blank record:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

XTab which fails to return all column headings:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE (((tblBudgets.IsCurrent)=True))
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

Any ideas?

TIA,

Rob

.

The problem was that you were negating the LEFT JOIN when you apply criteria
against the table (tblBudgets) on the right side of the join.

The where clause then filtered out any rows where IsCurrent was null which it
would be whenever there was no matching record in tblBudgets. Daune's
construct using NZ took care of the NULL value.

Interesting solution that I would have handled using either nested queries or
a subquery in the FROM clause in place of tblBudgets. Something like the
following.

TRANSFORM Sum(B.Amount) AS SumOfAmount
SELECT B.Country, B.Item
, Sum(B.Amount) AS TotalAmount
FROM qryFY LEFT JOIN
(SELECT FY, Amount, Country, Item
FROM tblBudgets
WHERE IsCurrent = True) as B
ON qryFY.FY = B.FY
GROUP BY B.Country, B.Item
PIVOT qryFY.DisplayFY

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Rob Parker wrote:
Thanks Duane,

That solves the missing headings problem. I don't understand why, though;
is it caused by the fact that in the blank record (which I assume is arising
from the new record in the table, which has a composite primary key of FY,
Country and Item) the IsCurrent field is null? And even so, why does that
cause the left join to fail?

Rob


Duane Hookom wrote:
Try:

TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item,
Sum(tblBudgets.Amount) AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE Nz(tblBudgets.IsCurrent,-1)= -1
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;


This should be so simple, but I'm about to tear my hair out.

I have a crosstab query based on a table, left joined to a query
which selects distinct FYs from another table, so that all FYs will
show as column headings. Pretty standard stuff. The strange thing
about this is that the crosstab query returns a single blank record,
in addition to the records I expect. But even stranger is that, if
I add a WHERE clause to the query, the query no longer returns all
the fields from the left-joined FY query, so column headings which
have no data are now missing.

Details:

Using Access 2003 SP3, with Jet 4.0, running on Win PR Pro. Database
has been compact/repaired, and decompiled.

qryFY:
SELECT DISTINCT tblMonthlyDatasets.FY, getFYString([FY]) AS DisplayFY
FROM tblMonthlyDatasets
ORDER BY tblMonthlyDatasets.FY;

qryFY returns the expected dataset (no blank record). The crosstab
uses the FY field (long integer) to link to tblBudgets, and uses
DisplayFY (a string showing FY in a format such as "2009-10") for the
column headings; however, using FY itself as the column heading does
not fix the problem.

tblBudgets:
Country - text
Item - text
FY - long integer
Amount - currency
IsCurrent - yes/no

XTab which returns blank record:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

XTab which fails to return all column headings:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount) AS
TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE (((tblBudgets.IsCurrent)=True))
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

Any ideas?

TIA,

Rob

.

Thanks John,

I guess I've not come across this issue before because I normally use nested
queries - my crosstabs are generally based on a query returning specific
results, rather than a table. And my nested queries are commonly
non-updateable, so I'm also not used to seeing a blank record in a crosstab.

And now that I understand it, I see that yet another way of solving it would
be to set the WHERE clause to:
WHERE tblBudgets.IsCurrent = True OR tblBudgets.IsCurrent Is Null

Again, thanks to both you and Duane,

Rob


John Spencer wrote:
The problem was that you were negating the LEFT JOIN when you apply
criteria against the table (tblBudgets) on the right side of the join.

The where clause then filtered out any rows where IsCurrent was null
which it would be whenever there was no matching record in
tblBudgets. Daune's construct using NZ took care of the NULL value.

Interesting solution that I would have handled using either nested
queries or a subquery in the FROM clause in place of tblBudgets. Something
like the following.

TRANSFORM Sum(B.Amount) AS SumOfAmount
SELECT B.Country, B.Item
, Sum(B.Amount) AS TotalAmount
FROM qryFY LEFT JOIN
(SELECT FY, Amount, Country, Item
FROM tblBudgets
WHERE IsCurrent = True) as B
ON qryFY.FY = B.FY
GROUP BY B.Country, B.Item
PIVOT qryFY.DisplayFY

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Rob Parker wrote:
Thanks Duane,

That solves the missing headings problem. I don't understand why,
though; is it caused by the fact that in the blank record (which I
assume is arising from the new record in the table, which has a
composite primary key of FY, Country and Item) the IsCurrent field
is null? And even so, why does that cause the left join to fail?

Rob


Duane Hookom wrote:
Try:

TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item,
Sum(tblBudgets.Amount) AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE Nz(tblBudgets.IsCurrent,-1)= -1
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;


This should be so simple, but I'm about to tear my hair out.

I have a crosstab query based on a table, left joined to a query
which selects distinct FYs from another table, so that all FYs will
show as column headings. Pretty standard stuff. The strange thing
about this is that the crosstab query returns a single blank
record, in addition to the records I expect. But even stranger is
that, if I add a WHERE clause to the query, the query no longer
returns all the fields from the left-joined FY query, so column
headings which have no data are now missing.

Details:

Using Access 2003 SP3, with Jet 4.0, running on Win PR Pro. Database
has been compact/repaired, and decompiled.

qryFY:
SELECT DISTINCT tblMonthlyDatasets.FY, getFYString([FY]) AS
DisplayFY FROM tblMonthlyDatasets
ORDER BY tblMonthlyDatasets.FY;

qryFY returns the expected dataset (no blank record). The crosstab
uses the FY field (long integer) to link to tblBudgets, and uses
DisplayFY (a string showing FY in a format such as "2009-10") for
the column headings; however, using FY itself as the column
heading does not fix the problem.

tblBudgets:
Country - text
Item - text
FY - long integer
Amount - currency
IsCurrent - yes/no

XTab which returns blank record:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount)
AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

XTab which fails to return all column headings:
TRANSFORM Sum(tblBudgets.Amount) AS SumOfAmount
SELECT tblBudgets.Country, tblBudgets.Item, Sum(tblBudgets.Amount)
AS TotalAmount
FROM qryFY LEFT JOIN tblBudgets ON qryFY.FY = tblBudgets.FY
WHERE (((tblBudgets.IsCurrent)=True))
GROUP BY tblBudgets.Country, tblBudgets.Item
PIVOT qryFY.DisplayFY;

Any ideas?

TIA,

Rob

.