Count/sum of records in query

[shafara7]

Hi, I want to create a query that will show the total number recordsthat have a specific methods. For example see photo.
I have multiple number of Project with the same name but the methods are sometimes differs and sometimes the same.

I know in the query I have to Group the Projects together, but I don't know how to create a field where it will count/summarize the number of Methods used by the same Project.
Can anybody explain how?

 

[JHB]

Use a crosstab query, if the table name is "tblProject".

TRANSFORM Count(MethodeID) AS CountOfMethodeID
SELECT [Project]
FROM tblProject
GROUP BY [Project]
PIVOT "Method " & [MethodeID];

 

[shafara7]

Thank you JHB. That sql code works like a charm
Just a question more. The values from the table is actually far more complicated than that.
The MethodID actually have more than 2 type. Let's say..

MethodID
xx
xx
yy
yy
aa
bb

The Methods xx and aa belongs to the same group and so do Methods yy with bb.
With your given sql, I will have have 4 column, one for each method.
But how do I create only 2 column, which consist of the groupped method?

In the query field I tried to add the following:

Inspection: "Method " & [MethodID]="xx" Or "aa"

On another field:
Analysis: "Method " & [MethodID]="yy" Or "bb"

But it does not working, saying that I can only have one CrossTab Column.
Do you know where I should put that code? Or is that code wrong?

 

[JHB]

It is a problem when people doesn't mention the whole requirements in their first post, then they'll some solution that doesn't fulfilled what they want.
Could you show it like you've done in the first post - the data and the wanted result, then I'll look at it if it is possible.

 

[shafara7]

I'm sorry JHB. I thought I had mention all the requirement, but I just realised the complication after seeing the result of the query using the CrossTab query.

So, Method A is composed of MethodID 1 and 6 while Method B is composed of MethodID 2 and 7.

Would it be easier if I just add a new field called MethodType on my tblMethod and then instead of using MethodID as the column, I use MethodType.?
Or do you have any other alternative?

 

[JHB]

I'm sorry JHB. I thought I had mention all the requirement, but I just realised the complication after seeing the result of the query using the CrossTab query.

Yes sometimes we forget how it actually should look like.

Or do you have any other alternative?

I think I've an alternative, but I haven't more time today, I'll come back tomorrow morning.

 

[JHB]

Run the query in the attached database.

 

[shafara7]

Thank you very much JHB! That is exactlx what I need.

 

[JHB]

You're welcome, good luck.