Hi,
SQL defines Total queries with the use of an aggregate, with or without
GROUP BY clause. An aggregate can be COUNT(*), COUNT(fieldName),
SUM(fieldName), MIN(fieldName), MAX(fieldName) , ... ,
DISTINCTCOUNT(fieldName) , ... A field name can be replaced by an
expression involving one or many fields or constants. Not all aggregate are
supported by all engines. JET does not support DISTINCTCOUNT, so you have to
mimic it like:
SELECT COUNT(*) , whatever
FROM (SELECT DISTINCT fieldName, whatever FROM myTable GROUP BY whatever)
GROUP BY whatever
which is equivalent to
SELECT DISTINCTCOUNT(fieldName), whatever
FROM myTable
GROUP BY whatever
(note that exact MS SQL Server syntax would be : SELECT COUNT(DISTINCT
fieldname), whatever FROM myTable GROUP BY whatever).
(note: reserved keyword are allcap, other words are user/designed names)
If you have no GROUP BY, the whole table is assumed to make *one* group:
SELECT COUNT(*)
FROM (SELECT DISTINCT fieldName FROM myTable)
is equivalent to
SELECT DISTINCTCOUNT(fieldName)
FROM mytable
A UNION query removes the duplicate, unless you use UNION ALL
SELECT fieldName, whatever FROM myTable
UNION
SELECT fieldName, whatever FROM myTable
is thus equivalent (as result, not as execution time) to
SELECT DISTINCT fieldName, whatever FROM myTable
A Total query is more versatile than a DISTINCT since it can differentiate
what can vary (the groups) and which field are to be considered as
containing duplicated data:
SELECT Monthly, LAST(employee)
FROM myTable
GROUP BY Monthly
which returns just one (more or less randomly selected) record *for each*
Monthly value (so, 12 records in all, if Monthly has possible integer values
from 1 to 12), but
SELECT LAST(Monthly), LAST(employee)
FROM myTable
returns just one record (more or less randomly).
Hoping it may help,
Vanderghast, Access MVP
