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!
