Programming Forums - View Single Post

xavier · Jun 6th, 2007, 8:55 AM

Apparently Access it's just weird

. It doesn't support count(distinct .. etc).

You could do this : http://allenbrowne.com/ser-66.html.
Or, you could Have an inner select , and count that:

 sql Syntax (Toggle Plain Text) 
SELECT count(*) FROM
(SELECT DISTINCT  [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND  SFtoFD.NAMEB IS NULL;) AS a
select count(*) from
(SELECT DISTINCT  [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND  SFtoFD.NAMEB IS NULL;) as a

Jun 6th, 2007, 8:55 AM	#10
xavier Professional Programmer Join Date: Oct 2004 Location: .ro Posts: 381 Rep Power: 4	Apparently Access it's just weird . It doesn't support count(distinct .. etc). You could do this : http://allenbrowne.com/ser-66.html. Or, you could Have an inner select , and count that: sql Syntax (Toggle Plain Text) SELECT count() FROM (SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;) AS a select count() from (SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;) as a __________________ Don't take life too seriously, it's not permanent !