need another way to summarize data

[RMFI]

I have an asp page that displays various statistics about data in the underlying database (Access 2003). The data manipulation is handled on the database side, so all I have to do is grab the numbers and slap them on the asp page, really.

Now, however, I was asked to allow the user to see the same statistics based on a date range they select on the page. At first I thought this was a simple task, but can't figure out how to do it.

Now I need the ability to FIRST filter out the data based on the date range specified by the user, and THEN run all my database calculations. ...but how do you first grab a subset of data from a table, and then in the same database run more queries on it?

Is there a way to do this? If yes, how? If no... is there another way I've not yet learned or thought of?

The only other thought I have is to write code that will loop through my recordsets and come up with the numbers to do the calculations on the page, but that seems like ... well, like there should be a better way!

Any help greatly appreciated.

[Newsgroup_User]

The thing about this kind of question is that the specifics about your data
are actually rather important to the answer.
In most cases, I think adding a date range would be a trivial exercise. I
can think of cases where it might be a little bit of an adventure, though.

Can you share your calculations and let us know where the date range would
come into play (on which table(s))?


"RMFI" <webforumsuser@macromedia.com> wrote in message
news:egh6dc$8du$1@forums.macromedia.com...
> Now, however, I was asked to allow the user to see the same statistics
> based
> on a date range they select on the page. At first I thought this was a
> simple
> task, but can't figure out how to do it.

[RMFI]

Thanks for your response.

The data is in tblData. Each record represents one answered test question for one user. Table layout is:
RecordID
UserID
QuestionID
Answer (0/1)
DateTimeStamp

I need to determine the following facts for any selected time period:

* how many unique users answered questions
* how many total questions were answered
* how many were "yes" / how many were "no"
* which percent were "yes" / "no"
* which topics had the most "no" answers (and yes/no percent breakdown)

The last one would require we pull in two other tables, tblquestion, which contains the question details, including the topicID, and tblTopics, which would give us the topic name.

My calculations, currently, use crosstab queries to list yes/no numbers and percents, and unique users, grouped by either the questionID or UserID.

Does that help clarify? Hopefully I'm overlooking a very simply answer!

Thanks, again, for your help.

[Newsgroup_User]

This one is really very simple. Whatever query you have now, keep it, just
add tblData.DateTimeStamp BETWEEN @StartDate AND @EndDate to your query.

Any aggregation you do will be done after the result set is filtered, so
you'll be fine. If there's a problem, then please post the actual query so
we can tell why that may be.


"RMFI" <webforumsuser@macromedia.com> wrote in message
news:egj1d0$e9u$1@forums.macromedia.com...
> I need to determine the following facts for any selected time period:
>
> * how many unique users answered questions
> * how many total questions were answered
> * how many were "yes" / how many were "no"
> * which percent were "yes" / "no"
> * which topics had the most "no" answers (and yes/no percent breakdown)

[RMFI]

That's what I thought, at first, too. Here is a scenario showing why that doesn't work:

SQL that works on whole table:
SELECT DISTINCTROW Count(tblData.fldUserID) AS CountOffldUserID, tblData.fldUserID
FROM tblData
GROUP BY tblData.fldUserID;

Adding fldDateTimeStamp to the query causes the grouping to be incorrect. Using the same data, just bringing in the date/time causes the Unique User count to be incorrect.

SELECT DISTINCTROW Count(tblData.fldUserID) AS CountOffldUserID, tblData.fldUserID, DateValue([fldTimeStamp]) AS DateStamp
FROM tblData
GROUP BY tblData.fldUserID, DateValue([fldTimeStamp]);

If I don't show the fldDateTimeStamp in the query results, I can't filter based on date in my recordset. Right?

Am I still missing something?

There are other, trickier complications when it comes to the other values I need to calculate.


Data sample:
fldRecordID fldUserID fldQuestionID fldAnswerArray fldTimeStamp
643 61 33 0 8/22/2006 3:10:53 PM
644 61 34 0 8/22/2006 3:10:53 PM
645 61 35 0 8/22/2006 3:10:53 PM
646 61 36 0 8/22/2006 3:10:53 PM
787 61 38 0 8/23/2006 12:32:43 PM
788 61 37 0 8/23/2006 12:32:43 PM
789 61 39 1 8/23/2006 12:32:43 PM
1479 100 13 0 8/25/2006 9:22:42 AM
1480 100 14 1 8/25/2006 9:22:42 AM
1661 100 16 1 8/29/2006 11:33:56 AM
1662 100 15 0 8/29/2006 11:33:56 AM
1663 100 18 1 8/29/2006 11:33:56 AM
2195 149 7 0 9/10/2006 6:53:39 PM
2196 149 9 0 9/10/2006 6:53:39 PM
2197 149 10 1 9/10/2006 6:53:39 PM
2249 149 11 0 9/13/2006 12:13:04 PM
2250 149 12 0 9/13/2006 12:13:04 PM
2251 149 13 1 9/13/2006 12:13:04 PM

[RMFI]

Hm, ok, I figured out I can just do the entire SQL for that one in my recordset, and I think that'll work. I guess I'll see if I can work out the other one that way too (it's a little more complicated, but I'll see what I can do).

Thanks again. Funny how just spelling it out to someone else will help you see the answer yourself. Sigh!

[Newsgroup_User]

I don't know what you're trying to count here, but a timestamp will have
zero effect on the grouping.

SELECT UserID, COUNT(QuestionID) AS QuestionsAnswered
FROM dbo.Data
WHERE DateTimeStamp BETWEEN @StartDate AND @EndDate
GROUP BY UserID

Now you know how many questions each user answered, and from the record
count, know how many unique users answered questions. Summing
QuestionsAnswered in your application will show you total questions
answered. Adding a single column gets you most of what you want:

SELECT UserID, COUNT(QuestionID) AS QuestionsAnswered, SUM(Answer) AS
YesAnswers
FROM dbo.Data
WHERE DateTimeStamp BETWEEN @StartDate AND @EndDate
GROUP BY UserID

Now, your application knows the number of unique users answering questions
(from the row count), the total number of questions answered (sum of
QuestionsAnswered), the number of "yes" (sum of YesAnswers) and "no" (sum of
QuestionsAnswered - sum of YesAnswers) answers (you also know this now per
user), and the percent of yes and no answers per user and in total by doing
appropriate math with YesAnswers and QuestionsAnswered.

As for topics, there's really no difference to the above query. Join in
your additional tables, change your grouping, and you can get all the same
information.

Easy, no?