Solved

Access 2003 query question

Posted on 2008-10-28
10
1,302 Views
Last Modified: 2010-10-05
Hi,

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?

Thanks

Bill

0
Comment
Question by:billium99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22824881
It sounds like you don't want to add the date to the SELECT portion of the query, but only to the WHERE portion.  For example:

SELECT COUNT(field1) FROM table1 WHERE date1 < #1/1/2008#
0
 
LVL 1

Author Comment

by:billium99
ID: 22825162
Hi - I think the issue there is that all of my selects are occuring inside of Access except the final one. The final one, on my ASP page, can't use a WHERE clause for Date (which is where I would need it to it can vary based on the date range search), because I can't get a date field in any of my Access queries without getting multiple records instead of the single SUM, AVG, COUNT results. or could I somehow have Access pull the date variables from the ASP page and apply them to all of the original queries I'm Joining together?
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22825253
The SQL language allows for nested queries, as you seem to be aware.  If you paste the SQL code of the queries you have so far, I can combine them into one query for you.  If you could run a combined query all at once from ASP would that make it easier to specify the date condition?
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 1

Author Comment

by:billium99
ID: 22825536
Certainly it would! Thanks.

Here are the separate queries:

Query "TOTALSUMS":

SELECT Sum(Jobs.EstimatedPrice) AS TotalEstimateDollars, Avg(Jobs.EstimatedPrice) AS AvgOfTotalEstimatedPrice, Sum(Jobs.AcceptedPrice) AS SumOfTotalAcceptedPrice, Avg(Jobs.AcceptedPrice) AS AvgOfTotalAcceptedPrice, Count(Jobs.EstimateStatus) AS TotalEstimatesHandled, Users.UserFirstName, Jobs.EstimatorAssigned
FROM Jobs LEFT JOIN Users ON Jobs.EstimatorAssigned = Users.UserID
WHERE (((Jobs.EstimatedPrice)<>0))
GROUP BY Users.UserFirstName, Jobs.EstimatorAssigned;

Query "Accepted":

SELECT Count(Jobs.JobID) AS CountofAccepted, Count(Jobs.EstimatedPrice) AS CountOfAcceptedEstimatedPrice, Avg(Jobs.EstimatedPrice) AS AvgOfAcceptedEstimatedPrice, Sum(Jobs.AcceptedPrice) AS SumOfAcceptedAcceptedPrice, Avg(Jobs.AcceptedPrice) AS AvgOfAcceptedAcceptedPrice, Jobs.EstimatorAssigned
FROM Jobs
WHERE (((Jobs.AcceptedPrice)<>0))
GROUP BY Jobs.EstimatorAssigned;

Query "Created"

SELECT Count(Jobs.JobID) AS CountOfCreated, Jobs.EstimatorAssigned
FROM Jobs
WHERE (((Jobs.EstimateStatus)="Created"))
GROUP BY Jobs.EstimatorAssigned;

Query "Estimated"

SELECT Count(Jobs.JobID) AS CountOfCurrentEstimated, Sum(Jobs.EstimatedPrice) AS SumOfCurrentEstimatedPrice, Avg(Jobs.EstimatedPrice) AS AvgOfCurrentEstimatedPrice, Jobs.EstimatorAssigned
FROM Jobs
WHERE (((Jobs.EstimateStatus)="Estimated"))
GROUP BY Jobs.EstimatorAssigned;

Query "Rejected":

SELECT Count(Jobs.JobID) AS CountOfRejected, Jobs.EstimatorAssigned, Sum(Jobs.EstimatedPrice) AS SumOfRejectedEstimatedPrice, Avg(Jobs.EstimatedPrice) AS AvgOfRejectedEstimatedPrice
FROM Jobs
WHERE (((Jobs.EstimateStatus)="Rejected"))
GROUP BY Jobs.EstimatorAssigned;

And then the query that puts them all together "FinalQuery"

SELECT TOTALSUMS.UserFirstName, TOTALSUMS.TotalEstimatesHandled, TOTALSUMS.TotalEstimateDollars, TOTALSUMS.AvgOfTotalEstimatedPrice, Accepted.CountofAccepted, TOTALSUMS.SumOfTotalAcceptedPrice, TOTALSUMS.AvgOfTotalAcceptedPrice, Estimated.CountOfCurrentEstimated, Estimated.SumOfCurrentEstimatedPrice, Estimated.AvgOfCurrentEstimatedPrice, Accepted.CountofAccepted, Accepted.CountOfAcceptedEstimatedPrice, Accepted.AvgOfAcceptedEstimatedPrice, Accepted.SumOfAcceptedAcceptedPrice, Accepted.AvgOfAcceptedAcceptedPrice, Rejected.CountOfRejected, Rejected.SumOfRejectedEstimatedPrice, Rejected.AvgOfRejectedEstimatedPrice
FROM ((TOTALSUMS LEFT JOIN Estimated ON TOTALSUMS.EstimatorAssigned = Estimated.EstimatorAssigned) LEFT JOIN Accepted ON TOTALSUMS.EstimatorAssigned = Accepted.EstimatorAssigned) LEFT JOIN Rejected ON TOTALSUMS.EstimatorAssigned = Rejected.EstimatorAssigned;

Thanks for your time!

Bill

0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22825770
This is just a sample, as I imagine you can format the rest of the code to your needs ;)

I've taken the first two queries and combined them into one big query using the same joins and nesting.  If you continue this format with the other three queries it should get you to the final result.  The WHERE conditions will need to be parsed 5 times, but it seems like you have a grip on that part. ;)
SELECT
 TOTALSUMS1.UserFirstName,
 TOTALSUMS1.TotalEstimatesHandled,
 TOTALSUMS1.TotalEstimateDollars,
 TOTALSUMS1.AvgOfTotalEstimatedPrice,
 TOTALSUMS1.SumOfTotalAcceptedPrice,
 TOTALSUMS1.AvgOfTotalAcceptedPrice,
 
 Accepted2.CountofAccepted,
 Accepted2.CountofAccepted,
 Accepted2.CountOfAcceptedEstimatedPrice,
 Accepted2.AvgOfAcceptedEstimatedPrice,
 Accepted2.SumOfAcceptedAcceptedPrice,
 Accepted2.AvgOfAcceptedAcceptedPrice,
 
FROM
(
 SELECT
  Users.UserFirstName,
  Jobs.EstimatorAssigned,
  Sum(Jobs.EstimatedPrice) AS TotalEstimateDollars,
  Avg(Jobs.EstimatedPrice) AS AvgOfTotalEstimatedPrice,
  Sum(Jobs.AcceptedPrice) AS SumOfTotalAcceptedPrice,
  Avg(Jobs.AcceptedPrice) AS AvgOfTotalAcceptedPrice,
  Count(Jobs.EstimateStatus) AS TotalEstimatesHandled
 FROM Jobs LEFT JOIN Users ON Jobs.EstimatorAssigned = Users.UserID
 WHERE Jobs.EstimatedPrice <> 0 AND Jobs.Date < #1/1/2008#
 GROUP BY Users.UserFirstName, Jobs.EstimatorAssigned
) AS TOTALSUMS1
 
LEFT JOIN
(
 SELECT
  Count(Jobs.JobID) AS CountofAccepted,
  Count(Jobs.EstimatedPrice) AS CountOfAcceptedEstimatedPrice,
  Avg(Jobs.EstimatedPrice) AS AvgOfAcceptedEstimatedPrice,
  Sum(Jobs.AcceptedPrice) AS SumOfAcceptedAcceptedPrice,
  Avg(Jobs.AcceptedPrice) AS AvgOfAcceptedAcceptedPrice,
  Jobs.EstimatorAssigned
 FROM Jobs
 WHERE Jobs.AcceptedPrice <> 0 AND Jobs.Date < #1/1/2008#
 GROUP BY Jobs.EstimatorAssigned;
) AS Accepted2
ON TOTALSUMS.EstimatorAssigned = Accepted.EstimatorAssigned

Open in new window

0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22825791
The last line there should be corrected to:

ON TOTALSUMS1.EstimatorAssigned = Accepted2.EstimatorAssigned
0
 
LVL 11

Accepted Solution

