hey guys sorry to trouble again. but i found another mistake in my database.


seems like some of the queries have exactly the same data which i didnt notice.


is there anyway to pull out one of them instead of having the query display 2 of them ?

You could use the Keyword DISTINCT
select distinct * from table
or a group by
select field1, field2 from table group by field1, field2
both will filter all doubles from the result.

HTH:D

oh got it ! thanks.


but my problem somehow got bigger.

let me explain.

my current query draws out at least 25 fields of data.

so sometimes the person entering the data might be too tired and enter the same data twice but a single field different.

for example :

the first data looks like this:

Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-5E8A2K| 1-32640 | Ang Kim Mui | BCHKLOI

the second data looks like this:
Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-8FLRNS| 1-32640 | Ang Kim Mui | BCHKLOI


the third data looks like this:
Contact CIN | ID | Name | Created by (name) | Created by (login id)
S1234567D | 1-5E8A2K| 1-42456 | Ang Kim Mui | BCHKLOI




basically what i'm using to search the data is the Contact CIN. Thus when i search for the Contact CIN S1234567D. All this 3 data will come out. Is there a possible way just to make the query just display one of the 3 records instead of all 3 ?

It does not matter which record is displayed

You can do a SELECT UNIQUE {fieldname} on any field. If it is not an indexed field, I believe that is a somewhat less efficient query than it would be for indexed fields. However, it we aren't talking about lots of records, don't sweat the speed difference.

hi The_Doc_Man. i've searched for the UNIQUE function in microsoft access. doesnt seem to appear anywhere thought.

here is my code. is there anyway to implement the unique function ?



SELECT Query2.[Opty Id], Query2.[Opty Name], SJ_CIS.NEWCINSFX, Query2.[Contact CIN], Query2.[Contact CIN SFX], Query2.[Create By (Name)], Query2.[Created By (Login)], Query2.[Opty Created Date], SJ_CIS.JOIN_DATE, Query2.[Opty Closed Date], Query2.[Closed By (Emp #)], Query2.[Referral - Employee #], Query2.[Referral - Employee], Query2.[Sales Rep], Query2.Product

FROM Query2 INNER JOIN SJ_CIS ON [Query2].[Contact CIN] & [Query2].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

WHERE ((CDate([Query2].[Opty Created Date]) < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < CDate([Query2].[Opty Closed Date])));