Include empty values in crosstab query

A

Alex

I have a table, biweektbl, that lists all the possible Bi-Weekly shifts. Is
there a way to revise the following crosstab query to include all Bi-Weekly
shifts listed in that table even if there are no values in the query results.
I'm using the crosstab query for a chart and need to show columns for null
series as well. Thanks.

PARAMETERS [forms]![frm_Pick_Operation_Shift_Chart]![StartDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![EndDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![Sectcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Operationcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb] IEEEDouble;
TRANSFORM Sum(qry_Defects_by_Oper_Shift_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT " Bi-Weekly " & [WkCode] & " - Shift " & [Shft] AS Expr1
FROM qry_Defects_by_Oper_Shift_Chart
WHERE (((qry_Defects_by_Oper_Shift_Chart.Shft) Like
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb])) OR
((([forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb]) Is Null))
GROUP BY " Bi-Weekly " & [WkCode] & " - Shift " & [Shft]
PIVOT qry_Defects_by_Oper_Shift_Chart.Defect;
 
J

Jerry Whittle

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.
 
S

S.Clark

The concept is that you'll need to get the desired non-records into the query
prior to the crosstab. I see that you use qry_Defects_by_Oper_Shift_Chart.

I would try to add an Outer Join to this query to ensure you get ALL of the
desired values.

If you're not familiar with an Outer Join, here is an example that show's
ALL Products, and their count of items ordered.

SELECT Products.ProductID, Products.ProductName, Sum([Order
Details].Quantity) AS SumOfQuantity
FROM Products LEFT JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductID, Products.ProductName;
 
A

Alex

but don't I want the row headings, not column headings?

Jerry Whittle said:
One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Alex said:
I have a table, biweektbl, that lists all the possible Bi-Weekly shifts. Is
there a way to revise the following crosstab query to include all Bi-Weekly
shifts listed in that table even if there are no values in the query results.
I'm using the crosstab query for a chart and need to show columns for null
series as well. Thanks.

PARAMETERS [forms]![frm_Pick_Operation_Shift_Chart]![StartDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![EndDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![Sectcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Operationcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb] IEEEDouble;
TRANSFORM Sum(qry_Defects_by_Oper_Shift_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT " Bi-Weekly " & [WkCode] & " - Shift " & [Shft] AS Expr1
FROM qry_Defects_by_Oper_Shift_Chart
WHERE (((qry_Defects_by_Oper_Shift_Chart.Shft) Like
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb])) OR
((([forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb]) Is Null))
GROUP BY " Bi-Weekly " & [WkCode] & " - Shift " & [Shft]
PIVOT qry_Defects_by_Oper_Shift_Chart.Defect;
Click to expand...
Click to expand...
 
A

Alex

Thanks, but I don't define "Bi-Weekly . . . " until the crosstab query so
what/how would I join my table listing all possible Bi-Weekly shifts and my
main table since my main table doesn't yet have values Bi-Weekly shifts?
thanks.

S.Clark said:
The concept is that you'll need to get the desired non-records into the query
prior to the crosstab. I see that you use qry_Defects_by_Oper_Shift_Chart.

I would try to add an Outer Join to this query to ensure you get ALL of the
desired values.

If you're not familiar with an Outer Join, here is an example that show's
ALL Products, and their count of items ordered.

SELECT Products.ProductID, Products.ProductName, Sum([Order
Details].Quantity) AS SumOfQuantity
FROM Products LEFT JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductID, Products.ProductName;

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



Alex said:
I have a table, biweektbl, that lists all the possible Bi-Weekly shifts. Is
there a way to revise the following crosstab query to include all Bi-Weekly
shifts listed in that table even if there are no values in the query results.
I'm using the crosstab query for a chart and need to show columns for null
series as well. Thanks.

PARAMETERS [forms]![frm_Pick_Operation_Shift_Chart]![StartDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![EndDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![Sectcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Operationcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb] IEEEDouble;
TRANSFORM Sum(qry_Defects_by_Oper_Shift_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT " Bi-Weekly " & [WkCode] & " - Shift " & [Shft] AS Expr1
FROM qry_Defects_by_Oper_Shift_Chart
WHERE (((qry_Defects_by_Oper_Shift_Chart.Shft) Like
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb])) OR
((([forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb]) Is Null))
GROUP BY " Bi-Weekly " & [WkCode] & " - Shift " & [Shft]
PIVOT qry_Defects_by_Oper_Shift_Chart.Defect;
Click to expand...
Click to expand...
 
S

S.Clark

I would create a table called tlkpBiWeekly. In it, I would add a row for
each 'be-weekly' (be that a start date, or week number??). Then I would use
that table as the Parent(or ALL) table of the Left (outer) Join.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



Alex said:
Thanks, but I don't define "Bi-Weekly . . . " until the crosstab query so
what/how would I join my table listing all possible Bi-Weekly shifts and my
main table since my main table doesn't yet have values Bi-Weekly shifts?
thanks.

S.Clark said:
The concept is that you'll need to get the desired non-records into the query
prior to the crosstab. I see that you use qry_Defects_by_Oper_Shift_Chart.

I would try to add an Outer Join to this query to ensure you get ALL of the
desired values.

If you're not familiar with an Outer Join, here is an example that show's
ALL Products, and their count of items ordered.

SELECT Products.ProductID, Products.ProductName, Sum([Order
Details].Quantity) AS SumOfQuantity
FROM Products LEFT JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductID, Products.ProductName;

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



Alex said:
I have a table, biweektbl, that lists all the possible Bi-Weekly shifts. Is
there a way to revise the following crosstab query to include all Bi-Weekly
shifts listed in that table even if there are no values in the query results.
I'm using the crosstab query for a chart and need to show columns for null
series as well. Thanks.

PARAMETERS [forms]![frm_Pick_Operation_Shift_Chart]![StartDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![EndDate] DateTime,
[forms]![frm_Pick_Operation_Shift_Chart]![Sectcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Operationcmb] Text ( 255 ),
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb] IEEEDouble;
TRANSFORM Sum(qry_Defects_by_Oper_Shift_Chart.[SumOfDef Qnty]) AS
[SumOfSumOfDef Qnty]
SELECT " Bi-Weekly " & [WkCode] & " - Shift " & [Shft] AS Expr1
FROM qry_Defects_by_Oper_Shift_Chart
WHERE (((qry_Defects_by_Oper_Shift_Chart.Shft) Like
[forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb])) OR
((([forms]![frm_Pick_Operation_Shift_Chart]![Shiftcmb]) Is Null))
GROUP BY " Bi-Weekly " & [WkCode] & " - Shift " & [Shft]
PIVOT qry_Defects_by_Oper_Shift_Chart.Defect;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query for chart 8
return all records in crosstab query 4
Duplicate values in Crosstab 2
insert a crosstab in to temp table query 2
I need to include a field in a crosstab query just to sort 4
Crosstab Query Troubles Again 5
Reverse Axis in Crosstab Query 3
Crosstab Query SQL 1

Top