Update the date to match the first record changed in a group

[Tamrak]

Good afternoon,

I have a table consisting of the following fields:

PO
DateAppended
Status
ShippedDate
ReceivedDate

I created another field that concatenate these five fields together in order to prevent any duplication. This ConCat field is the 6th field and it is my primary field.

I have another table that compares the records with this table, using the concatenation field as foreign key. Any unmatched record (not found), will be appended to this current table.

Each record will be appended to this table when any of these conditions are met:

1. Status has been changed and / or
2. ShippedDate has been changed and /or
3. ReceivedDate has been changed

I have seven statuses;

1-Requested
2-Issued
3-Sent
4-Fulfilled
5-Cancelled
6-onHold
7-Resumed

The logic will be -> the PO is requested -> issued -> send to vendor -> vendor fulfilled PO. At anytime -> the PO can be cancelled or it can be put on hold. It can be resumed after everyone agrees to lift off the on-hold status.

I am using the queries to generate the result. So far, I am 90% successful, if there is no duplicated status.

I include some of the records as an example, based on the field given. (I have not included the concatenated field.)

PO, DateAppended, Status, ShippedDate, ReceivedDate
990,12/1/2005,Requested,12/15/2005, 12/19/2005
990,12/5/2005,Issued, 12/15/2005, 12/19/2005
990,12/7/2005,Issued,12/17/2005, 12/19/2005
990,12/9/2005,Sent,12/17/2005,12/19/2005
990,12/12/2005, onHold,12/17/2005,12/19/2005
990,12/21/2005,Resumed, 12/27/2005, 12/30/2005
990,12/23/2005,Cancelled,12/27/2005,12/30/2005
990,12/28/2005, Cancelled, 12/28/2005,12/28/2005
991,12/2/2005,Requested, 01/15/2006, 01/22/2006
991,12/5/2005,Issued, 01/15/2006, 01/22/2006
991,12/6/2006,Issued, 01/15/2006, 01/24/2006
992,12/3/2005,Requested, 12/22/2005, 12/27/2005
992,12/8/2005,Issued, 12/22/2005,12/27/2005
992,12/9/2005,Issued, 12/26/2005,1/4/2006
992,12/11/2005,Sent, 12/26/2005, 1/4/2006
992,12/13/2005,Sent, 12/31/2005, 1/4/2006
992,12/15/2005,Sent, 12/31/2005, 1/7/2006
992,12/17/2005,Fulfilled, 12/31/2005, 1/7/2006
992,1/8/2006,Fulfilled, 12/31/2005, 1/8/2006
…
…
…
And so on..
…

I am looking for a code that will update the “DateAppended” field to match the first record that status has changed

The query should be grouped by PO:

Examples:

• PO # 990, we would like the DateAppended to be “12/23/2005” on all cancelled status
• PO # 991, we would like the DateAppended to be “12/05/2005” on all Issued status
• PO # 992, we would like the DateAppended to be “12/08/2005” on all Issued status and DateAppended to be “12/11/2005” on all Sent status. The same thing for Fulfilled Status, which should be “12/17/2005”

Can you provide any type of codes that I can get the result above? Thanks.

 

[quatermass]

Hi Tamrak,

If I've got the idea:

select 
t1.PO, t2.min_DateAppend, t1.Status, t1.ShippedDate, t1.ReceivedDate
from 
table t1, 
    (select 
      PO, min(DateAppended) as min_DateAppended, Status
     from 
     table
     group by PO, Status) t2

where t1.PO = t2.PO and t1.Status = t2.Status

This gets the min status date as a sub-select and assigns it to each of the PO records with the same status


Hope it helps

Kevin

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[Tamrak]

Hi Quatermass,

All I am looking for is just the main table, table1. How can I update all of the DateAppended field from this table to match the first record that the status has changed?

We do not need to involve the second table. Sorry for the confusion.

Thank you.

 

[Tamrak]

Hello again,

All I am looking is just this main table, tblPO.

I would like to see the DateAppended field being updated to match the first record that the status changes.

For example, using PO # 992

992,12/3/2005,Requested, 12/22/2005, 12/27/2005
992,12/8/2005,Issued, 12/22/2005,12/27/2005
992,12/9/2005,Issued, 12/26/2005,1/4/2006
992,12/11/2005,Sent, 12/26/2005, 1/4/2006
992,12/13/2005,Sent, 12/31/2005, 1/4/2006
992,12/15/2005,Sent, 12/31/2005, 1/7/2006
992,12/17/2005,Fulfilled, 12/31/2005, 1/7/2006
992,1/8/2006,Fulfilled, 12/31/2005, 1/8/2006


After the update, it will be like this:

992,12/3/2005,Requested, 12/22/2005, 12/27/2005
992,12/8/2005,Issued, 12/22/2005,12/27/2005
992, 12/8/2005 ,Issued, 12/26/2005,1/4/2006
992,12/11/2005,Sent, 12/26/2005, 1/4/2006
992,12/11/2005 ,Sent, 12/31/2005, 1/4/2006
992, 12/11/2005 ,Sent, 12/31/2005, 1/7/2006
992,12/17/2005,Fulfilled, 12/31/2005, 1/7/2006
992, 12/17/2005 ,Fulfilled, 12/31/2005, 1/8/2006

The date in red will match the previous record that the status first changes.

I think an update query might be able to do the trick for the entire table. I do apologize for any confusion. I am trying to explain the best I could.

Thanks.

 

[quatermass]

Hi,

You can either use the code to generate a new table, or use the sub-select statement as an update statement as follows:

update table 
set DateAppended = subselect.min_DateAppended
from 
table, 
(select 
     PO, min(DateAppended) as min_DateAppended, Status
     from 
     table
     group by PO, Status
) subselect
where table.PO = subselect.PO
and table.Status = subselect.Status

The SQL is valid SQL Server syntax, and should be valid in Access. If the statement is invalid in Access (and its been a long time since I used Access) you should use the original code to generate a new table, and replace the existing one with the new one.


Hope it helps

Kevin


**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[Tamrak]

Kevin,

Thank you for your time. The code has a little bit difficulties when putting into MS Access queries. It gave me an error:

"Syntax error (missing operator)in query expression 'subselect.min_DateAppended from table'.

I am uncertain what operator is missing or the Access Query is not compatible with the code.

I do appreciate your time assisting regarding this matter. Thanks.

 

[PHV]

And what about something like this ?

UPDATE tblPO
SET DateAppended=DMin("DateAppended","tblPO","PO=" & [PO] & " AND Status='" & [Status] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Dear PHV,

Thank you so much. Your codes are simple and made it perfectly of what I have been looking for. Thanks for your time. Another star for your contribution ****