by:
miqrogroove earned 500 total points
ID: 22825848
Ah I noticed you only had 4 tables in there so here's a more complete copy:
SELECT
 TOTALSUMS1.UserFirstName,
 TOTALSUMS1.TotalEstimatesHandled,
 TOTALSUMS1.TotalEstimateDollars,
 TOTALSUMS1.AvgOfTotalEstimatedPrice,
 TOTALSUMS1.SumOfTotalAcceptedPrice,
 TOTALSUMS1.AvgOfTotalAcceptedPrice,
 
 Accepted2.CountofAccepted,
 Accepted2.CountofAccepted,
 Accepted2.CountOfAcceptedEstimatedPrice,
 Accepted2.AvgOfAcceptedEstimatedPrice,
 Accepted2.SumOfAcceptedAcceptedPrice,
 Accepted2.AvgOfAcceptedAcceptedPrice,
 
 Estimated.CountOfCurrentEstimated,
 Estimated.SumOfCurrentEstimatedPrice,
 Estimated.AvgOfCurrentEstimatedPrice, 
 
 Rejected.CountOfRejected,
 Rejected.SumOfRejectedEstimatedPrice,
 Rejected.AvgOfRejectedEstimatedPrice
 
FROM
(
 SELECT
  Users.UserFirstName,
  Jobs.EstimatorAssigned,
  Sum(Jobs.EstimatedPrice) AS TotalEstimateDollars,
  Avg(Jobs.EstimatedPrice) AS AvgOfTotalEstimatedPrice,
  Sum(Jobs.AcceptedPrice) AS SumOfTotalAcceptedPrice,
  Avg(Jobs.AcceptedPrice) AS AvgOfTotalAcceptedPrice,
  Count(Jobs.EstimateStatus) AS TotalEstimatesHandled
 FROM Jobs LEFT JOIN Users ON Jobs.EstimatorAssigned = Users.UserID
 WHERE Jobs.EstimatedPrice <> 0 AND Jobs.Date < #1/1/2008#
 GROUP BY Users.UserFirstName, Jobs.EstimatorAssigned
) AS TOTALSUMS1
 
LEFT JOIN
(
 SELECT
  Count(Jobs.JobID) AS CountofAccepted,
  Count(Jobs.EstimatedPrice) AS CountOfAcceptedEstimatedPrice,
  Avg(Jobs.EstimatedPrice) AS AvgOfAcceptedEstimatedPrice,
  Sum(Jobs.AcceptedPrice) AS SumOfAcceptedAcceptedPrice,
  Avg(Jobs.AcceptedPrice) AS AvgOfAcceptedAcceptedPrice,
  Jobs.EstimatorAssigned
 FROM Jobs
 WHERE Jobs.AcceptedPrice <> 0 AND Jobs.Date < #1/1/2008#
 GROUP BY Jobs.EstimatorAssigned
) AS Accepted2
ON TOTALSUMS1.EstimatorAssigned = Accepted2.EstimatorAssigned
 
LEFT JOIN
(
 SELECT
  Count(Jobs.JobID) AS CountOfCurrentEstimated,
  Sum(Jobs.EstimatedPrice) AS SumOfCurrentEstimatedPrice,
  Avg(Jobs.EstimatedPrice) AS AvgOfCurrentEstimatedPrice,
  Jobs.EstimatorAssigned
 FROM Jobs
 WHERE Jobs.EstimateStatus = "Estimated" AND Jobs.Date < #1/1/2008#
 GROUP BY Jobs.EstimatorAssigned
) AS Estimated3
ON TOTALSUMS1.EstimatorAssigned = Estimated3.EstimatorAssigned
 
LEFT JOIN
(
 SELECT
  Count(Jobs.JobID) AS CountOfRejected,
  Jobs.EstimatorAssigned, Sum(Jobs.EstimatedPrice) AS SumOfRejectedEstimatedPrice,
  Avg(Jobs.EstimatedPrice) AS AvgOfRejectedEstimatedPrice
 FROM Jobs
 WHERE Jobs.EstimateStatus = "Rejected" AND Jobs.Date < #1/1/2008#
 GROUP BY Jobs.EstimatorAssigned
) AS Rejected4
ON TOTALSUMS1.EstimatorAssigned = Rejected4.EstimatorAssigned

Open in new window

0
 
LVL 1

Author Comment

by:billium99
ID: 22826222
Thanks - it'll take me a while to get my head around this - I'll keep you posted...

Bill
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22826307
haha okay :)  The only mistake I know I made in that last one was I forgot the 3's and 4's in the query names at the top.  And if you need that 5th table to be in there then that will need to be done.

This query can be run just like the other one you had in ASP.  You will need to insert your date into the query string in 4 or 5 places, but if you can get this to run as-is you're 90% done.
0
 
LVL 1

Author Closing Comment

by:billium99
ID: 31510875
Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question