Access/SQL question

[Darth Vodka]

allo

anyone know how to do an aggregate function that counts unique records?

?

btw count(DISTINCT fldWhatEver) works in transact SQL but not Access SQL

ho hum

 

[Steven]

allo

anyone know how to do an aggregate function that counts unique records?

?

btw count(DISTINCT fldWhatEver) works in transact SQL but not Access SQL

ho hum

Something like this?

SELECT PastCashFlows.VAL, PastCashFlows.ECH, PastCashFlows.ART, PastCashFlows.PU1, PastCashFlows.PU2
FROM PastCashFlows
WHERE (((PastCashFlows.VAL) In (SELECT [VAL] FROM [PastCashFlows] As Tmp GROUP BY [VAL] HAVING Count(*)=1 )))
ORDER BY PastCashFlows.VAL;

 

[SJN-Fox]

Aaah right. Well, I'm glad we got that one cleared up.

 

[Darth Vodka]

Something like this?

SELECT PastCashFlows.VAL, PastCashFlows.ECH, PastCashFlows.ART, PastCashFlows.PU1, PastCashFlows.PU2
FROM PastCashFlows
WHERE (((PastCashFlows.VAL) In (SELECT [VAL] FROM [PastCashFlows] As Tmp GROUP BY [VAL] HAVING Count(*)=1 )))
ORDER BY PastCashFlows.VAL;

thanx for that

i can what you're up to, but the WHERE would mess my particular one up as i need to count up non unique as well

i've actually done it with 2 queries (essentially the same as nesting another SELECT in there i suppose)

i'm a bit gutted that Access doesn't support COUNT ( DISTINCT... cos that was exactly what i wanted

thanx again

 

[MHS]

Aaah right. Well, I'm glad we got that one cleared up.

:icon_lol: