Help with a frustrating query

[Todd]

I have the following two tables:

TblStatus
with fields StatID (autonum), VessNum, StatDate, Status,
Location

example data
StatID, VessNum, StatDate, Status, Location
1,V-1, 1/1/03, Empty, Storage
2,V-1, 1/3/03, Full, Rm101
3,V-1, 1/3/03, Cleaned, n/a
3,V-2, 1/12/03, Empty, Storage
4,V-2, 1/15/03, Full, Rm 101

TblLocPickList
with fields LocID, Location, Status

example data
LocID, Location, Status
1, Storage, Empty
2, Off-Site, Empty
11, n/a, Cleaned
12, Rm101, Cleaned
20, Rm101, Full
21, Rm202, Full

The LocID field in TblLocPickList is not an autonumber
field, it is set up so that the Status items are ordered
in the sequential order that they should occur for our
workflow. Note: this also feeds a combo box on a status
entry form.

I'm trying to write a query that will return the latest
status for each vessel. In the case of the example data
above, the query should return:

VessNum, StatDate, Status, Location
V-1, 1/3/03, Full, Rm101
V-2, 1/15/03, Full, Rm101

As you can from the example data, there is sometimes a
status change that occurs in the same day for a a
particular vessel, I only want to return the latest
status.

The problem I'm having is distinguishing the latest status
update for vessels that have multiple status changes on
the same day. I've been able to solve it by choosing the
Max(StatID), however, this forces the data entry to be
made in the actual order that the status events occur. It
would be better if the query could work regardless of the
data entry order (could be that one data entry person
enters the cleaning event, and another enters the filling
event depending on how the paperwork goes.)

I think that this could work by using the table
TblLocPickList to identify the last event that should have
occured (ie Max(LocID)). The process workflow always is
in this order.

I've tried to do this unsuccessfully.

Can anyone help?

 

[Todd]

I just realized I made a mistake in my example data for
TblStatus. StatID is an autonumber field, so there cannot
be duplicate values. Read as 1,2,3,4,5; Instead of
1,2,3,3,4. Sorry.

I still need help with the question though

 

[Tom Ellison]

Dear Todd:

Your post says, in part, "there is sometimes a status change that
occurs in the same day for a a [sic] particular vessel, I want to
return the latest status."

This is in the StatusDate column of TblStatus, isn't it?

Have you made the combination of VessNum / StatusDate unique? If you
allow a Location to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

