Update record with data from previous record

  • Thread starter Thread starter Steve Thompson
  • Start date Start date
S

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.
 
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.
Click to expand...


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.
 
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.
Click to expand...

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.
 
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

John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
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
Click to expand...

Please post the actual SQL view of the query you're running, and indicate how
you're executing the query.
 
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
 
Back
Top