Subquery help?

[Jaazaniah]

I have a table setup tbl_Hours that tracks employees, times started
and finished, and the project number details of the work. The table,
when initially designed, went the quick and dirty way: Example of the
field list:

Emp
Activity#
....
Date
Start Time
End Time

I've been trying to reduce it down to a single date/time field to save
space and front-end programming time, but when I try to spit out the
results that the reporting queries are looking for (like number of
hours spent based on current time field and next greatest time field),
the subquery fails. Here's what I'm trying to use in the query
qry_TimeSpent:

(SELECT Min(t1.Date) AS ENDDATE FROM tbl_Hours AS t1 INNER JOIN
tbl_Hours AS t2 on t1.HistoryID = t2.HistoryID WHERE t1.Date >
t2.Date) AS To

I've tried a few variations, but am obviously doing something wrong as
this subquery returns null values.
Goal: Return next largest Date value for the current Emp
Here's the SQL, the Left joins are there to feed into the reporting
structure. it can change, but would prefer not to.

SELECT tbl_Hours.Emp, tbl_Hours.Date,
(SELECT Min(t1.Date) AS ENDDATE
FROM tbl_Hours AS t1 INNER JOIN tbl_Hours AS t2
ON t1.HistoryID = t2.HistoryID WHERE t1.Date > t2.Date)
AS [To], tbl_Hours.Activity
FROM (((tbl_Hours LEFT JOIN tbl_Activities ON tbl_Hours.Activity =
tbl_Activities.ActivityID) LEFT JOIN tbl_Projects ON
tbl_Activities.Project = tbl_Projects.ProjectID) LEFT JOIN tbl_Clients
ON tbl_Projects.Client = tbl_Clients.ClientID) LEFT JOIN tbl_Employees
ON tbl_Hours.Emp = tbl_Employees.EmpID;

 

[Guest]

Sometimes, in our desire to normalize our database or make it more "elegant"
we can go overboard, and actually slow things down. There is nothing wrong
with storing the start and end times of an employees shift. Actually, I
strongly encourage it (as long as you store these as DateTime values where
you include the date and the time in the field). The amount of time it takes
to compute Time Spent (End_Time - Start_Time) is miniscule, so don't worry
about it. Also, the amount of space needed to store these two fields is also
miniscule in relation to the total space required for most databases of this
type.

BTW, Date is an Access reserved word, so I would not use it as a field name
if you can help it. Use something more descriptive like WorkDate or
something like that.

HTH
Dale

 

[Gary Walter]

Hi Jaazaniah,

I may not fully understand your dilemna,
but that won't keep me from trying to help...

You start with a query

SELECT
tbl_Hours.Emp,
tbl_Hours.Date,
tbl_Hours.Activity
FROM
(((tbl_Hours LEFT JOIN tbl_Activities ON tbl_Hours.Activity =
tbl_Activities.ActivityID) LEFT JOIN tbl_Projects ON
tbl_Activities.Project = tbl_Projects.ProjectID) LEFT JOIN tbl_Clients
ON tbl_Projects.Client = tbl_Clients.ClientID) LEFT JOIN tbl_Employees
ON tbl_Hours.Emp = tbl_Employees.EmpID;

and for every Emp/Activity record in the result set,
you would like a correlated subquery to return the
next largest "Date" for that Emp/Activity based on the
"Date" for that record?

{you probably want to change the field name "Date"
to some other name that is not an Access reserved word}

Where I *think* you are missing out is that you
want to correlate back to "tbl_Hours" in your main
query above, not to some further instance of the table.

