query returns in different columns projects from different phases

[AntonioMachado via AccessMonster.com]

I have a tblProject that contains projects and their phase of execution.

Each project can be in one of the following three phases:

Portfolio, Pre-project or Execution

I want to show a table in a form containing the names of projects in each
phase.

I thought of using a query where the columns are the project phases and the
query would return the names of the projects in each of these phases.

Let’s say the query result would be:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1 Projetct4 Projetct5
Project2 Project7 Projetct6
Projetct3 Projetct8
Project9

But I couldn’t do this. I don’t know if it’s possible because I don’t know if
a query can apply criteria to show in different columns (Portfolio, Pre-
Project and Execution) values from the same table column (column Phase from
tblProject).

Can anyone help me?

Thank you very much,
Antônio Machado.

 

[Guest]

SELECT IIf([ProjectPhase] = "Portfolio", [Project Name], Null) As Portfolio,
IIf([ProjectPhase] = "Pre-Project", [ProjectName], Null) As Pre-Project,
IIf([ProjectPhase] = "Execution", [ProjectName], Null) As Execution FROM
tblProject.

You will need t substitue the correct names, but this is the basic concept.

 

[AntonioMachado via AccessMonster.com]

Thank you very much. It works, but partially.

Now, instead of having the result below:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1 Projetct4 Projetct5
Project2 Project7 Projetct6
Projetct3 Projetct8
Project9


I have something like:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1
Project2
Projetct3
Project4
Project7
Project5
Project6
Project8
Project9

I understand that the query is returning all results that I want, but it
doesn't use the same row for showing them in different columns. I guess this
happens because each project is one row in tblProjects.

If it used rows 1 to 3 to show projects 1 to 3 in a specific phase, it will
use row 4 and on to show the remaining for the other phases... I couldn't
make it not show a row that has no project in a phase.

Thank you again!

SELECT IIf([ProjectPhase] = "Portfolio", [Project Name], Null) As Portfolio,
IIf([ProjectPhase] = "Pre-Project", [ProjectName], Null) As Pre-Project,
IIf([ProjectPhase] = "Execution", [ProjectName], Null) As Execution FROM
tblProject.

You will need t substitue the correct names, but this is the basic concept.

I have a tblProject that contains projects and their phase of execution.

[quoted text clipped - 25 lines]

Thank you very much,
Antônio Machado.

 

[AntonioMachado via AccessMonster.com]

Anyone can help me with this?

Thank you
Antonio Machado
-------------

Thank you very much. It works, but partially.

Now, instead of having the result below:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1 Projetct4 Projetct5
Project2 Project7 Projetct6
Projetct3 Projetct8
Project9

I have something like:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1
Project2
Projetct3
Project4
Project7
Project5
Project6
Project8
Project9

I understand that the query is returning all results that I want, but it
doesn't use the same row for showing them in different columns. I guess this
happens because each project is one row in tblProjects.

If it used rows 1 to 3 to show projects 1 to 3 in a specific phase, it will
use row 4 and on to show the remaining for the other phases... I couldn't
make it not show a row that has no project in a phase.

Thank you again!

SELECT IIf([ProjectPhase] = "Portfolio", [Project Name], Null) As Portfolio,
IIf([ProjectPhase] = "Pre-Project", [ProjectName], Null) As Pre-Project,

[quoted text clipped - 7 lines]

 

[Marshall Barton]

I have a tblProject that contains projects and their phase of execution.

Each project can be in one of the following three phases:

Portfolio, Pre-project or Execution

I want to show a table in a form containing the names of projects in each
phase.

I thought of using a query where the columns are the project phases and the
query would return the names of the projects in each of these phases.

Let’s say the query result would be:

PORTFOLIO PRE-PROJECT EXECUTION
Projetct1 Projetct4 Projetct5
Project2 Project7 Projetct6
Projetct3 Projetct8
Project9

But I couldn’t do this. I don’t know if it’s possible because I don’t know if
a query can apply criteria to show in different columns (Portfolio, Pre-
Project and Execution) values from the same table column (column Phase from
tblProject).

This would be a nightmare to do in a query.

However, you could use three continuous (or datasheet)
subforms. Set each subform's record source to something
like:

SELECT Project FROM table WHERE Phase = "Portfolio"