Count duplicates 2

[Stroeve]

I'm using CR (6) for about a week now and I'm still having difficulties in achieving the following:
CURRENT REPORT:
Database Fields are SYSTEM, COMPONENT, ITEM, MODULE.
Grouping is set on SYSTEM.

SYSTEM COMPONENT ITEM MODULE

Applications Cognos Powerplay One
Applications Cognos Powerplay One
Applications Cognos Powerplay One
Applications Coral Coral Draw Help
Applications Coral Coral Draw Help

Hardware Desktop Monitor H
Hardware Desktop Monitor H
Hardware Desktop Monitor H
Hardware Laptop Monitor Y
Hardware Laptop Monitor Y

I WOULD LIKE TO MY REPORT TO SHOW THE FOLLOWING RESULT:

SYSTEM COMPONENT ITEM MODULE COUNTNR

Applications Cognos Powerplay One 3
Applications Coral Coral Draw Help 2

Hardware Desktop Monitor H 3
Hardware Laptop Monitor Y 2


Could anyone help me out here?
Thanking you in advance,

Jan Stroeve

 

[AyJayEL]

Write a formula that concatonates the fields. ie {SYSTEM} + {COMPONENT} + {ITEM} + {MODULE}. Next create a group based on the formula. Put a summary in the group footer and suppress the details. Learn something new every day *:->*

 

[Stroeve]

Thanks!

 

[thomasyu]

It seems not a CRW question. Try approach the issue in the SQL Level.

Change your query to
SELECT DISTINCT System, Component, Item, Module, Count(*) AS [counter]
FROM Table1
GROUP BY System, Component, Item, Module;

If you are using SQL server, Oracle, you may create a view to simplify the work performed at CRW level. If you are using MS Access, create a query with the above SQL.

 

[thomasyu]

If you want to print duplicate only, add the following after the group clause.
Group by ....
Having count(*) > 0;

 

[Stroeve]

My database administrator does not allow me to change anything in the database itself. My question is this: can I enter the "Count(*) AS [counter]" part somewhere in CR?

SELECT DISTINCT System, Component, Item, Module,
Count(*) AS [counter]
FROM Table1
GROUP BY System, Component, Item, Module;

Thanks in advance.