Problems with Distinct in access

[Purge]

Hello SQL professionals

I am trying to get rid of duplicates in a column instead of the rows using access 2000. For example.

Here is what is returned to me without Distinct

B 20
A 10
B 40
C 20
D 30
C 10
A 30

Instead I would like something more like this

B 20
A 10
C 20
D 30

Can this be done? Thanks

 

[namliam]

Try using a group by query

(Click the sum sign when you are in query design screen)

Greetz

 

[Purge]

Distinct Query problem

Hi,

Thanks for the suggestion. For some reason or another group by does not work on this situation. I still get the same results.

Here is the SQL code

SELECT DISTINCT StuMod.scode, HealthStat.Date, HealthStat.PatientCode, HealthStat.NPOPID, Session.Supervisor

FROM (HealthStat INNER JOIN StuMod ON HealthStat.PatientCode = StuMod.PatientCode) INNER JOIN [Session] ON HealthStat.PatientCode = Session.PatientCode

GROUP BY StuMod.scode, HealthStat.Date, HealthStat.PatientCode, HealthStat.NPOPID, Session.Supervisor
HAVING (((StuMod.scode)=[Forms]![Query]![scode]) AND ((HealthStat.Date)>=[Forms]![Query Student Treatments]![StartDate] And (HealthStat.Date)<=[Forms]![Query Student Treatments]![EndDate]) AND ((HealthStat.NPOPID)="N/P"));

scode Date PatientCode NPOPID Supervisor
9830 01/03/2004 4931 N/P DR Jones
9830 19/03/2004 4930 N/P Dr Raul
9830 19/03/2004 4930 N/P Dr Mat

As you can see I do not want the patient code 4930 to apear twice, Just the first instance of it. Of course no new enteries to this database have been added, but I expect the next Patientcode to be shown would be 4929.

Thanks

 

[namliam]

You are using supervisor in your distinct/group by since it difers its a unique row. thus double output.....

Nothing you can much do, exept if you dont need it you can take it out of the query or change that field to First or Min or Max or something

Greetz