Access 2003 query question
Posted on 2008-10-28
I use a Dreamweaver extension (Webassist DB Search) to create a date range search page using ASPvb.
I have a recordset in my results page taken from multiple queries I built inside Access to Join several filtered tables from the same source table (one version uses a value of Accepted, one with Rejected, etc).
I'm using multiple queries inside Access because I need to COUNT, SUM, and AVG various values based on Accepted, Rejected, etc. The current query breaks this down by a UserID assigned to every record in the source table.
The results are like so:
Name | Accepted Count | Total $ Accepted | Rejected Count | Total $ Rejected
Bob 5 $500 3 $300
Doug 7 $1200 2 $1100
And so on.
All of this works! I have all the breakdowns behaving as expected with one caveat: my date range search.
All the records in the source table for these queries contain a date. But if I add date field to any of the queries or the master query that joins them altogether, I get all the SUMS and AVGs but with a row for every actual record, because date becomes part of the query and becomes the default Sort By
Is there a way for my date range search to be tied to these queries in a way that my looped array of records does not need to display all records or dates, but displayed results can still be filtered by date?