Count records issue

[EstesExpress]

I need to create a field that counts the number of serial numbers for one location from a table. I have the basic part working but the serial numbers are repeated quite a lot and I only want it to count a serial number once. I tried to use a AND sum([SerialNumber]) at the end of the statement but that was not working.

=DCount("SerialNumber","dbo_HHUsageHistorical","Ar eaDescription = 'Atlanta, GA'")

[CGM3]

A bit roundabaout, but it should work (haven't tested it yet):

 
     ' This should create a virtual table where each separate Serial Number in Atlanta, GA appears once (that's what DISTINCT does)
     Set rsTemp = CurrentDb.OpenRecordset("SELECT DISTINCT Serial Number FROM dbo_HHUsageHistorical WHERE AreaDescription = 'Atlanta, GA'", dbOpenSnapshot)
   rsTemp.MoveLast
   rsTemp.MoveFirst
    ' RecordCount gives the number of records in the table
     iL = rsTemp.RecordCount
     rsTemp.Close

Naturally, you'll have to define rsTemp as a recordset, and iL as a long integer, and apply the latter as required.