Group results in query

A

AntonioRio

I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
 
K

Klatuu

Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
 
J

John Spencer

Use a calculated field for Area

Field: Area: IIF([YourTable].[Area] in ("DIFN","DIFS"), {YourTable].[Area],
"Others")


If you get a circular reference error, the change the alias of the field to
"theArea" instead of "Area"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

KARL DEWEY

Try this ---
SELECT Query_Projects.Phase, IIf([Area]<>"DIFN" And
[Area]<>"DIFS","Other",[Area]) AS Areas, Sum(Query_Projects.CountOfPhase) AS
Total
FROM Query_Projects
GROUP BY Query_Projects.Phase, IIf([Area]<>"DIFN" And
[Area]<>"DIFS","Other",[Area]);
 
J

John Spencer

TYPO

Had { instead of [

Field: Area: IIF([YourTable].[Area] in ("DIFN","DIFS"), [YourTable].[Area],
"Others")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
Use a calculated field for Area

Field: Area: IIF([YourTable].[Area] in ("DIFN","DIFS"),
{YourTable].[Area], "Others")


If you get a circular reference error, the change the alias of the field
to "theArea" instead of "Area"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

AntonioRio said:
I have a table containing several Projects, Areas (where project takes
place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So
in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
 
A

AntonioRio

Thank you, but it's working partially.

Now I get "Others" in the "Area" field with the sum of DIMB, DILO and all
other areas that I have on the table. This is perfect.

But DIFN and DIFS also appear grouped now. I need the three areas separate
(DIFN, DIFS and Others).

Now I get:

Project Area CountOfPhase
Pre-project 11 (It grouped DIFN and DIFS. wrong)
Pre-project Others 2 (It grouped DIMB and DILO. right!)
Portfolio 7 (It grouped DIFN and DIFS)
Portfolio Others 9
Execution 4 (It grouped DIFN and DIFS)
Execution Others 9

Thank you.

Klatuu said:
Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
--
Dave Hargis, Microsoft Access MVP


AntonioRio said:
I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
 
A

AntonioRio

Thank you, but it's working partially.

Now I get "Others" in the "Area" field with the sum of DIMB, DILO and all
other areas that I have on the table. This is perfect.

But DIFN and DIFS also appear grouped now. I need the three areas separate
(DIFN, DIFS and Others).

Now I get:

Project Area CountOfPhase
Pre-project 11 (It grouped DIFN and DIFS. wrong)
Pre-project Others 2 (It grouped DIMB and DILO. right!)
Portfolio 7 (It grouped DIFN and DIFS)
Portfolio Others 9
Execution 4 (It grouped DIFN and DIFS)
Execution Others 9

Thank you.

Klatuu said:
Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
--
Dave Hargis, Microsoft Access MVP


AntonioRio said:
I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
 
K

Klatuu

Sorry, but I don't see how that could be happening. The code should produce
3 groups, one for DIFN, one for DIFS, and one for everything else.
--
Dave Hargis, Microsoft Access MVP


Klatuu said:
Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
--
Dave Hargis, Microsoft Access MVP


AntonioRio said:
I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
 
A

AntonioRio

In the meantime, I tried. IIf([Area]="DIFN";"DIFN";IIf([Area
="DIFS";"DIFS";"Other"))

Now If area is equal to DIFN, it keeps DIFN ungrouped in the area field. The
same for DIFS. Everything else is grouped in "Other".

Thanks!
-----------------------

Klatuu said:
Sorry, but I don't see how that could be happening. The code should produce
3 groups, one for DIFN, one for DIFS, and one for everything else.
--
Dave Hargis, Microsoft Access MVP


Klatuu said:
Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
--
Dave Hargis, Microsoft Access MVP


AntonioRio said:
I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
Click to expand...
 
A

AntonioRio

I've worked out how to do this. Now I need a textbox in a form to bring the
quantity of projects in a certain Phase AND in a certain Area.

I tried
=DLookUp("[CountOfPhase]";"[Query_Projects]";"[Phase]='portfolio'";[Area]='"DIFN")

in order for the textbox to return the quantity (CountOfPhase) for projects
in Portfolio phase AND that takes place in DIFN area.

But this is not working.

Thank you for your time.
Antonio Machado.



Klatuu said:
Sorry, but I don't see how that could be happening. The code should produce
3 groups, one for DIFN, one for DIFS, and one for everything else.
--
Dave Hargis, Microsoft Access MVP


Klatuu said:
Change the field that is Area to a Calculated field and use an IIf statement
to load the desired value in the field:

TheArea: IIf([Area] = "DIFN" Or [Area] = "DIFS", [Area], "Other")
--
Dave Hargis, Microsoft Access MVP


AntonioRio said:
I have a table containing several Projects, Areas (where project takes place)
and projects Phases.

I made a query that Counts the total of projects in each area AND phase:

Query_Projects:

Phase Area CountOfPhase

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project DIMB 1
Pre-project DILO 1
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio DIMB 5
Portfolio DILO 4
Execution DIFN 2
Execution DIFS 2
Execution DIMB 3
Execution DILO 6

But I want this query to group results that are not from DIFN or DIFS. So in
this example, I would have DIMB + DILO projects added together, as shown
below:

Pre-project DIFN 3
Pre-project DIFS 8
Pre-project Others 2
Portfolio DIFN 6
Portfolio DIFS 1
Portfolio Others 9
Execution DIFN 2
Execution DIFS 2
Execution Others 9

How do I code this?

Thanks
Antonio Machado
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return certain row result from a Query 1
query returns in different columns projects from different phases 4
Bind TextBox to a specific row in a query 4
Bind a TextBox to a specific row in a query 0
Bind a Textbox in a Form 2
Crosstab Query based on 2 Combo Boxes. How? 17
Enterprise Information Management 0
Subquery not integrating to query to display previous sum of quantities 3

Top