summary query

K

Kamil

Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?

Best regards,
Kamil
 
J

John Spencer

SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
K

Kamil

Thanks.

How to show the balance of activated users for each month?
I want to know how many users were activated at end of month.
Is it possible?


SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?
Click to expand...
Best regards,
Kamil
Click to expand...
Click to expand...
 
J

John Spencer

I don't understand the question.

Which of the following do you want:
== the number of users that have been activated during the month
== the number of users that have been activated prior to the end of the month
The users have been activated at some time in the past
== the number of users that are active as of the end of the month. The users
have been activated prior to the end of the month and their status has not
changed to something else like "inactive" before the end of the month
== some other definition of what you are looking for.

This query gives the count per month where a record with the status of
Activated exists and the Flag is equal to an asterisk (*).

SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

Also can you post the actual names of your fields and the field types.
User ID >>>> ??????
User State >>> STATE
Date/Time of entry >>> ?????
Last State Flag >>>>FLAG

What values does Last State Flag (FLAG) have and what do they signify?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks.

How to show the balance of activated users for each month?
I want to know how many users were activated at end of month.
Is it possible?


SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?
Best regards,
Kamil
Click to expand...
Click to expand...
Click to expand...
 
K

Kamil

The number of active users at the end of each month.
example:
201001->1000
201002->2500
201003->4000

more description:
I want to know how many active users my service had in time.
So I need information for the end of each month.

fields:
User ID -> ID_USER -> long
User State >>> STATE -> int (1..10)
Date/Time of entry >>> LOG_DATETIME -> date/time
Last State Flag >>>>FLAG -> string(3) (I only use * or null, where * means that state is actual. only 1 state for each user can be flagged)
Click to expand...

thanks

I don't understand the question.

Which of the following do you want:
== the number of users that have been activated during the month
== the number of users that have been activated prior to the end of the month
The users have been activated at some time in the past
== the number of users that are active as of the end of the month. The users
have been activated prior to the end of the month and their status has not
changed to something else like "inactive" before the end of the month
== some other definition of what you are looking for.

This query gives the count per month where a record with the status of
Activated exists and the Flag is equal to an asterisk (*).

SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

Also can you post the actual names of your fields and the field types.
User ID >>>> ??????
User State >>> STATE
Date/Time of entry  >>> ?????
Last State Flag >>>>FLAG

What values does Last State Flag (FLAG) have and what do they signify?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks.
Click to expand...
How to show the balance of activated users for each month?
I want to know how many users were activated at end of month.
Is it possible?
Click to expand...
SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")
If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Kamil wrote:
Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?
Best regards,
Kamil
Click to expand...
Click to expand...
Click to expand...
 
K

Kamil

The number of active users at the end of each month.
example:
201001->1000
201002->2500
201003->4000

more description:
I want to know how many active users my service had in time.
So I need information for the end of each month.

fields:
User ID -> ID_USER -> long
User State >>> STATE -> int (1..10)
Date/Time of entry >>> LOG_DATETIME -> date/time
Last State Flag >>>>FLAG -> string(3) (I only use * or null, where * means that state is actual. only 1 state for each user can be flagged)
Click to expand...

thanks

I don't understand the question.

Which of the following do you want:
== the number of users that have been activated during the month
== the number of users that have been activated prior to the end of the month
The users have been activated at some time in the past
== the number of users that are active as of the end of the month. The users
have been activated prior to the end of the month and their status has not
changed to something else like "inactive" before the end of the month
== some other definition of what you are looking for.

This query gives the count per month where a record with the status of
Activated exists and the Flag is equal to an asterisk (*).

SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

Also can you post the actual names of your fields and the field types.
User ID >>>> ??????
User State >>> STATE
Date/Time of entry  >>> ?????
Last State Flag >>>>FLAG

What values does Last State Flag (FLAG) have and what do they signify?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks.
Click to expand...
How to show the balance of activated users for each month?
I want to know how many users were activated at end of month.
Is it possible?
Click to expand...
SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")
If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Kamil wrote:
Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?
Best regards,
Kamil
Click to expand...
Click to expand...
Click to expand...
 
K

Kamil

The number of active users at the end of each month.
example:
201001->1000
201002->2500
201003->4000

