Percentage in Group by 1

[jslmvl]

There is an Access Table1:
Field1 Field2
A a
B b
A a
C a
A b
C b
A a

I want to find the percentages :
A a 75%
b 25%
B b 100%
C a 50%
b 50%

Perhaps use Select Count Grop By....? Can you tell me how?

Thank you in advance.

 

[PHV]

One way:

SELECT A.Field1, A.Field2, Format(Count(*) /B.Count1,'PERCENT') AS Percentage
FROM yourTable AS A INNER JOIN (
SELECT field1, Count(*) AS Count1 FROM yourTable GROUP BY Field1
) AS B ON A.field1 = B.Field1
GROUP BY A.Field1, A.Field2, B.Count1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

Thanks a lot!

By VB6+Access, got error

Tun-time error '32656':
Item cannot be found in the collection corresponding to the requested name or ordinal.

 

[lespaul]

what's the actual code/sql that's giving the error? You changed all the Field# to your field names and yourTable to your table name?

Leslie

Have you met Hardy Heron?

 

[jslmvl]

What I used was something like
objConnection.Open ......
strSQL = "SELECT A.Field1, A.Field2,...."
objRecordset.Open strSQL,objConnection

Yes, I used my field names and table name....otherwise I would get other error...

 

[PHV]

Which line of code is highlighted when in debug mode at the time the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

This line:
objRecordset.Open strSQL,objConnection

 

[PHV]

So, what is the real value of strSQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

In the mdb file, there is a table Table2 with 2 fields Field1 and Field2, both are formated as text.

strSQL = _
"SELECT A.Field1, A.Field2, Format(Count(*) /B.Count1,'PERCENT') AS Percentage " & _
"FROM Table2 AS A INNER JOIN ( " & _
"SELECT field1, Count(*) AS Count1 FROM Table2 GROUP BY Field1 " & _
") AS B ON A.field1 = B.Field1 " & _
"GROUP BY A.Field1, A.Field2, B.Count1

 

[jslmvl]

PHV,

Sorry, your code is working fine! I don't know what I did and got the error.

However, I don't understand how the code works.....for example, why we can use B.Count1 before we create B latter?