Hello,
I have Data that looks like this:
Order.....Operation.....Work Center
1....................10....................1234
1....................20....................5678
1....................30....................9123
What I am trying to do is create a column that returns the previous work center. For example on Operation 30 I'd like this column to return 5678. Thanks in advance for your help.
Okay, I assume that Operation is in ascending order,and unique.
I have a table that lists village id, name, and enum id.
Village_id.......Name..........Enum_id
001...............ThisTown......234
002...............ThatTown.....354
003...............Mytown........898
The sql query to get this is simply
-
SELECT tblVillage.Village_Id, tblVillage.VillageName, tblVillage.Enumerator_Id
-
FROM tblVillage;
-
-
I know need to add a column to refer to the previous village
This code is LastVill: DMax("Village_id","tblVillage","Village_id<" & [village_id])
and the SQL looks like this
-
SELECT tblVillage.Village_Id, tblVillage.VillageName, tblVillage.Enumerator_Id, DMax("Village_id","tblVillage","Village_id<" & [village_id]) AS LastVill
-
FROM tblVillage;
-
-
Now I can use LastVill to get the enumerator I want as follows
LastEnum: DLookUp("Enumerator_ID","tblVillage","Village_ID=" & [LastVill])
and the SQL looks like this
-
SELECT tblVillage.Village_Id, tblVillage.VillageName, tblVillage.Enumerator_Id, DMax("Village_id","tblVillage","Village_id<" & [village_id]) AS LastVill, DLookUp("Enumerator_ID","tblVillage","Village_ID=" & [LastVill]) AS LastEnum
-
FROM tblVillage;
-
-
and I end up with
Village_id.......Name.........Enum_id...LastVill.. ...LastEnum
001...............ThisTown......234............... ..........#error#
002...............ThatTown.....354...........001.. ........234
003...............Mytown.......898...........002.. ........354