Skip to main content
R
RMFI
Participant
October 10, 2006
Question

need another way to summarize data

  • October 10, 2006
  • 1 reply
  • 357 views
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.
This topic has been closed for replies.

1 reply

N
Newsgroup_User
Inspiring
October 11, 2006
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.


N
Newsgroup_User
Inspiring
October 11, 2006
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)


N
Newsgroup_User
Inspiring
October 11, 2006
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?


Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices