Problem with expression 'count(distinct())' in Access, how to resolve?

[CHeighlund]

I'm working on a Delphi program that is supposed to interface with an Access Database. As far as I can tell, the version is Access 2000.

I'd been developing on a box which doesn't have Access, so I'd copied the information over to a PostgreSQL database and attempted to work from that. I swapped my code over to a box with Access today for a test run, and I'm getting blown up in the code at one point.

I was using the following code on the PostgreSQL side:

select count(distinct(deptcd)) as foo from sessionD2;

The purpose of this code was to select the number of unique values in the field. Although the database handling seemed to go well with an earlier statement of 'select distinct(deptcd) from sessionD2', the count(distinct()) throws out an error reading "undefined function 'distinct' in expression".

How can I code my statement to get the same effect in Access as I had before with PostgreSQL?

 

[lespaul]

Access lacks the Count Distinct capability, maybe something like this:

SELECT COUNT(*) As CountOfFoo FROM (SELECT DISTINCT DEPTCD FROM SessionD2)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[lespaul]

another thought:
depending on what you are doing with the distinct list of department codes you could use a TStringList with duplicates set to no.

var
DepartmentList : TStringList;
begin
  DepartmentList := nil;
  try
    DepartmentList := TStringList.Create;
    DepartmentList.Sotred = True;
    DepartmentList.Duplicates := dupIgnore;
    With qYourQuery do
    begin
      //run query
      while not eof do
      begin
        DeparmentList.Add(FieldByName('DEPTID').AsString;
        Next;
      end;
    end;
  finally
    DepartmentList.Free;
  end;
end;

Leslie

 

[CHeighlund]

Thanks for the advice; the first one is more like what I'm looking for, and I'll probably swap to it. I ended up juryrigging the code by adding to a variable during the 'while not eof' block, then using the variable instead of the results, but that adds time and processing that I'd rather have on the database side. Again, thanks for the information.