Those tricky Crosstab Queries

D

David

Hello ol wise ones,
I'm using Access 2002. Two questions to help improve this crosstab query.
Question1: I have combined the fields due_date and task so the column order
would be correct. Is there a way to keep the sort order by due_date but only
display the field task? I've tried using RIGHT, but as it trims, it also
impact the sort criteria.

Question2: the tasks may be unique for each value in SR field. Can I place
a N/A in the rows which that particular task does not apply? Current it
shows blank cell. I like the blank cell if it is a task for the SR, but no
value in the field Date_Completed.
Example:Rec1 has Task1 in the table. Rec2 does not have Task1 in the table
Table
Record 1
Field SR - Rec1
Field Task - Task1
Date_Completed - (blank)

Record 2
Field SR - Rec1
Field Task - Task2
Date_Completed - 11/15/06

Record 3
Field SR - Rec2
Field Task - Task2
Date_Completed - 11/15/06

(how I would like it to appear)
SR----Task1------Task2
Rec1--(blank)-- 11/15/06
Rec2--N/A------11/15/06

With the query below, Rec1 and Rec2, for Task1 will both be (blank).

TRANSFORM First(tbl_Milestone_History_for_SR.Date_Completed) AS
FirstOfDate_Completed
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
ORDER BY [Due_Date] & " " & [Task]
PIVOT [Due_Date] & " " & [Task];

Any suggestions you have, is greatly appreciated.
Thank you,
David
 
K

KARL DEWEY

Not really enough data on input and output example but this might do it for
you.

TRANSFORM First(IIf([Date_Completed] Is Null,"N/A",[Date_Completed])) AS Expr1
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst, tbl_Milestone_History_for_SR.Due_Date
ORDER BY tbl_Milestone_History_for_SR.Task
PIVOT tbl_Milestone_History_for_SR.Task;
 
D

David

Karl,
Thank you for the response, unfortunately it did not produce what I was
hoping for.
Here is the data in the table (minus the table to provide the Analyst name)

ID SR Release Task Due_Date Completed Date_Completed
333 Project1 April Task1 3/26/2007 No
334 Project1 April Task2 3/27/2007 No
352 Project2 April Task2 3/26/2007 Yes 11/16/2006

Here is the result of SQL you modified,

Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin N/A
April Project1 Release 3 - Fraud Alvin N/A
April Project2 Release 3 - Service Ericka 11/16/2006

The code posted in my original questions provides this result
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka 11/16/2006


Here is what I'm wanting it to look like
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka n/a 11/16/2006

since there is no Task 1 in the table for Project2.





KARL DEWEY said:
Not really enough data on input and output example but this might do it for
you.

TRANSFORM First(IIf([Date_Completed] Is Null,"N/A",[Date_Completed])) AS Expr1
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst, tbl_Milestone_History_for_SR.Due_Date
ORDER BY tbl_Milestone_History_for_SR.Task
PIVOT tbl_Milestone_History_for_SR.Task;

David said:
Hello ol wise ones,
I'm using Access 2002. Two questions to help improve this crosstab query.
Question1: I have combined the fields due_date and task so the column order
would be correct. Is there a way to keep the sort order by due_date but only
display the field task? I've tried using RIGHT, but as it trims, it also
impact the sort criteria.

Question2: the tasks may be unique for each value in SR field. Can I place
a N/A in the rows which that particular task does not apply? Current it
shows blank cell. I like the blank cell if it is a task for the SR, but no
value in the field Date_Completed.
Example:Rec1 has Task1 in the table. Rec2 does not have Task1 in the table
Table
Record 1
Field SR - Rec1
Field Task - Task1
Date_Completed - (blank)

Record 2
Field SR - Rec1
Field Task - Task2
Date_Completed - 11/15/06

Record 3
Field SR - Rec2
Field Task - Task2
Date_Completed - 11/15/06

(how I would like it to appear)
SR----Task1------Task2
Rec1--(blank)-- 11/15/06
Rec2--N/A------11/15/06

With the query below, Rec1 and Rec2, for Task1 will both be (blank).

TRANSFORM First(tbl_Milestone_History_for_SR.Date_Completed) AS
FirstOfDate_Completed
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
ORDER BY [Due_Date] & " " & [Task]
PIVOT [Due_Date] & " " & [Task];

Any suggestions you have, is greatly appreciated.
Thank you,
David
Click to expand...
Click to expand...
 
G

Gary Walter

PMFBI

it sounds like you are asking for the following
as your TRANSFORM expression:

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,"
",[Date_Completed])),"N/A")

you'll have to remember that a "blank" Task date is actually
a SPACE (not a NULL) in your results...

assumed [Completed] was type Yes/No...

