C
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
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