Adding a WHERE clause to a Crosstab query

[Carl Rapson]

My database contains two tables:

1. [Schedule Definition], which contains two fields: [Line Number] (int) and
[Description] (string)
2. [Schedules], which contains 3 fields: [ID] (long), [Line Number] (int),
and [Date Due] (date)

There are 17 line items defined in the [Schedule Definition] table, each
with a description. The [Schedules] table will contain entries for one or
more line items for each ID. Thus, the data could look something like:

1,1,10/1/04
1,2,10/5/04
1,5,10/8/04
2,1,11/1/04
2,6,11/6/04
2,17,11/30/04

What I want is a crosstab query that produces a grid showing all 17 line
items as the rows and the days of the month (1-31) as the columns. For a
given ID, I want to put an "X" in the day column for each line item defined
for the ID. So, for example, for ID=1 I want to see:

1 2 3 4 5 6 7 8 9 ...
Item1 X
Item2 X
Item3
Item4
Item5 X

(I hope this comes across with the correct formatting).

I have created a crosstab query that looks like this:

TRANSFORM First("X") AS [The Value]
SELECT [Schedule Definition].[Line Number],[Schedule Definition].Description
FROM [Schedules] RIGHT JOIN [Schedule Definition] ON [Schedules].[Line
Number]=[Schedule Definition].[Line Number]
GROUP BY [Schedule Definition].[Line Number],[Schedule
Definition].Description
PIVOT Format([Date Due],"d") In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

My question is, how do I limit this to a particular ID? If I add the ID to
the query in a WHERE clause (WHERE ID=1, before the GROUP BY clause), I only
get rows for the line items defined for ID=1 instead of all line items:

1 2 3 4 5 6 7 8 9 ...
Item1 X
Item2 X
Item5 X

So, how can I produce the grid for a given ID, including those line items
not containing dates?

I hope I have given enough information. Thanks for any assistance,

Carl Rapson

 

[Guest]

You have to make a select query that includes all your items then left join
it on item with your current table with the properties of include all records
from item query and only those...

This should solve your problems of your items not showing up with a where
clause.

HTH
Martin

 

[Carl Rapson]

Thanks, I will look into that and see if it solves the problem.

Carl

You have to make a select query that includes all your items then left
join
it on item with your current table with the properties of include all
records
from item query and only those...

This should solve your problems of your items not showing up with a where
clause.

HTH
Martin

My database contains two tables:

1. [Schedule Definition], which contains two fields: [Line Number] (int)
and
[Description] (string)
2. [Schedules], which contains 3 fields: [ID] (long), [Line Number]
(int),
and [Date Due] (date)

There are 17 line items defined in the [Schedule Definition] table, each
with a description. The [Schedules] table will contain entries for one or
more line items for each ID. Thus, the data could look something like:

1,1,10/1/04
1,2,10/5/04
1,5,10/8/04
2,1,11/1/04
2,6,11/6/04
2,17,11/30/04

What I want is a crosstab query that produces a grid showing all 17 line
items as the rows and the days of the month (1-31) as the columns. For a
given ID, I want to put an "X" in the day column for each line item
defined
for the ID. So, for example, for ID=1 I want to see:

1 2 3 4 5 6 7 8 9 ...
Item1 X
Item2 X
Item3
Item4
Item5 X

(I hope this comes across with the correct formatting).

I have created a crosstab query that looks like this:

TRANSFORM First("X") AS [The Value]
SELECT [Schedule Definition].[Line Number],[Schedule
Definition].Description
FROM [Schedules] RIGHT JOIN [Schedule Definition] ON [Schedules].[Line
Number]=[Schedule Definition].[Line Number]
GROUP BY [Schedule Definition].[Line Number],[Schedule
Definition].Description
PIVOT Format([Date Due],"d") In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

My question is, how do I limit this to a particular ID? If I add the ID
to
the query in a WHERE clause (WHERE ID=1, before the GROUP BY clause), I
only
get rows for the line items defined for ID=1 instead of all line items:

1 2 3 4 5 6 7 8 9 ...
Item1 X
Item2 X
Item5 X

So, how can I produce the grid for a given ID, including those line items
not containing dates?

I hope I have given enough information. Thanks for any assistance,

Carl Rapson