Selecting most recent update

[Guest]

I am trying my best to develop a query that will display data based on the
status of a work order. I have created two tables; one that holds work
orders and another that holds all updates to each work order. They are
related by the field "Work Order #" in a one-to-many relationship. I would
like to pull all work orders that have a status other than "Closed". The
status is selected by a drop down menu in my subform from the secondary
table. Each work order may have several updates. I have tried pulling the
Max date and included criteria where the Status field <>"Closed". However,
this pulls the most recent update for each work order in which the status is
not Closed. I just want to pull the work orders where that remain open as of
the date the query is run. I'm sure this is confusing, any assistance would
be greatly appreciated. As necessary, I can provide any other information.
Thanks!!

 

[Dirk Goldgar]

I am trying my best to develop a query that will display data based
on the status of a work order. I have created two tables; one that
holds work orders and another that holds all updates to each work
order. They are related by the field "Work Order #" in a one-to-many
relationship. I would like to pull all work orders that have a
status other than "Closed". The status is selected by a drop down
menu in my subform from the secondary table. Each work order may
have several updates. I have tried pulling the Max date and included
criteria where the Status field <>"Closed". However, this pulls the
most recent update for each work order in which the status is not
Closed. I just want to pull the work orders where that remain open
as of the date the query is run. I'm sure this is confusing, any
assistance would be greatly appreciated. As necessary, I can provide
any other information. Thanks!!

I think you're saying that that you have a table structure something
like this:

Table: Work Orders
Field: Work Order #
(other fields)

Table: Work Order Updates
Field: Work Order #
Field: Status Date
Field: Status

So for each work order, you have a set of updates to the status, each
update being dated via the field [Status Date]. One of the possible
status values is "Closed". You want to pull from the [Work Orders]
table those records that are not "Closed", according to the [Work Order
Updates] table. Is that right so far?

If so, there's one further question I have about "Closed" status. Once
a work order is closed -- that is, once a [Work Order Updates] record is
created with a status of "Closed" -- can it be reopened? Can additional
updates be entered for it, changing its status from Closed to something
else? Is a work order only considered closed if its latest update has
the status = "Closed"?

(I know, that's several questions. But they're all trying to get at the
same information.)

 

[Guest]

Dirk,

I suppose it would be possible (though unlikely) that an additional update
could be added to a work order to re-open it; thereby the most recent update
would indicate another status. And yes, for all intents and purposes, the
work order would only be considered closed if the latest update has the
status = "Closed". Hope this helps...I appreciate your assistance.

Dave

I am trying my best to develop a query that will display data based
on the status of a work order. I have created two tables; one that
holds work orders and another that holds all updates to each work
order. They are related by the field "Work Order #" in a one-to-many
relationship. I would like to pull all work orders that have a
status other than "Closed". The status is selected by a drop down
menu in my subform from the secondary table. Each work order may
have several updates. I have tried pulling the Max date and included
criteria where the Status field <>"Closed". However, this pulls the
most recent update for each work order in which the status is not
Closed. I just want to pull the work orders where that remain open
as of the date the query is run. I'm sure this is confusing, any
assistance would be greatly appreciated. As necessary, I can provide
any other information. Thanks!!

I think you're saying that that you have a table structure something
like this:

Table: Work Orders
Field: Work Order #
(other fields)

Table: Work Order Updates
Field: Work Order #
Field: Status Date
Field: Status

So for each work order, you have a set of updates to the status, each
update being dated via the field [Status Date]. One of the possible
status values is "Closed". You want to pull from the [Work Orders]
table those records that are not "Closed", according to the [Work Order
Updates] table. Is that right so far?

If so, there's one further question I have about "Closed" status. Once
a work order is closed -- that is, once a [Work Order Updates] record is
created with a status of "Closed" -- can it be reopened? Can additional
updates be entered for it, changing its status from Closed to something
else? Is a work order only considered closed if its latest update has
the status = "Closed"?

(I know, that's several questions. But they're all trying to get at the
same information.)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

I also should mention, not every work order from the primary table will
necessarily have an update. So, somewhere in there, I guess I also need to
include a return of work orders where the updates to the status is null.

I am trying my best to develop a query that will display data based
on the status of a work order. I have created two tables; one that
holds work orders and another that holds all updates to each work
order. They are related by the field "Work Order #" in a one-to-many
relationship. I would like to pull all work orders that have a
status other than "Closed". The status is selected by a drop down
menu in my subform from the secondary table. Each work order may
have several updates. I have tried pulling the Max date and included
criteria where the Status field <>"Closed". However, this pulls the
most recent update for each work order in which the status is not
Closed. I just want to pull the work orders where that remain open
as of the date the query is run. I'm sure this is confusing, any
assistance would be greatly appreciated. As necessary, I can provide
any other information. Thanks!!

I think you're saying that that you have a table structure something
like this:

Table: Work Orders
Field: Work Order #
(other fields)

Table: Work Order Updates
Field: Work Order #
Field: Status Date
Field: Status

So for each work order, you have a set of updates to the status, each
update being dated via the field [Status Date]. One of the possible
status values is "Closed". You want to pull from the [Work Orders]
table those records that are not "Closed", according to the [Work Order
Updates] table. Is that right so far?

