Query to look at other rows

[Alex]

I have the following set of data in a query:

Row PartA PartB PartC PartD PartE PartF Date NewDate
1 111 1/15/05
2 222 1/9/05
3 222 211 1/1/05
4 222 211 212 1/5/05
5 222 233 244 1/6/05
6 222 233 244 255 1/9/05
7 333 233 244 255 345 1/16/05

In this query I need to populate the NewDate field on each row with the
earliest Date of either the Date on the row being populating or the Date on
a row where the last part (that’s not null) of the row to be populated
matches a part in another row. But, even if there is a part that matches the
last part of another row, all of the previous parts have to match too.

Examples:

Row 1 NewDate will be 1/15/05 because there are no other rows that contain
the same part as the last part in Row 1.

Row 2 NewDate should be the earliest Date in Row 2, Row 3, Row 4, Row 5, or
Row 6 because Rows 2 – 6 contain a part that matches the last part in Row 2.

Row 3 NewDate should be the earliest Date if Row 3 or Row 4 because Row 4
contains a part that matches the last part in Row 3.

Row 4 NewDate should be 1/5/05 because there are no parts in any rows that
match the last part in row 4.

Row 5 NewDate should be the earliest Date of Row 5 or Row 6 because Row 6
contains the same part as the last part in Row 5. Row 7 is not included
because although there is a 244 in Row 7, which matches the last part in Row
5, all of the previous parts in Row 7 must match as well.

Row 6 NewDate should be 1/9/05 because no other rows match the last part in
Row 6.

Any ideas how I can do this? Your help is much appreciated.

Alex

 

[[MVP] S.Clark]

Sometimes, it's easiest to write data to a table, then perform other action
queries against that table.

In this case, I think I'd go with that, because you need to perform some
logical checking to determine the min date, especially give the crosstab
nature of this data. If you're data is normalized somewhere back a few
steps, it may help as well.