count records based on date range

  • Thread starter Thread starter SFatz
  • Start date Start date
S

SFatz

I have a task logging database that is used by the department to track hours
spent on various assigned tasks. The database has the following fields:

UserID, LogDate, HoursLogged, and some others for detail specific data not
pertinent here.

I need a query to count how many users logged hours during each week. I can
get a list of the users by week with the following query.

SELECT DISTINCT [LogDate]-Weekday([LogDate])+7 AS WeekEndDate, UserID
FROM TaskLog
ORDER BY [LogDate]-Weekday([LogDate])+7;

( [LogDate]-Weekday([LogDate])+7 calculates the week ending date. )

This query gives me the following results.
WeekEndDate UserID
09/18/04 Alan
09/18/04 Betty
09/18/04 Colin
09/25/04 Alan
10/02/04 Colin
10/02/04 Drew

I can't seem to get a query that will give me the number of users per week
that entered something in the log. The query should provide results like
the following.

WeekEndDate UserCount
09/18/04 3
09/25/04 1
10/02/04 2

My many thanks go out in advance for your help.
 
The easiest way would be to use your existing query as the source of another query.

SELECT WeekEndDate, Count(UserID) as UserCount
FROM YourExistingQuery
GROUP BY WeekEndDate
 
Thanks for the tip. That method works.
However, I liket to avoid intermediate queries if possible. Is there a way
to do this in a single query?
Or conversely, can you set my head straight on why using multiple queries is
better than using single, more complex ones.
(Other than the obvious that the complex query is more difficult to debug)
Thanks again,

John Spencer (MVP) said:
The easiest way would be to use your existing query as the source of
another query.

SELECT WeekEndDate, Count(UserID) as UserCount
FROM YourExistingQuery
GROUP BY WeekEndDate


I have a task logging database that is used by the department to track
hours
spent on various assigned tasks. The database has the following fields:

UserID, LogDate, HoursLogged, and some others for detail specific data
not
pertinent here.

I need a query to count how many users logged hours during each week. I
can
get a list of the users by week with the following query.

SELECT DISTINCT [LogDate]-Weekday([LogDate])+7 AS WeekEndDate, UserID
FROM TaskLog
ORDER BY [LogDate]-Weekday([LogDate])+7;

( [LogDate]-Weekday([LogDate])+7 calculates the week ending date. )

This query gives me the following results.
WeekEndDate UserID
09/18/04 Alan
09/18/04 Betty
09/18/04 Colin
09/25/04 Alan
10/02/04 Colin
10/02/04 Drew

I can't seem to get a query that will give me the number of users per
week
that entered something in the log. The query should provide results like
the following.

WeekEndDate UserCount
09/18/04 3
09/25/04 1
10/02/04 2

My many thanks go out in advance for your help.
Click to expand...
Click to expand...
 
You can do it all using a subquery.

SELECT S.WeekEndDate, Count(S.Userid) as UserCount
FROM (
SELECT DISTINCT LogDate-Weekday(LogDate)+7 AS WeekEndDate,
UserID
FROM TaskLog) AS S
GROUP BY S.WeekEndDate

Access will probably restructure this to the following. Note the square
brackets around the entire subquery, the period at the end, and the "Alias" for
the Sub-query name. This syntax will work as long as there are NO spaces in the
field or table names (you can't use brackets within the brackets).

SELECT S.WeekEndDate, Count(S.Userid) as UserCount
FROM [SELECT DISTINCT LogDate-Weekday(LogDate)+7 AS WeekEndDate,
UserID
FROM TaskLog]. AS S
GROUP BY S.WeekEndDate
Thanks for the tip. That method works.
However, I liket to avoid intermediate queries if possible. Is there a way
to do this in a single query?
Or conversely, can you set my head straight on why using multiple queries is
better than using single, more complex ones.
(Other than the obvious that the complex query is more difficult to debug)
Thanks again,

John Spencer (MVP) said:
The easiest way would be to use your existing query as the source of
another query.

SELECT WeekEndDate, Count(UserID) as UserCount
FROM YourExistingQuery
GROUP BY WeekEndDate


I have a task logging database that is used by the department to track
hours
spent on various assigned tasks. The database has the following fields:

UserID, LogDate, HoursLogged, and some others for detail specific data
not
pertinent here.

I need a query to count how many users logged hours during each week. I
can
get a list of the users by week with the following query.

SELECT DISTINCT [LogDate]-Weekday([LogDate])+7 AS WeekEndDate, UserID
FROM TaskLog
ORDER BY [LogDate]-Weekday([LogDate])+7;

( [LogDate]-Weekday([LogDate])+7 calculates the week ending date. )

This query gives me the following results.
WeekEndDate UserID
09/18/04 Alan
09/18/04 Betty
09/18/04 Colin
09/25/04 Alan
10/02/04 Colin
10/02/04 Drew

I can't seem to get a query that will give me the number of users per
week
that entered something in the log. The query should provide results like
the following.

WeekEndDate UserCount
09/18/04 3
09/25/04 1
10/02/04 2

My many thanks go out in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Thanks,
I shun spaces in the names for that very reason.
This works like a charm.

Thanks again to you, and all the other MVPs, for support of these forums.

John Spencer (MVP) said:
You can do it all using a subquery.

SELECT S.WeekEndDate, Count(S.Userid) as UserCount
FROM (
SELECT DISTINCT LogDate-Weekday(LogDate)+7 AS WeekEndDate,
UserID
FROM TaskLog) AS S
GROUP BY S.WeekEndDate

Access will probably restructure this to the following. Note the square
brackets around the entire subquery, the period at the end, and the
"Alias" for
the Sub-query name. This syntax will work as long as there are NO spaces
in the
field or table names (you can't use brackets within the brackets).

SELECT S.WeekEndDate, Count(S.Userid) as UserCount
FROM [SELECT DISTINCT LogDate-Weekday(LogDate)+7 AS WeekEndDate,
UserID
FROM TaskLog]. AS S
GROUP BY S.WeekEndDate
Thanks for the tip. That method works.
However, I liket to avoid intermediate queries if possible. Is there a
way
to do this in a single query?
Or conversely, can you set my head straight on why using multiple queries
is
better than using single, more complex ones.
(Other than the obvious that the complex query is more difficult to
debug)
Thanks again,

John Spencer (MVP) said:
The easiest way would be to use your existing query as the source of
another query.

SELECT WeekEndDate, Count(UserID) as UserCount
FROM YourExistingQuery
GROUP BY WeekEndDate



SFatz wrote:

I have a task logging database that is used by the department to track
hours
spent on various assigned tasks. The database has the following
fields:

UserID, LogDate, HoursLogged, and some others for detail specific data
not
pertinent here.

I need a query to count how many users logged hours during each week.
I
can
get a list of the users by week with the following query.

SELECT DISTINCT [LogDate]-Weekday([LogDate])+7 AS WeekEndDate, UserID
FROM TaskLog
ORDER BY [LogDate]-Weekday([LogDate])+7;

( [LogDate]-Weekday([LogDate])+7 calculates the week ending date. )

This query gives me the following results.
WeekEndDate UserID
09/18/04 Alan
09/18/04 Betty
09/18/04 Colin
09/25/04 Alan
10/02/04 Colin
10/02/04 Drew

I can't seem to get a query that will give me the number of users per
week
that entered something in the log. The query should provide results
like
the following.

WeekEndDate UserCount
09/18/04 3
09/25/04 1
10/02/04 2

My many thanks go out in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top