need to summarize the output according to location

[str1979]

My query



TRANSFORM Count(Sheet1.[No]) AS CountOfNo
SELECT DISTINCT Sheet1.[Q-1], Sheet1.[Q-2], Sheet1.[Q-3], Sheet1.Q4, Data.CommunityName
FROM Data INNER JOIN Sheet1 ON Data.CommunityName = Sheet1.CommunityName
WHERE (((Sheet1.[Q-1])="Completed")) OR (((Sheet1.[Q-2])="Completed")) OR (((Sheet1.[Q-3])="Completed")) OR (((Sheet1.Q4)="Completed"))
GROUP BY Sheet1.[Q-1], Sheet1.[Q-2], Sheet1.[Q-3], Sheet1.Q4, Data.CommunityName
PIVOT Data.CommunityName;



rest attached the files and output required

[CJ_London]

I don't understand the question or the result you want.

The code above shows a count, whilst your result shows text.

The code above includes criteria to only include 'completed', yet your result shows values of 'ongoing' and 'not yet started'

Appreciate it is your first time here, but you need to be clearer about what you have, where you want to get to and what you have tried. You also need to be consistent - in one place it is Q-1, in another Q1 and again Vatsalya-Maseera Hameer II or Vatsalya 2? are these the same?

You know your business, we don't. Please explain in simple in English what your business does.

My guess is you are trying to apply a crosstab to a 4 column query as a pivot. Cross tabs only return a value for a single column, not 4

for a crosstab your data should look like

Community..Question..Status
Vatsalya......Q-1.........Completed
Vatsalya......Q-3.........Ongoing
...
...

[str1979]

I had attached database in original post.
New to access.

I need summarized output where according to unique location the result is displayed.
There are 4 quarters where every quarter result is summarized according to changing location.
There are 3 values: completed, on going and yet not completed.
The first priority is given to completed. second to on going and third to not yet completed.

In a given location if I want to see that in my first quarter how many projects are completed. Suppose, if 13 locations are there and each location has different values but even one of the single location has completed. we consider it as a completed and likewise same applies to all the locations and quarters.

There are 16 main locations and every location has 120 to 130 sub locations. We need to compare each and every sub location with the corresponding single location
and find the result and comprising of all the sub location. The result will be final for the main location.

Hope I am able to explain the problem.


Location Q1 Q2 Q3 Q4
Delhi completed completed on going not yet started

Mumbai completed on going not yet started not yet started

[CJ_London]

As I said in my first post, you need to store the data as

Community..Quarter..Status
Vatsalya......Q-1.........Completed
Vatsalya......Q-3.........Ongoing
...
...

You cannot do what you want with the data stored as it is at the moment - it is not normalised, it is an Excel structure. Access is a database and requires a different structure for storing data

[str1979]

can the output that I require can be done by using access

[CJ_London]

You can get something like what you want with

SELECT DISTINCT Sheet1.CommunityName, Min(Sheet1.[Q-1]) AS [MinOfQ-1], Min(Sheet1.[Q-2]) AS [MinOfQ-2], Min(Sheet1.[Q-3]) AS [MinOfQ-3], Min(Sheet1.Q4) AS MinOfQ4
FROM Sheet1
GROUP BY Sheet1.CommunityName;

but you need to replace 'Ongoing' with a word that sorting wise is between Completed and Not Yet Started - perhaps 'Continuing'?

[CJ_London]

try this - it converts Ongoing to Continuing as it runs

SELECT DISTINCT Sheet1.CommunityName, Min(IIf([Q-1]="Ongoing","Continuing",[Q-1])) AS Qtr1, Min(IIf([Q-2]="Ongoing","Continuing",[Q-2])) AS Qtr2, Min(IIf([Q-3]="Ongoing","Continuing",[Q-3])) AS Qtr3, Min(IIf([Q4]="Ongoing","Continuing",[Q4])) AS Qtr4
FROM Sheet1
GROUP BY Sheet1.CommunityName;

[str1979]

Thanks Ajax!

This solution worked.