SELECT
tbl_Hours.Emp,
tbl_Hours.[Date],
tbl_Hours.Activity,
(SELECT
Min(t1.[Date])
FROM
tbl_Hours As t1
WHERE
t1.Emp = tbl_Hours.Emp
AND
t1.Activity = tbl_Hours.Activity
AND
t1.[Date] > tbl_Hours.[Date]) As [To]
FROM
(((tbl_Hours LEFT JOIN tbl_Activities ON tbl_Hours.Activity =
tbl_Activities.ActivityID) LEFT JOIN tbl_Projects ON
tbl_Activities.Project = tbl_Projects.ProjectID) LEFT JOIN tbl_Clients
ON tbl_Projects.Client = tbl_Clients.ClientID) LEFT JOIN tbl_Employees
ON tbl_Hours.Emp = tbl_Employees.EmpID;

{I did not look it up, but I think "To"
is also a reserved word...}

good luck,

gary

I have a table setup tbl_Hours that tracks employees, times started
and finished, and the project number details of the work. The table,
when initially designed, went the quick and dirty way: Example of the
field list:

Emp
Activity#
...
Date
Start Time
End Time

I've been trying to reduce it down to a single date/time field to save
space and front-end programming time, but when I try to spit out the
results that the reporting queries are looking for (like number of
hours spent based on current time field and next greatest time field),
the subquery fails. Here's what I'm trying to use in the query
qry_TimeSpent:

(SELECT Min(t1.Date) AS ENDDATE FROM tbl_Hours AS t1 INNER JOIN
tbl_Hours AS t2 on t1.HistoryID = t2.HistoryID WHERE t1.Date >
t2.Date) AS To

I've tried a few variations, but am obviously doing something wrong as
this subquery returns null values.
Goal: Return next largest Date value for the current Emp
Here's the SQL, the Left joins are there to feed into the reporting
structure. it can change, but would prefer not to.

SELECT tbl_Hours.Emp, tbl_Hours.Date,
(SELECT Min(t1.Date) AS ENDDATE
FROM tbl_Hours AS t1 INNER JOIN tbl_Hours AS t2
ON t1.HistoryID = t2.HistoryID WHERE t1.Date > t2.Date)
AS [To], tbl_Hours.Activity
FROM (((tbl_Hours LEFT JOIN tbl_Activities ON tbl_Hours.Activity =
tbl_Activities.ActivityID) LEFT JOIN tbl_Projects ON
tbl_Activities.Project = tbl_Projects.ProjectID) LEFT JOIN tbl_Clients
ON tbl_Projects.Client = tbl_Clients.ClientID) LEFT JOIN tbl_Employees
ON tbl_Hours.Emp = tbl_Employees.EmpID;

 

[Jamie Collins]

I have a table setup tbl_Hours that tracks employees, times started
and finished, and the project number details of the work. The table,
when initially designed, went the quick and dirty way: Example of the
field list:

Emp
Activity#
...
Date
Start Time
End Time

I've been trying to reduce it down to a single date/time field to save
space and front-end programming time, but when I try to spit out the
results that the reporting queries are looking for (like number of
hours spent based on current time field and next greatest time field),
the subquery fails. Here's what I'm trying to use in the query
qry_TimeSpent:

(SELECT Min(t1.Date) AS ENDDATE FROM tbl_Hours AS t1 INNER JOIN
tbl_Hours AS t2 on t1.HistoryID = t2.HistoryID WHERE t1.Date >
t2.Date) AS To

You original design flaw was to split date and time; I urge you not
commit a second flaw by dropping your end date(time) column!

At the moment you are storing a period like this:

(#2007-07-24 00:00:00#, #1899-12-30 09:00:00#, #1899-12-30 17:00:00#]

I trust you can spot the flaw when it is written out like this. Not
unsurprisingly, the fix is to model the same period like this:

(#2007-07-24 09:00:00#, #2007-12-24 17:00:00#]

Perhaps you also want a validation rule to ensure that the date part
is the same for both start and end date.

As you are finding, if you split the sub atmomic start- and end date
pair across rows you need a subquery to reunite them. There was a
recent discussion on this topic, which details the important point
about the need for a sequenced primary key in such tables (called
'valid-time state' tables in the SQL literature):

http://tinyurl.com/3dp7td

Jamie.

--