RE: SQL Query...HELP REQUIRED!
- From: Jim C. <JimC@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 07:18:03 -0700
Hi,
While this may not be the ideal solution, I would do it with 2 queries. The
first creates the sum of the hours for each engineer, and then the second is
what you run to find the engineer with the lowest projected hours -
Query1:
Select Engineers, Sum(EstimatedJobHours) as TotalJobHours
FROM OngoingRequests
Group by e.Engineers
And then, Query 2:
SELECT Engineers
FROM Query1
WHERE Query1.TotalJobHours In (Select Min(TotalJobHours) from Query1);
Note that unless you need something other than the email address, based on
what you have provided, you do not need to link to tblSecurity to return the
email address.
Jim
"AQ" wrote:
> Hi,
>
> I am having a problem trying to generate a SQL query that will access my
> Access 2000 Database..... So you'll undrstand i'll just give you the names of
> the tables and the fields of which I am trying to query:
>
> Table Name: tblSecurity
> Field I'm trying to access: useremail
>
> Table Name: OngoingRequests
> Fields I'm trying to access: Engineer, EstimatedJobHours
>
> Note: Both useremail and Engineer hold email associated addresses
>
> I want to generate a SQL query that selects the useremail of the Engineer
> that has the least EstimatedJobHours when all EstimatedJobHours are added up
> for each Engineer.....
>
> In Pseudo Code I picture it like this:
> SELECT u.useremail
> FROM tblSecurity.u, OngoingRequests.e
> WHERE u.useremail = e.Engineers
> AND SUM(EstimatedJobHours) = MIN(of all SUM)
>
>
> The reason I need this query is because I want to send a service request
> (email) to the engineer in the database with the least amount of Estimated
> Job Hours....
>
> I hope this makes sense!! Any suggestions greatly appreciated.... ; )
>
.
- References:
- SQL Query...HELP REQUIRED!
- From: AQ
- SQL Query...HELP REQUIRED!
- Prev by Date: Unmatched qry, 2 fields
- Next by Date: Multiple field query
- Previous by thread: SQL Query...HELP REQUIRED!
- Next by thread: help with Elseif or IIF
- Index(es):
Relevant Pages
|