"David" wrote:
Karl,
Thank you for the response, unfortunately it did not produce what I was
hoping for.
Here is the data in the table (minus the table to provide the Analyst
name)

ID SR Release Task Due_Date Completed Date_Completed
333 Project1 April Task1 3/26/2007 No
334 Project1 April Task2 3/27/2007 No
352 Project2 April Task2 3/26/2007 Yes 11/16/2006

Here is the result of SQL you modified,

Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin N/A
April Project1 Release 3 - Fraud Alvin N/A
April Project2 Release 3 - Service Ericka 11/16/2006

The code posted in my original questions provides this result
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka 11/16/2006


Here is what I'm wanting it to look like
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka n/a 11/16/2006

since there is no Task 1 in the table for Project2.





KARL DEWEY said:
Not really enough data on input and output example but this might do it
for
you.

TRANSFORM First(IIf([Date_Completed] Is Null,"N/A",[Date_Completed])) AS
Expr1
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst, tbl_Milestone_History_for_SR.Due_Date
ORDER BY tbl_Milestone_History_for_SR.Task
PIVOT tbl_Milestone_History_for_SR.Task;

David said:
Hello ol wise ones,
I'm using Access 2002. Two questions to help improve this crosstab
query.
Question1: I have combined the fields due_date and task so the column
order
would be correct. Is there a way to keep the sort order by due_date
but only
display the field task? I've tried using RIGHT, but as it trims, it
also
impact the sort criteria.

Question2: the tasks may be unique for each value in SR field. Can I
place
a N/A in the rows which that particular task does not apply? Current
it
shows blank cell. I like the blank cell if it is a task for the SR,
but no
value in the field Date_Completed.
Example:Rec1 has Task1 in the table. Rec2 does not have Task1 in the
table
Table
Record 1
Field SR - Rec1
Field Task - Task1
Date_Completed - (blank)

Record 2
Field SR - Rec1
Field Task - Task2
Date_Completed - 11/15/06

Record 3
Field SR - Rec2
Field Task - Task2
Date_Completed - 11/15/06

(how I would like it to appear)
SR----Task1------Task2
Rec1--(blank)-- 11/15/06
Rec2--N/A------11/15/06

With the query below, Rec1 and Rec2, for Task1 will both be (blank).

TRANSFORM First(tbl_Milestone_History_for_SR.Date_Completed) AS
FirstOfDate_Completed
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
ORDER BY [Due_Date] & " " & [Task]
PIVOT [Due_Date] & " " & [Task];

Any suggestions you have, is greatly appreciated.
Thank you,
David
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

forgot about wordwrapping...
NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
" ",[Date_Completed])),"N/A")

or

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
Chr(32), [Date_Completed])), "N/A")

or

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
"Open", [Date_Completed])), "N/A")


Gary Walter said:
PMFBI

it sounds like you are asking for the following
as your TRANSFORM expression:

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,"
",[Date_Completed])),"N/A")

you'll have to remember that a "blank" Task date is actually
a SPACE (not a NULL) in your results...

assumed [Completed] was type Yes/No...

"David" wrote:
Karl,
Thank you for the response, unfortunately it did not produce what I was
hoping for.
Here is the data in the table (minus the table to provide the Analyst
name)

ID SR Release Task Due_Date Completed Date_Completed
333 Project1 April Task1 3/26/2007 No
334 Project1 April Task2 3/27/2007 No
352 Project2 April Task2 3/26/2007 Yes 11/16/2006

Here is the result of SQL you modified,

Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin N/A
April Project1 Release 3 - Fraud Alvin N/A
April Project2 Release 3 - Service Ericka 11/16/2006

The code posted in my original questions provides this result
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka 11/16/2006


Here is what I'm wanting it to look like
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka n/a 11/16/2006

since there is no Task 1 in the table for Project2.





KARL DEWEY said:
Not really enough data on input and output example but this might do it
for
you.

TRANSFORM First(IIf([Date_Completed] Is Null,"N/A",[Date_Completed])) AS
Expr1
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst, tbl_Milestone_History_for_SR.Due_Date
ORDER BY tbl_Milestone_History_for_SR.Task
PIVOT tbl_Milestone_History_for_SR.Task;

David" wrote:

Hello ol wise ones,
I'm using Access 2002. Two questions to help improve this crosstab
query.
Question1: I have combined the fields due_date and task so the column
order
would be correct. Is there a way to keep the sort order by due_date
but only
display the field task? I've tried using RIGHT, but as it trims, it
also
impact the sort criteria.

