Update record with data from previous record

[Steve Thompson]

I have a table with data imported from Excel. Because of the way the Excel
worksheet was arranged, the data looks like this...

ID Field1 Field2
-----------------------
1 Cat1 Day1
2 Day2
3 Day3
4 Cat2 Day1
5 Day2
6 Day3
7 Cat3 Day1
8 Day2
9 Day3

The blank values in Field1 should all be whatever value is directly above
them. So, records 2 & 3 should both have Cat1 in Field1, records 4 & 5 should
have Cat2, and so on.

I want to do something like "If Field1 is null, enter value from Field1 in
record where ID = Current ID - 1".

Can someone help with that? Thanks.

 

[louisjohnphillips]

I have a table with data imported from Excel. Because of the way the Excel
worksheet was arranged, the data looks like this...

ID    Field1    Field2
-----------------------
1     Cat1      Day1
2                  Day2
3                  Day3
4     Cat2      Day1
5                  Day2
6                  Day3
7     Cat3      Day1
8                  Day2
9                  Day3

The blank values in Field1 should all be whatever value is directly above
them. So, records 2 & 3 should both have Cat1 in Field1, records 4 & 5 should
have Cat2, and so on.

I want to do something like "If Field1 is null, enter value from Field1 in
record where ID = Current ID - 1".

Can someone help with that? Thanks.

Can you attached a script to the OnChange event of Field1?

Me.Field1.DefaultValue = chr(41) & Me.Field1.value & chr(41)

The expression chr(41) represents a quotation mark.

 

[John W. Vinson]

I have a table with data imported from Excel. Because of the way the Excel
worksheet was arranged, the data looks like this...

ID Field1 Field2
-----------------------
1 Cat1 Day1
2 Day2
3 Day3
4 Cat2 Day1
5 Day2
6 Day3
7 Cat3 Day1
8 Day2
9 Day3

The blank values in Field1 should all be whatever value is directly above
them. So, records 2 & 3 should both have Cat1 in Field1, records 4 & 5 should
have Cat2, and so on.

I want to do something like "If Field1 is null, enter value from Field1 in
record where ID = Current ID - 1".

Can someone help with that? Thanks.

IF - and it can be a pretty big if! - you can count on the ID being strictly
sequential (that is, ID 3 will always be associated with ID 1), you can use an
Update query:

Update MyTable SET Field1 = DLookUp("Field1", "MyTable", "ID = " & DMax("ID",
"MyTable", "ID <" & [ID])) WHERE Field1 IS NULL;

I'm using the (rather inefficient) domain functions because queries involving
the Max totals function aren't updateable.

 

[Cassie Friesen]

Steve, were you able to make John's suggestion work? Or John, do you have
any ideas for me - I am wanting to do the exact same thing as Steve.
However, when I use this suggestion it only updates the very next record and
not all records where 'field1' is null. I know I'm missing something easy...
Cassie

I have a table with data imported from Excel. Because of the way the Excel
worksheet was arranged, the data looks like this...

ID Field1 Field2
-----------------------
1 Cat1 Day1
2 Day2
3 Day3
4 Cat2 Day1
5 Day2
6 Day3
7 Cat3 Day1
8 Day2
9 Day3

The blank values in Field1 should all be whatever value is directly above
them. So, records 2 & 3 should both have Cat1 in Field1, records 4 & 5 should
have Cat2, and so on.

I want to do something like "If Field1 is null, enter value from Field1 in
record where ID = Current ID - 1".

Can someone help with that? Thanks.

IF - and it can be a pretty big if! - you can count on the ID being strictly
sequential (that is, ID 3 will always be associated with ID 1), you can use an
Update query:

Update MyTable SET Field1 = DLookUp("Field1", "MyTable", "ID = " & DMax("ID",
"MyTable", "ID <" & [ID])) WHERE Field1 IS NULL;

I'm using the (rather inefficient) domain functions because queries involving
the Max totals function aren't updateable.

 

[John W. Vinson]

On Wed, 13 Aug 2008 12:42:02 -0700, Cassie Friesen <Cassie

Steve, were you able to make John's suggestion work? Or John, do you have
any ideas for me - I am wanting to do the exact same thing as Steve.
However, when I use this suggestion it only updates the very next record and
not all records where 'field1' is null. I know I'm missing something easy...
Cassie

Please post the actual SQL view of the query you're running, and indicate how
you're executing the query.

 

[Cassie Friesen]

John, here is the SQL view of the query:
UPDATE T_GL_Expenditures SET T_GL_Expenditures.Program =
DLookUp("Program","T_GL_Expenditures","autonumber = " &
DMax("autonumber","T_GL_Expenditures","autonumber <" & [autonumber]))
WHERE (((T_GL_Expenditures.Program) Is Null Or
(T_GL_Expenditures.Program)=""));

Not sure what you mean by 'how I'm executing the query' - sorry if this is
an obvious thing...
Cassie