Update rows of data from other rows

[anasttin]

Hi,



I have a table of data which will have values missing in some fields. What I'd like is the code for an update query that will look for records with these null values and then take the value from the previous relevant record (with the previous relevant record being based on an "Order" and "Name" fields), and then populate the missing values. Please see the Before and After tables below as an example.

Can anyone assist?

Thanks so much in advance for your help.

BEFORE

Name	Age	Order
Tom	20	1
Dick	22	2
Harry	31	3
Dick		4
Tom		5

AFTER

Name	Age	Order
Tom	20	1
Dick	22	2
Harry	31	3
Dick	22	4
Tom	20	5

[CJ_London]

it really looks like your data is not stored correctly, you should have name/age in one table so dick and tom only appears once. Also age should always be calculated, you should instead be storing the date of birth. Otherwise, next year you will need to change all the ages.

And what is the significance of order?

[anasttin]

Hi Ajax,

The data tables I showed are just an example to illustrate my need (and a bad example at that). It was just my attempt to simplify the question.

My real need is still there, namely to populate null fields for records with the same key ("name" for example), using another field to determine the most recent, previous record with that missing field (the "Order" field for example, with the highest value).

I'm sure its doable but not sure how.

Can you assist?

Thanks

it really looks like your data is not stored correctly, you should have name/age in one table so dick and tom only appears once. Also age should always be calculated, you should instead be storing the date of birth. Otherwise, next year you will need to change all the ages.

And what is the significance of order?

[Micron]

Making it too simple and too far removed from the real thing can be very problematic for those trying to help. For one thing, Name is not a label (name) you can use for any field or control. The only way I can see accomplishing the goal is to either create a custom function if this is strictly working against tables (I struggle too much with sub queries to be of much help there) or you could calculate this on a form. The problem I'd have with this outside of the procedure method is how to get around the circular reference since name has to refer to itself when looking up the DMax of Age based on that name. A calculated textbox on a form along with a textbox that references the table name field can solve the circular reference issue.

=IIf(IsNull([text2]),DMax("[age]","[tblMaxWhereNull]","[svname] = '" & [text0] & "'"),[text2])

where text2 holds the name, text0 the age and the calculated age control contains the expression. If the age appears Null, the calculation provides the age you need. However, this would only be a temporary thing that allows you to do the updates and fix things. It shouldn't be an on-going concern, otherwise I also tend to think you have a data structure issue. Be warned that DMax calculations on your table might be slower than you'd like, and will only slow down as the records increase, which is why I don't consider this a viable solution for on-going data fixing.

[anasttin]

Thanks Micron.

I was kind of thinking I could use a bit of SQL that utilised "SELECT TOP 1" on the "Order" column (as long as it wasn't NULL), and take the value from there, to populate the missing field data.

Is there anything workable on that front? And if so, what would the code be?

Again... thanks for the help.

Making it too simple and too far removed from the real thing can be very problematic for those trying to help. For one thing, Name is not a label (name) you can use for any field or control. The only way I can see accomplishing the goal is to either create a custom function if this is strictly working against tables (I struggle too much with sub queries to be of much help there) or you could calculate this on a form. The problem I'd have with this outside of the procedure method is how to get around the circular reference since name has to refer to itself when looking up the DMax of Age based on that name. A calculated textbox on a form along with a textbox that references the table name field can solve the circular reference issue.

=IIf(IsNull([text2]),DMax("[age]","[tblMaxWhereNull]","[svname] = '" & [text0] & "'"),[text2])

where text2 holds the name, text0 the age and the calculated age control contains the expression. If the age appears Null, the calculation provides the age you need. However, this would only be a temporary thing that allows you to do the updates and fix things. It shouldn't be an on-going concern, otherwise I also tend to think you have a data structure issue. Be warned that DMax calculations on your table might be slower than you'd like, and will only slow down as the records increase, which is why I don't consider this a viable solution for on-going data fixing.

[CJ_London]

you would have to use domain functions. for a normal select query you can use subqueries, but update queries cannot use subqueries

since your data is not clear, best I can suggest is something like

Update mytable
Set age=dlookup("age","mytable","name='" & name & "' AND isnull(age)=false AND order=" & dmax("order","mytable","name='" & name & "' AND isnull(age)=false"))

[Micron]

Good point, Ajax. But an update can use a select subquery as a table, no?
Too complicated for me. I think I'd try a vba procedure.

@anasttin - experiment with a table copy. When I placed my field and table names in the suggested expression, it wiped all the age values.
Later, if I can, I'll try playing around with the suggestion to see what parts of it return what, but Ajax will probably beat me to it as I have other things nagging at me at present.

[CJ_London]

But an update can use a select subquery as a table, no?

it can but in this case the OP needs the 'previous' value so needs to reference the 'original' table on a record by record basis. If they just needed the max value for example, I think you could do it with an aliased group by query

The OP has not been clear about the real requirement and there are other ways to skin the cat. My way is not that efficient but you can only work with what you have.

There is one small refinement to my suggestion. Since it only needs to update records where age is null, that can be included as a criteria

[Micron]

Well, as I mentioned, I tend to struggle with sub queries, but I'm not going to delve into a vba procedure with the pseudo fields and tables that were provided. It would just have to be re-written to suit reality.

[katrinabelle]

Hi sir .. can i ask a help... i would like to have a sample database which shows the opening and closing balance
example: DATE ITEM OpeningBalance Arrival Sold Ending
06/27/2017 Ballpen 5 10 5 10
and on the next day the ending balance must be the Opening balance like
06/28/2017 Ballpen 10 0 5 5

[ssanfu]

@ katrinabelle,
First, Welcome to the forum.

So what you have done is called "HI-jacking a thread". You posted your question in someone else's thread.
By doing this, you will get very few responses because no one will see your post.

You should start your own thread, describing your problem or question..

Good luck......