SQL Query...HELP REQUIRED!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.... ; )
 
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
 
Back
Top