Help with counting info in a database

[Riaang]

Hi, any help wil be appreciated. I have a lot of columns in my database but I want to work on these columns only. What I need is to add subtotals to the below. Eg after each membership number starting with 143 I want the count of all the Ben Type Code = to PM and the all that does not = to PM. Tha same goes for all the numbers starting with 144 and so on.

Member Number Ben Type Code
14300000931 PM
14300000831 SPO
14300000842 PM
14300820002 STU
14300820800 CHI
14400000739 PM
14400280022 PM
14400280022 SPO
14400200284 PM

My result for the above should be.
143 PM = 2
143 Not PM = 3
144 PM = 3
144 Not PM = 1.

Thanks.

 

[Philem]

Hullo. Well, you should be able to get close to your desired goal if you design a report that has a grouping level based on LEFT(3,[Member Number]), and have two group totals PM and Non-PM.

HTH

P

 

[Monkey]

This should do the trick


SELECT Left([member],3) AS FIRSTTHREE, IIf([BEN]="PM","PM","NOTPM") AS BENtype, Count(Tabel1.BEN) AS COUNTBEN
FROM Tabel1
GROUP BY Left([member],3), IIf([BEN]="PM","PM","NOTPM");

I've called your table(Tabel1) and your fields different Change them accordingly

Greetings Monkey

 

[Riaang]

Hi Monkey,

Sorry for sounding stupid but how and where do I use it in access?

Regards