Nested Select of Min and Max

[Ker]

I have a query that works great. It gives me the min for multiple
fields.
Within this query, I also need to get the max of some fields too.

I currently have output of
Date Name Min Start
9/1/03 Walker Rhines 7:00AM

I also need Max such as Max End 3:00PM

My question is how to attach the max selection to this syntax wise.

Thanks, Ker
SELECT SubJobTable.Date AS Expr1, SubJobTable.EmployeeName AS Expr2,
Min(a.testforum) AS [min]
FROM (SELECT Min([LoadUpStart]) AS testforum
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelToStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelFromStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([JobStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([AdminStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([NurseryStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([OtherStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([ShopStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([DesignStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([PlantMatPUDelStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]) AS a;

[Roberto Spier]

SELECT SubJobTable.Date AS Expr1, SubJobTable.EmployeeName AS Expr2,
Min(a.testforum) AS [min], Max(a.MAXtestforum) AS [max]
FROM (SELECT Min([LoadUpStart]) AS testforum, Max([LoadUpStart]) AS
MAXtestforum
....
union all
SELECT Min([TravelToStart]), Max([TravelToStart])
....
union all
SELECT Min([TravelFromStart]), Min([TravelFromStart])
....

"Ker" <ed********@charter.net> escreveu na mensagem
news:23**************************@posting.google.c om...

I have a query that works great. It gives me the min for multiple
fields.
Within this query, I also need to get the max of some fields too.

I currently have output of
Date Name Min Start
9/1/03 Walker Rhines 7:00AM

I also need Max such as Max End 3:00PM

My question is how to attach the max selection to this syntax wise.

Thanks, Ker
SELECT SubJobTable.Date AS Expr1, SubJobTable.EmployeeName AS Expr2,
Min(a.testforum) AS [min]
FROM (SELECT Min([LoadUpStart]) AS testforum
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelToStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelFromStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([JobStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([AdminStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([NurseryStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([OtherStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([ShopStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([DesignStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([PlantMatPUDelStart])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]) AS a;

[Ker Bear]

I tried this like you said. It's close but still doesn't produce the
correct output.

The output is
9/1/03 walker white 7am 7pm
9/2/03 Angie mace 7am 7pm (should be 6am 3pm)
9/2/03 Kenneth mere 7am 7pm (should be 8am 4pm)
It's listing the right dates and employees but listed the same min and
max everytime
When I run the query, it asks me for Expr1 and Expr 2. I only want it
to ask me date range and automatically list all my employees from
SubJobTable with a record with their name in it where date is within the
range. Thanks for all your help!

Here's the query revised with your modifications.

SELECT SubJobTable.Date AS Expr1, SubJobTable.EmployeeName AS Expr2,
Min(a.testforum) AS [min], Max(a.maxtestforum) AS [max]
FROM [SELECT Min([LoadUpStart]) AS testforum, Max([LoadUpEnd]) AS
maxtestforum
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelToStart]), Max([TravelToEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([TravelFromStart]), Max([TravelFromEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([JobStart]), Max([JobEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([AdminStart]) , Max([AdminEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([NurserySt! art]), Max([NurseryEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([OtherStart]), Max([OtherEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([ShopStart]), Max([ShopEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([DesignStart]), Max([DesignEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]
union all
SELECT Min([PlantMatPUDelStart]), Max([PlantMatPUDelEnd])
FROM SubJobTable
WHERE Expr1 = [SubJobTable].[Date] AND Expr2 =
[SubJobTable].[EmployeeName]]. AS a, SubJobTable
GROUP BY SubJobTable.Date, SubJobTable.EmployeeName;

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ker Bear]

I haven't received a response to my thread, can someone please look at
this.

Thanks,
Ker

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!