I need to bulit a report and in the database it have some same category item. I need the report to display just distinct item in the database and also doing some counting on the occurence of data depending on the category. The problem that I have is when I use distinct in the SQL it does not count the number of occurences of each category. So how can I cope with this problem?

just continue using the distinct in your SQL... just add a column to count your record which is something like this.

SELECT blah, blah, (SELECT COUNT(*) FROM <yourTable> WHERE Category=[<yourTable>].[Cagegory]) AS Cnt FROM <yourTable>

or add a group on your report then put your report fields at the group Header or Footer not to the detail section...

____________________________
BeAr

Hi,
Try:

SELECT MyCategory, count(*) as OccurenceCount
FROM MyTable
GROUP BY MyCategory

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Below is my SQL Statement with doy_nick solution

SELECT DISTINCT KODRUJUKAN.NAME AS JenisRekod,  TERIMA.JENISREK, TERIMA.USERNAMA,  (SELECT COUNT(*) FROM TERIMA WHERE JENISREK=TERIMA.JENISREK) AS Cnt
FROM SUMBER, PENGUJUD, KODKEADAAN, KODSYARAT, KODJENISUKUR, KODUKURAN, TERIMA, KODRUJUKAN
WHERE  SUMBER.KODPUJUD=TERIMA.SUMBER AND PENGUJUD.KODPUJUD=TERIMA.PENGUJUD AND KODSYARAT.CODE=TERIMA.SYARAT AND KODKEADAAN.CODE=TERIMA.KEADAAN AND KODRUJUKAN.CODE=TERIMA.JENISREK AND KODJENISUKUR.INDEX_NO=4 AND KODJENISUKUR.CODE=TERIMA.JENIS_UKUR AND KODUKURAN.INDEX_NO=5 AND KODUKURAN.CODE=TERIMA.UKURAN ORDER by KODRUJUKAN.NAME,Usernama

but it cannot be done since:
1.it gave me an error
2.when i paste on the SQL Queries in Access in return me all the same value

so can you correct me where I gone wrong?

with stcikleprojects solution I have an error, something to do with aggregation (what the is that?)..help me with with this one would you guys..Thanks in advance

can i ask you something? Why you include tables other than KODRUJUKAN and TERIMA? I know you have reason for that but as I have check your query, you only return fields which is from KODRUJUKAN and TERIMA tables.

By the way, just try my code below...

SELECT KODRUJUKAN.NAME AS JenisRekod,  TERIMA.JENISREK, TERIMA.USERNAMA, (TERIMA.JENISREK) AS Tmp  (SELECT COUNT(*) FROM TERIMA WHERE JENISREK=[Tmp]) AS Cnt
FROM KODRUJUKAN, TERIMA WHERE  KODRUJUKAN.CODE=TERIMA.JENISREK ORDER by KODRUJUKAN.NAME,TERIMA.Usernama


____________________________
BeAr

sorry, my mistake to include that table..have change it already yesterday as i checked it (because I'm continuing othe people's work).. just one question though...no distinct value??or I just have to group them inside the crystal report and include it in the group section??

Yup! just group them inside the crystal report... hope this will work in your project...

____________________________
BeAr