query returns in different columns projects from different phases

query returns in different columns projects from different phases

Post by AntonioMac » Thu, 19 Jul 2007 03:36:05


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 say the query result would be:

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

But I couldn do this. I don know if it possible because I don 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,
Antnio Machado.

--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

query returns in different columns projects from different phases

Post by S2xhdHV » Thu, 19 Jul 2007 03:56:00

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.
--
Dave Hargis, Microsoft Access MVP

 
 
 

query returns in different columns projects from different phases

Post by AntonioMac » Sun, 22 Jul 2007 09:47:17

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!




--
Message posted via http://www.yqcomputer.com/
 
 
 

query returns in different columns projects from different phases

Post by AntonioMac » Thu, 26 Jul 2007 04:50:06

Anyone can help me with this?

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



--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

query returns in different columns projects from different phases

Post by Marshall B » Thu, 26 Jul 2007 13:45:10


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"

--
Marsh
MVP [MS Access]