Question2: the tasks may be unique for each value in SR field. Can I
place
a N/A in the rows which that particular task does not apply? Current
it
shows blank cell. I like the blank cell if it is a task for the SR,
but no
value in the field Date_Completed.
Example:Rec1 has Task1 in the table. Rec2 does not have Task1 in the
table
Table
Record 1
Field SR - Rec1
Field Task - Task1
Date_Completed - (blank)

Record 2
Field SR - Rec1
Field Task - Task2
Date_Completed - 11/15/06

Record 3
Field SR - Rec2
Field Task - Task2
Date_Completed - 11/15/06

(how I would like it to appear)
SR----Task1------Task2
Rec1--(blank)-- 11/15/06
Rec2--N/A------11/15/06

With the query below, Rec1 and Rec2, for Task1 will both be (blank).

TRANSFORM First(tbl_Milestone_History_for_SR.Date_Completed) AS
FirstOfDate_Completed
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
ORDER BY [Due_Date] & " " & [Task]
PIVOT [Due_Date] & " " & [Task];

Any suggestions you have, is greatly appreciated.
Thank you,
David
Click to expand...
Click to expand...
Click to expand...
 
D

David

Works perfectly, thank you.

Gary Walter said:
forgot about wordwrapping...
NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
" ",[Date_Completed])),"N/A")

or

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
Chr(32), [Date_Completed])), "N/A")

or

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,
"Open", [Date_Completed])), "N/A")


Gary Walter said:
PMFBI

it sounds like you are asking for the following
as your TRANSFORM expression:

NZ(First(IIf([Completed]=0 And [Date_Completed] Is Null,"
",[Date_Completed])),"N/A")

you'll have to remember that a "blank" Task date is actually
a SPACE (not a NULL) in your results...

assumed [Completed] was type Yes/No...

"David" wrote:
Karl,
Thank you for the response, unfortunately it did not produce what I was
hoping for.
Here is the data in the table (minus the table to provide the Analyst
name)

ID SR Release Task Due_Date Completed Date_Completed
333 Project1 April Task1 3/26/2007 No
334 Project1 April Task2 3/27/2007 No
352 Project2 April Task2 3/26/2007 Yes 11/16/2006

Here is the result of SQL you modified,

Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin N/A
April Project1 Release 3 - Fraud Alvin N/A
April Project2 Release 3 - Service Ericka 11/16/2006

The code posted in my original questions provides this result
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka 11/16/2006


Here is what I'm wanting it to look like
Release SR Project_Name Analyst Task1 Task2
April Project1 Release 3 - Fraud Alvin
April Project2 Release 3 - Service Ericka n/a 11/16/2006

since there is no Task 1 in the table for Project2.





:



Not really enough data on input and output example but this might do it
for
you.

TRANSFORM First(IIf([Date_Completed] Is Null,"N/A",[Date_Completed])) AS
Expr1
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst, tbl_Milestone_History_for_SR.Due_Date
ORDER BY tbl_Milestone_History_for_SR.Task
PIVOT tbl_Milestone_History_for_SR.Task;

David" wrote:

Hello ol wise ones,
I'm using Access 2002. Two questions to help improve this crosstab
query.
Question1: I have combined the fields due_date and task so the column
order
would be correct. Is there a way to keep the sort order by due_date
but only
display the field task? I've tried using RIGHT, but as it trims, it
also
impact the sort criteria.

Question2: the tasks may be unique for each value in SR field. Can I
place
a N/A in the rows which that particular task does not apply? Current
it
shows blank cell. I like the blank cell if it is a task for the SR,
but no
value in the field Date_Completed.
Example:Rec1 has Task1 in the table. Rec2 does not have Task1 in the
table
Table
Record 1
Field SR - Rec1
Field Task - Task1
Date_Completed - (blank)

Record 2
Field SR - Rec1
Field Task - Task2
Date_Completed - 11/15/06

Record 3
Field SR - Rec2
Field Task - Task2
Date_Completed - 11/15/06

(how I would like it to appear)
SR----Task1------Task2
Rec1--(blank)-- 11/15/06
Rec2--N/A------11/15/06

With the query below, Rec1 and Rec2, for Task1 will both be (blank).

TRANSFORM First(tbl_Milestone_History_for_SR.Date_Completed) AS
FirstOfDate_Completed
SELECT tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
FROM tbl_Milestone_History_for_SR INNER JOIN tbl_Projects ON
tbl_Milestone_History_for_SR.SR = tbl_Projects.SR
GROUP BY tbl_Milestone_History_for_SR.Release,
tbl_Milestone_History_for_SR.SR, tbl_Projects.Project_Name,
tbl_Projects.Analyst
ORDER BY [Due_Date] & " " & [Task]
PIVOT [Due_Date] & " " & [Task];

Any suggestions you have, is greatly appreciated.
Thank you,
David
Click to expand...
Click to expand...
Click to expand...
 
Top