The query I will give you can return the most recent status or
statusses (I'm making up new words as I go along):

Now, looking at your data, and your proposed solution, this conflict
occurs for VessNum = V-1. The "latest status" is both Full, Rm101 AND
Empty, Storage. Yet somehow, miraculously, you have shown only the
Full, Rm101 in the result.

Now, from what I see, this is pretty much what you already realized.
There isn't enough information in your data do distinguish a sequence
between multiple events that occur on the same date. And you have
correctly identified one of the reasons why the StatusID may not give
you a correct sequencing of such rows. It is always the case that
someone may forget to make such an entry, then come back and make it
out of sequence!

You propose to use what I understand to be a normal sequence of events
as represented in the PickList to determine which happend last. I
wonder greatly about that.

Consider a case when a vessel starts the day as empty, then is
cleaned, and ends up the day full. That would work well.

Now consider another vessel that starts the day full, then becomes
empty, then cleaned. This vessel has the exact same 3 PickList steps
recorded, but not in the same sequence. To arrange that one's
sequence as you suggest would result in incorrect data.

You could put the time of day into StatDate as well as the date. That
would obviously tend to put them in correct sequence. An alternative
would be to add an integer sequencing column and use this to show the
order things happen both on the screen during data entry and then in
reporting. When a user enters an event out of sequence, they would
then have to move it into the correct sequence on the screen, with the
software manipulating this sequence value between the rows for that
vessel until the sequence is show properly. This assumes the person
doing the data entry has a way to know just what that sequence is for
that vessel.

Otherwise, the physical procedure would have to include a record of
the time of day when each step occurs. I do not believe there are any
likely alternatives to these. The person recording a step will need
to have a watch on, and one that is set correctly, so he can record
the time. The database will complain loud and hard if two steps are
submitted for the same vessel at the same date and time, because that
leaves a vessel in an ambiguous state at that time. This presumes you
protect it by creating the unique constraing I suggested on VessNum
and StatDate.

There is no substitute for eliminating ambiguity here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Correction:

In the third paragraph, change to:

Have you made the combination of VessNum / StatusDate unique? If you
allow a VESSEL to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

Dear Todd:

Your post says, in part, "there is sometimes a status change that
occurs in the same day for a a [sic] particular vessel, I want to
return the latest status."

This is in the StatusDate column of TblStatus, isn't it?

Have you made the combination of VessNum / StatusDate unique? If you
allow a Location to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

The query I will give you can return the most recent status or
statusses (I'm making up new words as I go along):

Now, looking at your data, and your proposed solution, this conflict
occurs for VessNum = V-1. The "latest status" is both Full, Rm101 AND
Empty, Storage. Yet somehow, miraculously, you have shown only the
Full, Rm101 in the result.

Now, from what I see, this is pretty much what you already realized.
There isn't enough information in your data do distinguish a sequence
between multiple events that occur on the same date. And you have
correctly identified one of the reasons why the StatusID may not give
you a correct sequencing of such rows. It is always the case that
someone may forget to make such an entry, then come back and make it
out of sequence!

You propose to use what I understand to be a normal sequence of events
as represented in the PickList to determine which happend last. I
wonder greatly about that.

Consider a case when a vessel starts the day as empty, then is
cleaned, and ends up the day full. That would work well.

Now consider another vessel that starts the day full, then becomes
empty, then cleaned. This vessel has the exact same 3 PickList steps
recorded, but not in the same sequence. To arrange that one's
sequence as you suggest would result in incorrect data.

You could put the time of day into StatDate as well as the date. That
would obviously tend to put them in correct sequence. An alternative
would be to add an integer sequencing column and use this to show the
order things happen both on the screen during data entry and then in
reporting. When a user enters an event out of sequence, they would
then have to move it into the correct sequence on the screen, with the
software manipulating this sequence value between the rows for that
vessel until the sequence is show properly. This assumes the person
doing the data entry has a way to know just what that sequence is for
that vessel.

Otherwise, the physical procedure would have to include a record of
the time of day when each step occurs. I do not believe there are any
likely alternatives to these. The person recording a step will need
to have a watch on, and one that is set correctly, so he can record
the time. The database will complain loud and hard if two steps are
submitted for the same vessel at the same date and time, because that
leaves a vessel in an ambiguous state at that time. This presumes you
protect it by creating the unique constraing I suggested on VessNum
and StatDate.

There is no substitute for eliminating ambiguity here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have the following two tables:

TblStatus
with fields StatID (autonum), VessNum, StatDate, Status,
Location

example data
StatID, VessNum, StatDate, Status, Location
1,V-1, 1/1/03, Empty, Storage
2,V-1, 1/3/03, Full, Rm101
3,V-1, 1/3/03, Cleaned, n/a
3,V-2, 1/12/03, Empty, Storage
4,V-2, 1/15/03, Full, Rm 101

TblLocPickList
with fields LocID, Location, Status

example data
LocID, Location, Status
1, Storage, Empty
2, Off-Site, Empty
11, n/a, Cleaned
12, Rm101, Cleaned
20, Rm101, Full
21, Rm202, Full

The LocID field in TblLocPickList is not an autonumber
field, it is set up so that the Status items are ordered
in the sequential order that they should occur for our
workflow. Note: this also feeds a combo box on a status
entry form.

I'm trying to write a query that will return the latest
status for each vessel. In the case of the example data
above, the query should return:

VessNum, StatDate, Status, Location
V-1, 1/3/03, Full, Rm101
V-2, 1/15/03, Full, Rm101

As you can from the example data, there is sometimes a
status change that occurs in the same day for a a
particular vessel, I only want to return the latest
status.

The problem I'm having is distinguishing the latest status
update for vessels that have multiple status changes on
the same day. I've been able to solve it by choosing the
Max(StatID), however, this forces the data entry to be
made in the actual order that the status events occur. It
would be better if the query could work regardless of the
data entry order (could be that one data entry person
enters the cleaning event, and another enters the filling
event depending on how the paperwork goes.)

I think that this could work by using the table
TblLocPickList to identify the last event that should have
occured (ie Max(LocID)). The process workflow always is
in this order.

I've tried to do this unsuccessfully.

Can anyone help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Todd]

Tom,
It all comes down to the cycle times in our process flow.
It is not physically possible for the sequence you
described (full, empty, cleaned) to occur in the same
day. Most of the steps are days or weeks long. There are
only a few steps that could potentially occur within the
same day, and they are in the middle of the pick-list
(note: there are many more steps than the 3 that I listed
in the example).

Therefore, I'd appreciate it if you could go back to my
original question and help me with the query to
distinguish duplicate date entries by the order of the
status sequence.

Thanks so much for your help.

-----Original Message-----
Correction:

In the third paragraph, change to:

Have you made the combination of VessNum / StatusDate unique? If you
allow a VESSEL to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

Dear Todd:

Your post says, in part, "there is sometimes a status change that
occurs in the same day for a a [sic] particular vessel, I want to
return the latest status."

This is in the StatusDate column of TblStatus, isn't it?

Have you made the combination of VessNum / StatusDate unique? If you
allow a Location to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

The query I will give you can return the most recent status or
statusses (I'm making up new words as I go along):

Now, looking at your data, and your proposed solution, this conflict
occurs for VessNum = V-1. The "latest status" is both Full, Rm101 AND
Empty, Storage. Yet somehow, miraculously, you have shown only the
Full, Rm101 in the result.

Now, from what I see, this is pretty much what you already realized.
There isn't enough information in your data do distinguish a sequence
between multiple events that occur on the same date. And you have
correctly identified one of the reasons why the StatusID may not give
you a correct sequencing of such rows. It is always the case that
someone may forget to make such an entry, then come back and make it
out of sequence!

You propose to use what I understand to be a normal sequence of events
as represented in the PickList to determine which happend last. I
wonder greatly about that.

Consider a case when a vessel starts the day as empty, then is
cleaned, and ends up the day full. That would work well.

Now consider another vessel that starts the day full, then becomes
empty, then cleaned. This vessel has the exact same 3 PickList steps
recorded, but not in the same sequence. To arrange that one's
sequence as you suggest would result in incorrect data.

You could put the time of day into StatDate as well as the date. That
would obviously tend to put them in correct sequence. An alternative
would be to add an integer sequencing column and use this to show the
order things happen both on the screen during data entry and then in
reporting. When a user enters an event out of sequence, they would
then have to move it into the correct sequence on the screen, with the
software manipulating this sequence value between the rows for that
vessel until the sequence is show properly. This assumes the person
doing the data entry has a way to know just what that sequence is for
that vessel.

Otherwise, the physical procedure would have to include a record of
the time of day when each step occurs. I do not believe there are any
likely alternatives to these. The person recording a step will need
to have a watch on, and one that is set correctly, so he can record
the time. The database will complain loud and hard if two steps are
submitted for the same vessel at the same date and time, because that
leaves a vessel in an ambiguous state at that time. This presumes you
protect it by creating the unique constraing I suggested on VessNum
and StatDate.

There is no substitute for eliminating ambiguity here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have the following two tables:

TblStatus
with fields StatID (autonum), VessNum, StatDate, Status,
Location

example data
StatID, VessNum, StatDate, Status, Location
1,V-1, 1/1/03, Empty, Storage
2,V-1, 1/3/03, Full, Rm101
3,V-1, 1/3/03, Cleaned, n/a
3,V-2, 1/12/03, Empty, Storage
4,V-2, 1/15/03, Full, Rm 101

TblLocPickList
with fields LocID, Location, Status

example data
LocID, Location, Status
1, Storage, Empty
2, Off-Site, Empty
11, n/a, Cleaned
12, Rm101, Cleaned
20, Rm101, Full
21, Rm202, Full

The LocID field in TblLocPickList is not an autonumber
field, it is set up so that the Status items are ordered
in the sequential order that they should occur for our
workflow. Note: this also feeds a combo box on a status
entry form.

I'm trying to write a query that will return the latest
status for each vessel. In the case of the example data
above, the query should return:

VessNum, StatDate, Status, Location
V-1, 1/3/03, Full, Rm101
V-2, 1/15/03, Full, Rm101

As you can from the example data, there is sometimes a
status change that occurs in the same day for a a
particular vessel, I only want to return the latest
status.

The problem I'm having is distinguishing the latest status
update for vessels that have multiple status changes on
the same day. I've been able to solve it by choosing the
Max(StatID), however, this forces the data entry to be
made in the actual order that the status events occur. It
would be better if the query could work regardless of the
data entry order (could be that one data entry person
enters the cleaning event, and another enters the filling
event depending on how the paperwork goes.)

I think that this could work by using the table
TblLocPickList to identify the last event that should have
occured (ie Max(LocID)). The process workflow always is
in this order.

I've tried to do this unsuccessfully.

Can anyone help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.

 

[Tom Ellison]

Dear Todd:

So then it is certain that whenever more than one step occurs in the
same day they will only always occur in the sequence of LocID. Do I
have this right? Within any given date, they will never become Full,
then subsequently Empty. The process of filling and the process of
emptying never occur within the same day, and in that order. Right?

So you now need to find the status with the maximum StatusDate and,
from within that date, the one with the maximum value of StatusID.

Here is a basic approach to be able to produce the one Status row as
described:

SELECT <some columns>
FROM TblStatus S
WHERE S.StatusDate = (SELECT MAX(StatusDate) FROM TblStatus S1
WHERE S1.VessNum = S.VessNum)
AND S.StatID = (SELECT MAX(StatID) FROM TblStatus S1
WHERE S1.VessNum = S.VessNum AND S1.StatusDate =
(SELECT MAX(StatusDate) FROM TblStatus S2
WHERE S2.VessNum = S1.VessNum))

Please let me know if this works out for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,
It all comes down to the cycle times in our process flow.
It is not physically possible for the sequence you
described (full, empty, cleaned) to occur in the same
day. Most of the steps are days or weeks long. There are
only a few steps that could potentially occur within the
same day, and they are in the middle of the pick-list
(note: there are many more steps than the 3 that I listed
in the example).

Therefore, I'd appreciate it if you could go back to my
original question and help me with the query to
distinguish duplicate date entries by the order of the
status sequence.

Thanks so much for your help.

-----Original Message-----
Correction:

In the third paragraph, change to:

Have you made the combination of VessNum / StatusDate unique? If you
allow a VESSEL to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

Dear Todd:

Your post says, in part, "there is sometimes a status change that
occurs in the same day for a a [sic] particular vessel, I want to
return the latest status."

This is in the StatusDate column of TblStatus, isn't it?

Have you made the combination of VessNum / StatusDate unique? If you
allow a Location to have two status rows for the exact same date, then
which one is "the latest status?" Obviously, there would be a
conflict there.

The query I will give you can return the most recent status or
statusses (I'm making up new words as I go along):

Now, looking at your data, and your proposed solution, this conflict
occurs for VessNum = V-1. The "latest status" is both Full, Rm101 AND
Empty, Storage. Yet somehow, miraculously, you have shown only the
Full, Rm101 in the result.

Now, from what I see, this is pretty much what you already realized.
There isn't enough information in your data do distinguish a sequence
between multiple events that occur on the same date. And you have
correctly identified one of the reasons why the StatusID may not give
you a correct sequencing of such rows. It is always the case that
someone may forget to make such an entry, then come back and make it
out of sequence!

You propose to use what I understand to be a normal sequence of events
as represented in the PickList to determine which happend last. I
wonder greatly about that.

Consider a case when a vessel starts the day as empty, then is
cleaned, and ends up the day full. That would work well.

Now consider another vessel that starts the day full, then becomes
empty, then cleaned. This vessel has the exact same 3 PickList steps
recorded, but not in the same sequence. To arrange that one's
sequence as you suggest would result in incorrect data.

You could put the time of day into StatDate as well as the date. That
would obviously tend to put them in correct sequence. An alternative
would be to add an integer sequencing column and use this to show the
order things happen both on the screen during data entry and then in
reporting. When a user enters an event out of sequence, they would
then have to move it into the correct sequence on the screen, with the
software manipulating this sequence value between the rows for that
vessel until the sequence is show properly. This assumes the person
doing the data entry has a way to know just what that sequence is for
that vessel.

Otherwise, the physical procedure would have to include a record of
the time of day when each step occurs. I do not believe there are any
likely alternatives to these. The person recording a step will need
to have a watch on, and one that is set correctly, so he can record
the time. The database will complain loud and hard if two steps are
submitted for the same vessel at the same date and time, because that
leaves a vessel in an ambiguous state at that time. This presumes you
protect it by creating the unique constraing I suggested on VessNum
and StatDate.

There is no substitute for eliminating ambiguity here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Dec 2003 13:29:42 -0800, "Todd"

I have the following two tables:

TblStatus
with fields StatID (autonum), VessNum, StatDate, Status,
Location

example data
StatID, VessNum, StatDate, Status, Location
1,V-1, 1/1/03, Empty, Storage
2,V-1, 1/3/03, Full, Rm101
3,V-1, 1/3/03, Cleaned, n/a
3,V-2, 1/12/03, Empty, Storage
4,V-2, 1/15/03, Full, Rm 101

TblLocPickList
with fields LocID, Location, Status

example data
LocID, Location, Status
1, Storage, Empty
2, Off-Site, Empty
11, n/a, Cleaned
12, Rm101, Cleaned
20, Rm101, Full
21, Rm202, Full

The LocID field in TblLocPickList is not an autonumber
field, it is set up so that the Status items are ordered
in the sequential order that they should occur for our
workflow. Note: this also feeds a combo box on a status
entry form.

I'm trying to write a query that will return the latest
status for each vessel. In the case of the example data
above, the query should return:

VessNum, StatDate, Status, Location
V-1, 1/3/03, Full, Rm101
V-2, 1/15/03, Full, Rm101

As you can from the example data, there is sometimes a
status change that occurs in the same day for a a
particular vessel, I only want to return the latest
status.

The problem I'm having is distinguishing the latest status
update for vessels that have multiple status changes on
the same day. I've been able to solve it by choosing the
Max(StatID), however, this forces the data entry to be
made in the actual order that the status events occur. It
would be better if the query could work regardless of the
data entry order (could be that one data entry person
enters the cleaning event, and another enters the filling
event depending on how the paperwork goes.)

I think that this could work by using the table
TblLocPickList to identify the last event that should have
occured (ie Max(LocID)). The process workflow always is
in this order.

I've tried to do this unsuccessfully.

Can anyone help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.