Count Unique items between two dates

[telford99]

I have the follwoing quere

SELECT COUNT(*) AS Workstation_Count
FROM [SELECT DISTINCT Workstation_ID FROM Machine_Connect]. AS [%$##@_Alias];

I want to count the number of unique items in the DB between two dates. There is also a date field in the db.

I'm using SQL - MS Access.

Thank you in advance

 

[PHV]

SELECT COUNT(*) AS Workstation_Count
FROM (SELECT DISTINCT Workstation_ID FROM Machine_Connect WHERE theDate BETWEEN #2006-07-01# And #2006-07-31#) AS D

And the ANSI SQL way:
SELECT COUNT(DISTINCT Workstation) Workstation_Count
FROM Machine_Connect
WHERE theDate BETWEEN DATE '2006-07-01' And DATE '2006-07-31'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hurricane766]

Hi,

SELECT COUNT(*) AS Workstation_Count
FROM
(SELECT DISTINCT Workstation_ID FROM Machine_Connect WHERE date_column > min_date_variable AND date_column < max_date_variable) AS distinct_ids;

Hope that helps