Distinct count query

[oyam]

I am using Access to query FoxPro database. I am trying to have multiple distinct counts. The query works with one distinct count but not with multiples. Here is the query, any ideas?

Osher

SELECT trnsdate, count(distinct(ordernbr)) as ord_count, count(distinct(ticketnbr)) as employee, count(distinct(tranhst.partnbr)) as TotLines, sum(shipqty) as TotQty, sum(shipqty*itemas.weight) as Totweight
FROM tranhst, salhst, itemas
WHERE trnsdate Between DateTime(2002,04,30) And DateTime(2002,04,30) And transcode = 'SHP' and tranhst.document = salhst.invoicenbr and tranhst.partnbr = salhst.partnbr and salhst.partnbr = itemas.partnum
GROUP BY trnsdate

 

[rgbean]

Osher,
Are you doing this query with Access? or are you using SQL Passthrough so ODBC / VFP is doing the query?

In VFP unless 'trnsdate' is exactly '04/30/2002 12:00:00 AM' your Between qualifier won't select any records.

Rick

 

[DSummZZZ]

I'm not totally sure how Access ticks, but with a Fox 2.x or VFP SELECT command, you can only use DISTINCT one time as it will give distinct values from the result of the rest of the query.

Dave S.