I have one too...

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

My crosstab returns Categories as column headers, Office as row headers, and
a number (a sum) as the value. If a certain Category has no numeric data the
crosstab does not return that Category in the resultset. When the query is
bound to a report and the report expects that column it generates an error.

Is there a way to get the report to only show the column headers and data if
it is returned by the query but hide it otherwise? Or should I be changing
the query? Any suggestions on how to do either...

Thanking you
 
You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office doesn't
have a particular category, the query will return a Null value. To return a
zero instead of a Null, use the NZ function. Change your Value column to
NZ([Value Column], 0).

You could also use an outer join with a list of all categories to make sure
you get them all, but that would involve perhaps significant changes to your
query. You didn't post your SQL or table structure, so I'm only guessing.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks for the info but I'm still having trouble getting past the missing
column problem... haven't found much info on using the IN operator you
mention... here is my query... perhaps you could show me how to modify it?

Note that I did use the NZ funtion to convert my nulls to zeros (not in this
query)... thanks... to be honest I had it there to start with but without
the ,0 at the end - didn't seem to work... don't use Access much...

Anyway here is a slightly modified version of my query:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE (((DateDiff("ww",[DateForwarded],Date())>0) AND
((Categories.Category='Category1') Or (Categories.Category='Category2') Or
(Categories.Category='Category3') Or (Categories.Category='Category4') Or
(Categories.Category='Category5') Or (Categories.Category='Category6'))))
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' : ' +
Cases.CaseTitle
PIVOT Categories.Category;

I need to be able to always return a column for each of the categories in
the WHERE clause... will the IN clause achieve this and if so, how?

Thanks again
Kevin




John Viescas said:
You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office doesn't
have a particular category, the query will return a Null value. To return a
zero instead of a Null, use the NZ function. Change your Value column to
NZ([Value Column], 0).

You could also use an outer join with a list of all categories to make sure
you get them all, but that would involve perhaps significant changes to your
query. You didn't post your SQL or table structure, so I'm only guessing.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Kevin said:
My crosstab returns Categories as column headers, Office as row headers, and
a number (a sum) as the value. If a certain Category has no numeric data the
crosstab does not return that Category in the resultset. When the query is
bound to a report and the report expects that column it generates an error.

Is there a way to get the report to only show the column headers and
data
if
it is returned by the query but hide it otherwise? Or should I be changing
the query? Any suggestions on how to do either...

Thanking you
 
PMFJI:
What John wanted was for you to check Help on the Column Headings property:
Try:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE DateDiff("ww",[DateForwarded],Date())>0
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' : ' +
Cases.CaseTitle
PIVOT Categories.Category IN ('Category1', 'Category2', 'Category3' ,
'Category4', 'Category5', 'Category6')



--
Duane Hookom
MS Access MVP


Kevin said:
Thanks for the info but I'm still having trouble getting past the missing
column problem... haven't found much info on using the IN operator you
mention... here is my query... perhaps you could show me how to modify it?

Note that I did use the NZ funtion to convert my nulls to zeros (not in this
query)... thanks... to be honest I had it there to start with but without
the ,0 at the end - didn't seem to work... don't use Access much...

Anyway here is a slightly modified version of my query:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE (((DateDiff("ww",[DateForwarded],Date())>0) AND
((Categories.Category='Category1') Or (Categories.Category='Category2') Or
(Categories.Category='Category3') Or (Categories.Category='Category4') Or
(Categories.Category='Category5') Or (Categories.Category='Category6'))))
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' : ' +
Cases.CaseTitle
PIVOT Categories.Category;

I need to be able to always return a column for each of the categories in
the WHERE clause... will the IN clause achieve this and if so, how?

Thanks again
Kevin




John Viescas said:
You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office doesn't
have a particular category, the query will return a Null value. To
return
a
zero instead of a Null, use the NZ function. Change your Value column to
NZ([Value Column], 0).

You could also use an outer join with a list of all categories to make sure
you get them all, but that would involve perhaps significant changes to your
query. You didn't post your SQL or table structure, so I'm only guessing.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Kevin said:
My crosstab returns Categories as column headers, Office as row
headers,
and
a number (a sum) as the value. If a certain Category has no numeric
data
the
crosstab does not return that Category in the resultset. When the
query
 
Ah, I get it now... I was trying to use the IN clause in the wrong place...
that works nicely... thanks

Would you know how to now force the query to show all weeks
(DateDiff("ww",[DateForwarded],Date()) AS Weeks), even those with no
cases...?

I could use that for another similar query...

Anyway, thanks for the info

Duane Hookom said:
PMFJI:
What John wanted was for you to check Help on the Column Headings property:
Try:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE DateDiff("ww",[DateForwarded],Date())>0
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' : ' +
Cases.CaseTitle
PIVOT Categories.Category IN ('Category1', 'Category2', 'Category3' ,
'Category4', 'Category5', 'Category6')



--
Duane Hookom
MS Access MVP


Kevin said:
Thanks for the info but I'm still having trouble getting past the missing
column problem... haven't found much info on using the IN operator you
mention... here is my query... perhaps you could show me how to modify it?

Note that I did use the NZ funtion to convert my nulls to zeros (not in this
query)... thanks... to be honest I had it there to start with but without
the ,0 at the end - didn't seem to work... don't use Access much...

Anyway here is a slightly modified version of my query:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE (((DateDiff("ww",[DateForwarded],Date())>0) AND
((Categories.Category='Category1') Or (Categories.Category='Category2') Or
(Categories.Category='Category3') Or (Categories.Category='Category4') Or
(Categories.Category='Category5') Or (Categories.Category='Category6'))))
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' :
'
+
Cases.CaseTitle
PIVOT Categories.Category;

I need to be able to always return a column for each of the categories in
the WHERE clause... will the IN clause achieve this and if so, how?

Thanks again
Kevin




John Viescas said:
You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office doesn't
have a particular category, the query will return a Null value. To
return
a
zero instead of a Null, use the NZ function. Change your Value column to
NZ([Value Column], 0).

You could also use an outer join with a list of all categories to make sure
you get them all, but that would involve perhaps significant changes
to
your
query. You didn't post your SQL or table structure, so I'm only guessing.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
My crosstab returns Categories as column headers, Office as row headers,
and
a number (a sum) as the value. If a certain Category has no numeric data
the
crosstab does not return that Category in the resultset. When the
query
is
bound to a report and the report expects that column it generates an
error.

Is there a way to get the report to only show the column headers and data
if
it is returned by the query but hide it otherwise? Or should I be changing
the query? Any suggestions on how to do either...

Thanking you
 
You can't add rows without having a value in a table that generates the row.
I have used a table with all dates and then used it in a group by query that
creates one row per week. Then combine this query with your crosstab and use
a join that includes all the records from the date table query.

--
Duane Hookom
MS Access MVP


666 said:
Ah, I get it now... I was trying to use the IN clause in the wrong place...
that works nicely... thanks

Would you know how to now force the query to show all weeks
(DateDiff("ww",[DateForwarded],Date()) AS Weeks), even those with no
cases...?

I could use that for another similar query...

Anyway, thanks for the info

Duane Hookom said:
PMFJI:
What John wanted was for you to check Help on the Column Headings property:
Try:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE DateDiff("ww",[DateForwarded],Date())>0
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + ' :
'
+
Cases.CaseTitle
PIVOT Categories.Category IN ('Category1', 'Category2', 'Category3' ,
'Category4', 'Category5', 'Category6')



--
Duane Hookom
MS Access MVP


Kevin said:
Thanks for the info but I'm still having trouble getting past the missing
column problem... haven't found much info on using the IN operator you
mention... here is my query... perhaps you could show me how to modify it?

Note that I did use the NZ funtion to convert my nulls to zeros (not
in
this
query)... thanks... to be honest I had it there to start with but without
the ,0 at the end - didn't seem to work... don't use Access much...

Anyway here is a slightly modified version of my query:

TRANSFORM Categories.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE (((DateDiff("ww",[DateForwarded],Date())>0) AND
((Categories.Category='Category1') Or
(Categories.Category='Category2')
Or
(Categories.Category='Category3') Or (Categories.Category='Category4') Or
(Categories.Category='Category5') Or (Categories.Category='Category6'))))
GROUP BY DateDiff("ww",[DateForwarded],Date()), Categories.CaseNo + '
:
'
+
Cases.CaseTitle
PIVOT Categories.Category;

I need to be able to always return a column for each of the categories in
the WHERE clause... will the IN clause achieve this and if so, how?

Thanks again
Kevin




You can use an IN clause (Query Properties, Column Headings) to explicitly
declare all the Categories that the report expects. When an Office
doesn't
have a particular category, the query will return a Null value. To return
a
zero instead of a Null, use the NZ function. Change your Value
column
to
NZ([Value Column], 0).

You could also use an outer join with a list of all categories to make
sure
you get them all, but that would involve perhaps significant changes to
your
query. You didn't post your SQL or table structure, so I'm only guessing.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
My crosstab returns Categories as column headers, Office as row headers,
and
a number (a sum) as the value. If a certain Category has no
numeric
data
the
crosstab does not return that Category in the resultset. When the query
is
bound to a report and the report expects that column it generates an
error.

Is there a way to get the report to only show the column headers and
data
if
it is returned by the query but hide it otherwise? Or should I be
changing
the query? Any suggestions on how to do either...

Thanking you
 
Back
Top