Crosstab report problem

  • Thread starter Thread starter Ynot
  • Start date Start date
Y

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??
 
You can enter all possible phase values into the Column Headings property of
the crosstab query.
 
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??
 
Provide your sql. If you have a crosstab query, then there is a column
headings property of the crosstab query.
 
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;
 
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
--

Ynot said:
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;
Click to expand...
 
That did it Duane. Thanks again for the help and an explanation I could
understand!


Duane Hookom said:
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
--

Ynot said:
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 said:
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??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top