Skip to main content
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

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.


RMFIAuthor
Participant
October 11, 2006
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.