Count/sum of records in query (1 Viewer)

shafara7 · Jul 19, 2017

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 · Jul 19, 2017

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

Code:

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

shafara7 · Jul 19, 2017

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:

Code:

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 · Jul 19, 2017

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 · Jul 19, 2017

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 · Jul 19, 2017

shafara7 said:
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.

shafara7 said:
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 · Jul 20, 2017

Run the query in the attached database.

shafara7 · Jul 20, 2017

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

JHB · Jul 20, 2017

You're welcome, good luck.

Count/sum of records in query (1 Viewer)

shafara7

Registered User.

Attachments

JHB

Have been here a while

shafara7

Registered User.

JHB

Have been here a while

shafara7

Registered User.

Attachments

JHB

Have been here a while

JHB

Have been here a while

Attachments

shafara7

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread