Crosstab report problem

[Ynot]

I have a crosstab report based on a crosstab query that links "phases" to
projects. Each project can have up to 20 phases scheduled. The report
works fine if I filter on Phases where Phase.Complete = "false." Where I
get into trouble is when I add a filter on Project where Project.Complete ="false,"
attempting to show only active projects. It seems that the data doesn't
populate all of the possible "phase" column headings and I get an error
saying the Jet database engine does not recognize a field name. The field
is not represented because I am trying to filter out completed projects.



Is there any way to solve this??

 

[Duane Hookom]

You can enter all possible phase values into the Column Headings property of
the crosstab query.

 

[Ynot]

Duane, thanks.



I understand what you are saying, I just don't know how to do it. This is
my first experience with a crosstab query/report and I thought I was doing
well until I hit this snag.

Right now the query has one column with the "phase_name" column of the
particular table. How do I do what you mentioned??

 

[Duane Hookom]

Provide your sql. If you have a crosstab query, then there is a column
headings property of the crosstab query.

 

[Ynot]

Here is the SQL, if you can show me where to put 2 column headings then I
think I can figure it out. I think this is what you were asking for..

Thanks in advance for the help.

TRANSFORM Max(PhaseJob.DateStart) AS MaxOfDateStart
SELECT PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
Count(PhaseJob.DateStart) AS [Total Of DateStart]
FROM PhaseJob
WHERE (((PhaseJob.StatusComplete)=False))
GROUP BY PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
PhaseJob.StatusComplete, PhaseJob.DateComplete, PhaseJob.DateComplete
ORDER BY PhaseJob.Super, PhaseJob.JOBNUMBER
PIVOT PhaseJob.DateDesc;

 

[Duane Hookom]

Is DateDesc your phase? If so, try something like:

TRANSFORM Max(PhaseJob.DateStart) AS MaxOfDateStart
SELECT PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
Count(PhaseJob.DateStart) AS [Total Of DateStart]
FROM PhaseJob
WHERE (((PhaseJob.StatusComplete)=False))
GROUP BY PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
PhaseJob.StatusComplete, PhaseJob.DateComplete, PhaseJob.DateComplete
ORDER BY PhaseJob.Super, PhaseJob.JOBNUMBER
PIVOT PhaseJob.DateDesc IN ("PhaseOne","PhaseTwo",..."PhaseX" );


--
Duane Hookom
MS Access MVP
--

Here is the SQL, if you can show me where to put 2 column headings then I
think I can figure it out. I think this is what you were asking for..

Thanks in advance for the help.

TRANSFORM Max(PhaseJob.DateStart) AS MaxOfDateStart
SELECT PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
Count(PhaseJob.DateStart) AS [Total Of DateStart]
FROM PhaseJob
WHERE (((PhaseJob.StatusComplete)=False))
GROUP BY PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
PhaseJob.StatusComplete, PhaseJob.DateComplete, PhaseJob.DateComplete
ORDER BY PhaseJob.Super, PhaseJob.JOBNUMBER
PIVOT PhaseJob.DateDesc;

 

[Ynot]

That did it Duane. Thanks again for the help and an explanation I could
understand!

Is DateDesc your phase? If so, try something like:

TRANSFORM Max(PhaseJob.DateStart) AS MaxOfDateStart
SELECT PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
Count(PhaseJob.DateStart) AS [Total Of DateStart]
FROM PhaseJob
WHERE (((PhaseJob.StatusComplete)=False))
GROUP BY PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
PhaseJob.StatusComplete, PhaseJob.DateComplete, PhaseJob.DateComplete
ORDER BY PhaseJob.Super, PhaseJob.JOBNUMBER
PIVOT PhaseJob.DateDesc IN ("PhaseOne","PhaseTwo",..."PhaseX" );


--
Duane Hookom
MS Access MVP
--

Here is the SQL, if you can show me where to put 2 column headings then I
think I can figure it out. I think this is what you were asking for..

Thanks in advance for the help.

TRANSFORM Max(PhaseJob.DateStart) AS MaxOfDateStart
SELECT PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
Count(PhaseJob.DateStart) AS [Total Of DateStart]
FROM PhaseJob
WHERE (((PhaseJob.StatusComplete)=False))
GROUP BY PhaseJob.Super, PhaseJob.JOBNUMBER, PhaseJob.JOBNAME,
PhaseJob.StatusComplete, PhaseJob.DateComplete, PhaseJob.DateComplete
ORDER BY PhaseJob.Super, PhaseJob.JOBNUMBER
PIVOT PhaseJob.DateDesc;

Provide your sql. If you have a crosstab query, then there is a column
headings property of the crosstab query.

--
Duane Hookom
MS Access MVP


Duane, thanks.



I understand what you are saying, I just don't know how to do it. This
is
my first experience with a crosstab query/report and I thought I was
doing
well until I hit this snag.

Right now the query has one column with the "phase_name" column of the
particular table. How do I do what you mentioned??





You can enter all possible phase values into the Column Headings
property
of
the crosstab query.

--
Duane Hookom
MS Access MVP


I have a crosstab report based on a crosstab query that links
"phases"
to
projects. Each project can have up to 20 phases scheduled. The
report
works fine if I filter on Phases where Phase.Complete = "false."
Where
I
get into trouble is when I add a filter on Project where
Project.Complete
="false,"
attempting to show only active projects. It seems that the data
doesn't
populate all of the possible "phase" column headings and I get an
error
saying the Jet database engine does not recognize a field name. The
field
is not represented because I am trying to filter out completed
projects.



Is there any way to solve this??