If so, there's one further question I have about "Closed" status. Once
a work order is closed -- that is, once a [Work Order Updates] record is
created with a status of "Closed" -- can it be reopened? Can additional
updates be entered for it, changing its status from Closed to something
else? Is a work order only considered closed if its latest update has
the status = "Closed"?

(I know, that's several questions. But they're all trying to get at the
same information.)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

I suppose it would be possible (though unlikely) that an additional
update could be added to a work order to re-open it; thereby the most
recent update would indicate another status. And yes, for all
intents and purposes, the work order would only be considered closed
if the latest update has the status = "Closed".

Okay, so then, given this table structure:

Table: Work Orders
Field: Work Order #
(other fields)

Table: Work Order Updates
Field: Work Order #
Field: Status Date
Field: Status

.... we need to identify sets of records as follows:

1. For each work order that has any updates, what is the latest status
date? This is easy to get, because the latest status date is the
maximum value, and there's a built-in SQL aggregate function to give it
to us. The SQL to get the information is:

SELECT [Work Order #], Max([Status Date]) As LastUpdate
FROM [Work Order Updates]
GROUP BY [Work Order #];

That gives us a set of records consisting of each work order # and its
latest status date. Although we don't really have to do it, let's make
life easier and save the above SELECT statement as a stored query, named
"qryWorkOrdersLastUpdate".

2. Now we need to know, for each of the records returned by
qryWorkOrdersLastUpdate, what was that work order's status on that date.
The following join expression, used in an appropriate SELECT statement,
will give us that:

[Work Order Updates] As U
INNER JOIN
qryWorkOrdersLastUpdate As L
ON
U.[Work Order #] = L.[Work Order #]
AND
U.[Status Date] = L.LastUpdate

Note that the above is not a full-fledged SQL statement, but just a
portion of one. I've aliased the table and query to U and L for
convenience in referring to their fields.

3. It seems reasonable that we may often want to know, for every work
order in the [Work Orders] table, what is its current status. If a work
order doesn't have any updates on file, that status will be Null, but we
still want to return a record to represent the work order. To get that,
we can use an outer join on the records returned by the join expression
above. Let's assume that we're generally going to want all the fields
from the [Work Orders] table, but with the additional Status field,
which we'll rename to CurrentStatus to avoid any confusion. For this,
the following SQL should serve:

SELECT
O.*,
U.[Status] As CurrentStatus
FROM
[Work Orders] As O
LEFT JOIN
(
[Work Order Updates] As U
INNER JOIN
qryWorkOrdersLastUpdate As L
ON
U.[Work Order #] = L.[Work Order #]
AND
U.[Status Date] = L.LastUpdate
)
ON O.[Work Order #] = U.[Work Order #];

We're probably going to want to use this information again, so why not
save the above query under the name, "qryWorkOrdersCurrentStatus".

4. So now, given that last query, all you need to do is filter out the
records that have CurrentStatus = "Closed":

SELECT * FROM qryWorkOrdersCurrentStatus
WHERE CurrentStatus <> "Closed";

And that's it. Of course, I may well have made a mistake in the SQL
somewhere, and the table and field names I used may not be the same as
yours. (Personally, I would never have spaces or non-standard
characters like "#" in my table or field names, as it forces me to use
brackets ([]) all the time.) And I haven't set up tables to test this
with, nor do I warrant that this is the best way to do it. But
something along these lines should get you what you want.

 

[Albert D.Kallal]

Here is how I do this

select Order#, EnterBy, Location, DueDate, STATUSDATE
from tblWorkOrders
left join tblUpDates on tblWorkOrders.Order# = tblStatus.Order#
Where tblWorkOrders.Order# in
(select top1 Order# from tblStatus
where Order# = tblWorkOrders.Order# order by DSC STATUSDATE)

The above query is written as above will do several things

return null for those records that do not have any status date/records

allows you to FREELY add additional fields to both tables, and NOT have
to rewrite the sql query. So, if you desire to include MORE fields then just
the STATUSDATE from the child table, you simply list them in the first
select part. This is MUCH better then using just the "max" to return the
date...as this allows you to use any and all fields from the child table in
the first select command.

If there are two status dates of the same day, then both records will be
returned. If you ONLY want the last record in that case, then order by the
autonumber field in the 2nd table. (and, if you don't have a
autonumber...add one...)...

(select top1 Order# from tbStatus
where Order# = tblWorkOrders.Order# order by DSC STATUSDATE, DSC id)

Last, but not least

(select top1 Order# from tblStatus
where Order# = tblWorkOrders.Order#

You actually could leave out the above where clause, and it still works..but
if you think about this, then a whole table scan will occur, and THEN the
"IN" clause is checked. So, by placing the restricting as above...the query
runs fast, since the join occurs, but is restricted to our small sub-set...

 

[Albert D.Kallal]

And, we forgot the status part....

so,

(select top1 Order# from tblStatus
where Order# = tblWorkOrders.Order#
and
Status = 'Open' order by DSC STATUSDATE)

As you can see, as written, we *easily* add the 2nd condition to this
query..and could easily add more....

It was my mistake to forget the "status" part...but, as you can see...it was
easily fixed by adding the condition, and so for this reason, I use the
above format as it both lets you list as many fields from both tables in the
query with ease. So, you might want both status and statusdate..and even
more fields from he child table to show in this query...and with this
approach..it is easy...