Counting days

[Phil McFarlane]

Have the following fields in a table: Date,Name,JobNo,Hours.
I need a SQL query to count the number of days each Name appears over a
period of days.
Any Name can appear more than once on any Date.
If a Name appeared three times on the same Date it should only count as one
day.

Any suggestions or help would be appreciated

Thanks
Phil_mac

 

[Michel Walsh]

SELECT name, COUNT(*)
FROM (SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate) AS a
GROUP BY name


where firstDate and lastDate are parameters defining the range of date of
your interest.


Hoping it may help,
Vanderghast, Access MVP

 

[Phil McFarlane]

Thanks for the quick reply. Unfortunately I left out an important fact. I am
using Access 97.

Cheers

SELECT name, COUNT(*)
FROM (SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate) AS a
GROUP BY name


where firstDate and lastDate are parameters defining the range of date of
your interest.


Hoping it may help,
Vanderghast, Access MVP

Have the following fields in a table: Date,Name,JobNo,Hours.
I need a SQL query to count the number of days each Name appears over a
period of days.
Any Name can appear more than once on any Date.
If a Name appeared three times on the same Date it should only count as
one
day.

Any suggestions or help would be appreciated

Thanks
Phil_mac

 

[Douglas J. Steele]

You should be able to use

SELECT name, COUNT(*)
FROM [SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate]. AS a
GROUP BY name

That's an opening square bracket before, and a closing square bracket
followed by a period after.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the quick reply. Unfortunately I left out an important fact. I
am
using Access 97.

Cheers

SELECT name, COUNT(*)
FROM (SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate) AS a
GROUP BY name


where firstDate and lastDate are parameters defining the range of date of
your interest.


Hoping it may help,
Vanderghast, Access MVP

Have the following fields in a table: Date,Name,JobNo,Hours.
I need a SQL query to count the number of days each Name appears over a
period of days.
Any Name can appear more than once on any Date.
If a Name appeared three times on the same Date it should only count as
one
day.

Any suggestions or help would be appreciated

Thanks
Phil_mac

 

[John W. Vinson]

Thanks for the quick reply. Unfortunately I left out an important fact. I am
using Access 97.

Cheers

SELECT name, COUNT(*)
FROM (SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate) AS a
GROUP BY name

This query should work fine for any version of Access. Are you having a
problem with it?

John W. Vinson [MVP]

 

[Phil McFarlane]

Initially this did not work, giving a bracketing error. However when name
and [date] where changed to tableName.name and tableName.date everything
worked OK.

Thanks to all for the input.

cheers

Thanks for the quick reply. Unfortunately I left out an important fact. I am
using Access 97.

Cheers

SELECT name, COUNT(*)
FROM (SELECT DISTINCT name, [date]
FROM tableName
WHERE [date] BETWEEN firstDate AND lastDate) AS a
GROUP BY name

This query should work fine for any version of Access. Are you having a
problem with it?

John W. Vinson [MVP]