Counting the Repetition of a Text Field in the Same Table

[BinuKJ]

I have a table which can have records almost repeated set of records except the difference of the a filed which is a flag.
this flag can have either a value CSI or CCN . I want to count the no CCN records of respective CSI records and show with this

Eg: Records come like

Charge1 200.00 34344 CSI
Charge2 150.25 34345 CSI
Charge1 75.00 562 CCN
Charge1 25.00 563 CCN
Charge2 65.00 564 CCN

I want to fetch a records should like
Charge1 200.00 CSI 2 (CCN Count of the Charge1)

[nico5038]

When you just want CSI and CSS, use:

select CSI-CCN_field, count( CSI-CCN_field) as CountOfCSI-CCN_field 
from tblYours
Group By CSI-CCN_field;

This will give:
CSI 2
CCN 3

Nic;o)

[NeoPa]

I've assumed (as you didn't say) a table layout as below and that there is always one CSI record to a group of CCN records.

Table Name = [MyTable]
Code; Text
Charge; Currency
Flag; Numeric
Type; Text (CSI or CCN)

[code]SELECT CSI.Code,
CSI.Charge,
CSI.Type,
Count(CCN.Code) AS CCNCount
FROM MyTable AS CSI LEFT JOIN
MyTable AS CCN
ON CSI.Code=CCN.Code
WHERE ((CSI.Type='CSI') AND (CCN.Type='CCN'))
GROUP BY

[BinuKJ]

CSI is the definite record and happened only once against each Unique JOB. CCN can occur any no of times with each JOB so when i want to see each job that with CSI values want to see how many CCN also generated against that JOB

I've assumed (as you didn't say) a table layout as below and that there is always one CSI record to a group of CCN records.

Table Name = [MyTable]
Code; Text
Charge; Currency
Flag; Numeric
Type; Text (CSI or CCN)

[code]SELECT CSI.Code,
CSI.Charge,
CSI.Type,
Count(CCN.Code) AS CCNCount
FROM MyTable AS CSI LEFT JOIN
MyTable AS CCN
ON CSI.Code=CCN.Code
WHERE ((CSI.Type='CSI') AND (CCN.Type='CCN'))
GROUP BY

[NeoPa]

Have you tried the SQL code I posted?
If so, what results did you get?