more description:
I want to know how many active users my service had in time.
So I need information for the end of each month.

fields:
User ID -> ID_USER -> long
User State >>> STATE -> int (1..10)
Date/Time of entry >>> LOG_DATETIME -> date/time
Last State Flag >>>>FLAG -> string(3) (I only use * or null, where * means that state is actual. only 1 state for each user can be flagged)
Click to expand...

thanks

I don't understand the question.

Which of the following do you want:
== the number of users that have been activated during the month
== the number of users that have been activated prior to the end of the month
The users have been activated at some time in the past
== the number of users that are active as of the end of the month. The users
have been activated prior to the end of the month and their status has not
changed to something else like "inactive" before the end of the month
== some other definition of what you are looking for.

This query gives the count per month where a record with the status of
Activated exists and the Flag is equal to an asterisk (*).

SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")

Also can you post the actual names of your fields and the field types.
User ID >>>> ??????
User State >>> STATE
Date/Time of entry  >>> ?????
Last State Flag >>>>FLAG

What values does Last State Flag (FLAG) have and what do they signify?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks.
Click to expand...
How to show the balance of activated users for each month?
I want to know how many users were activated at end of month.
Is it possible?
Click to expand...
SELECT Format([DateField],"yyyy-mm") as YearMonth
, Count([DateField]) as Activated
FROM USERS_LOG
WHERE STATE="ACTIVATED" AND FLAG="*"
GROUP BY Format([DateField],"yyyy-mm")
If you are trying to do this as a sub-query then all you may need to do is add
a bit more to the where clause.
(SELECT COUNT(*) AS ACTIVATED
FROM USERS_LOG As Temp
WHERE STATE="ACTIVATED"
AND FLAG="*"
AND Format(Temp.[DateField],"yyyymm")=Format(Users_Log.[DateField],"yyyymm"))
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Kamil wrote:
Hi.
I have a table with users log. It contains user id, user state, date/
time of entry and last state flag. Each time user state is changed
there is new entry in that log.
One of the states is "ACTIVATED".
I need to create a query, which will show the number of activated
users monthly.
It's easy to show current number (SELECT COUNT(*) AS ACTIVATED FROM
USERS_LOG WHERE STATE="ACTIVATED" AND FLAG="*")
but how to show it monthly?
Best regards,
Kamil
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

IF you only have the one table then you need a query against it that will
HOPEFULLY return every month plus year. If the query doesn't do that then you
need a table that is populated with a record for each month plus year.

SELECT Distinct Format(Log_DateTime,"yyyy-mm") as YYYYMM
FROM Users_Log

SELECT YYYYMM,
(SELECT COUNT(*)
FROM Users_Log
WHERE STATE="ACTIVATED" AND FLAG="*"
AND Format(Log_DateTime,"yyyy-mm") < YYYYMM)
FROM (SELECT Distinct Format(Log_DateTime,"yyyy-mm") as YYYYMM
FROM Users_Log ) as Dates

That will be slow with a lot of records. Much more efficient would be to
build a Month End table (tMonthEnd) with a datetime field (MonthEnd) and
records like
2008-01-31
2008-02-29
....
Then you would have a much more efficient query

SELECT MonthEnd, Count(User_ID)
FROM Users_Log INNER JOIN tMonthEnd
ON Users_Log.Log_DateTime <= tMonthEnd.MonthEnd
WHERE STATE="ACTIVATED" AND FLAG="*"

That will still have problems in that it will only count users that are still
active at the present time. To get a count of persons that were active
sometime during the month you probably need to drop the Flag="*" criterion.

To get persons that were active on the last day of the month, the query
becomes more complex. Since you now have to find any record for the person
that exists after the date of activation and before the end of month.

If you need the count as of the end of the month, let me know. I need to
ponder the solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
K

Kamil

Thank you for being so helpful.
You gave me great feedback.

I see that it could be a good idea, to calculate this using VBA, once
a month, and store the result in another table.

Best regards
Kamil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Custom PWA Field in My Timesheet Cannot Be Queried 1
How do you make a summary sheet? 0
Access query 2
Space at end of multi page list to accommodate new entries 1
How to build a "reach around" query? 5
Copy previous record into form field 0
normalization issue & logging in 3

Top