Counting P.K

[COMP]

Hi i have a database with different primary keys for example

ANUK1234 2mb
ANUK456 2mb
ANUK789 4mb
SHUK8797
SHU7854


Now they all have different bandwith. Now i was wondering what code i need to input in order to get it to show me this:

ANUK = 2 at 2mb
ANUK = 1 at 4mb

Any ideas??

 

[namliam]

you just wnat the first four chars? Left (yourfield,4) will do that for you.

 

[COMP]

no i have quite alot of ANUKS so i want to show how many ANUKS there are that have 2mb connection. SOmething along this.

 

[namliam]

like I said use a left function and "group by" query then use a count to find your answer...

 

[COMP]

ok i am not too sure on where to put these pieces of code. In the query i have circuit reference which hasd all the ANUKS456 MXLFX897 etc and the circuit banwith field. Where do i put the left(Circuitreference,4)?? and the count function??

thanks

 

[Brianwarnock]

Is this ANUK1234 2mb one field?

If so then try

Totalanuk2mb:sum(iif(left(yourfield,4="ANUK" and Right(yourfield,3)="2mb",1,0) and ditto for 4mb

Brian