You can solve that problem by modifying the crosstab query to always return
the four columns.
In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.
TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")
In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)
Hi, again
I'm having another problem. I'm displaying the data from the cross
tab query on a continuous form. I have 4 textboxes bounded to the query
- In Progress (txtInProgress), On Time (txtOnTime), Late (txtLate),
Very Late (txtVeryLate)
If one of the statuses are not present, for example if there are no on
time projects, I get an error '#Name?' in my txtOnTime.
Therefore for a quick solution I put another textbox (txtOnTime) on the
txtOnTime. By default the txtHideOnTime is not visible and it is made
visible by a error handler.
On Error GoTo handler
handler:
Me.txtHideOnTime.Visible = True
I need to mention, behind txtOnTime, txtLate, txtVeryLate, there is
another textbox that displays the green for on time, yellow for late
and red for very late.
For example On Time - txtBackColor2
Control Source =Switch([On Time]<>"",1,[On Time]="",Null)
Format = "ÛÛÛÛÛÛÛÛ"[Green];"ÛÛÛÛÛÛÛ"[White]
But how can I handle all 3 textboxes - txtOnTime, txtLate, txtVeryLate
to enable their respective hide textboxes, or is there another
solution.
Thank you very much Duane Hookom
You are the best
Duane said:
You can normalize your 4 queries into one:
SELECT Manager_Name, "In Progress" as Status, Projects
FROM [qselInProgress]
UNION ALL
SELECT Manager_Name, "On Time", Projects
FROM [qselOnTime]
UNION ALL
SELECT Manager_Name, "Late", Projects
FROM [qselLate]
UNION ALL
SELECT Manager_Name, "Very Late", Projects
FROM [qselVery_Late];
Save the above (substituting your actual field and table names) as
"quniMgrProjectStatus".
Then create your crosstab query with Manager_Name as the Row Heading,
Status
as the Column Heading, and Sum of Projects as the Value.
--
Duane Hookom
MS Access MVP
PRAV said:
I'm a bit confused ... I have 4 queries qry_inprogress, qry_ontime,
qry_late and qry_verylate which are giving me the data below
In progress
------------------------
Manager_Name Projects
Paul 10
Jack 5
Chris 6
Frank 2
Jason 7
Eric 4
On Time
------------------------------
Manager_Name Projects
Paul 8
Jack 2
Chris 3
Late
---------------------------------
Manager_Name Projects
Eric 3
Paul 1
Very_Late
----------------------------------
Manager_Name Projects
Jack 4
Jason 5
Chris 2
how can i merge them as you are allow only one query.
Duane Hookom wrote:
There is a crosstab wizard but I don't use it. All you have to
understand
is
which fields provide the values you want for your Column Heading, Row
Heading(s), and the Value.
--
Duane Hookom
MS Access MVP
Are there any documentation showing how to create a crosstab. I
checked
the ms access 97 one, but its not the one i want.
Duane Hookom wrote:
This looks like a crosstab query to me. Manager is the Row
Heading, a
DateDiff() expression is your column heading, and Count of Project
is
the
value.
--
Duane Hookom
MS Access MVP
Hi i need some guidance to produce a report in MS Access 97 for
all
managers that shows the number of projects in progress, on time,
late
and very late.
Name NoProject On Time Late Very Late
Paul 10 8 1 1
Jack 5 2 3 0
Jo 0 0 0 0
.
.
.
.
The full list of managers are in the tblManagers.
Each Project has a manager and an AgreedDate and CompleteDate
that's
in
tblProjects.
Not all managers are managing projects. I calculate the on time,
late
and very late by using 'datediff("d",AgreedDate, CompletedDate)'
and
a
count for the number of projects.
How can i create a report or a continous form show all users in
that
tblManagers that are managing project (number of project) and
the on
time, late and very late projects. Thank you in advance.
