Solved Update blank values

[ProgramRasta]

Hi All,

I have tried unsuccessfully to create an update query that updates the blank cells with the last non-null cell.

In the attached database, the 'Description' field is what I am attempting to update and the 'Required After Update' is the desired result.

Many Thanks for your time.

Cat.

 

[GPGeorge]

Unfortunately, there is NO inherent order in a table in a relational database. That means your table, as it is presented in the same, is only one of a number of different sort orders possible. To make matters even worse, there is also NO way to reliably sort the records into the order present. It's entirely arbitrary. Sorry.

The only way to accomplish this is to add a way to sort in the order you want. How that could be done depends on what this data is all about. More context is always better than less context in addressing problems.

 

[plog]

What you want to achieve with the data you have is not possible. There is no order in a table.

There's no first record, no last record, no next, no 233rd. Order simply does not exist in a table. Think of a table as a bucket where you throw data. It just goes in where it goes in and exists intermingled with all the other records. Now, when you etract data from that bucket you can explicitly tell it what order to take, but you must have a way to define which is the first, the last, the next, the 233rd record.

Unfortunately, I see no field in your table that allows you to define an order on your data (e.g. a date field, an autonumber, etc.), therefore, it is not possible to logically achieve what you want.

 

[ProgramRasta]

Thank you for taking the time to reply. Would an auto number column make any difference?

 

[GPGeorge]

Thank you for taking the time to reply. Would an auto number column make any difference?

Not really. AutoNumber are guaranteed ONLY to be unique. Nothing else. They typically DO fall sequentially, but that's not a reliable factor in tasks such as this. What other fields are available? Dates and Times are good for sorting, for example. Order numbers are generally issued sequentially.

Any field available which can reliably sort in a relevant sequence will work.

Again, this sample is provided out of context. Context--when, where and how it is used--is an important part of the analysis.

 

[arnelgp]

add autonumber field (ID).
create Query1 (see in design view).
the create the Update Query (query2).

 

[ProgramRasta]

add autonumber field (ID).
create Query1 (see in design view).
the create the Update Query (query2).

Thank you very much.

I hope you all have a great Easter weekend.

Cat

 

[arnelgp]

Think of a table as a bucket where you throw data. It just goes in where it goes in and exists intermingled with all the other records.

i think table is well defined Structure, rather than vastness.
like a well defined structure, it has header that defines how many records.
and each records has pointers to one another (what record is above or below, that is why without using autonumber you can
use the method .movefirst, movenext, etc.)

imagine you are viewing a table in datasheet view.
and you know what record is the second and you press down key to go there.
does it bring you to different record (or random record), than the one you are viewing?
it does not, it goes to the one you want to go.

 

[GPGeorge]

i think table is well defined Structure, rather than vastness.
like a well defined structure, it has header that defines how many records.
and each records has pointers to one another (what record is above or below, that is why without using autonumber you can
use the method .movefirst, movenext, etc.)

imagine you are viewing a table in datasheet view.
and you know what record is the second and you press down key to go there.
does it bring you to different record (or random record), than the one you are viewing?
it does not, it goes to the one you want to go.

Please explain how a header defines how many records a table holds.

Records do not, in themselves point to other records. However, it is true that when you retrieve a recordset which has been ordered by default or by application of an ORDER BY clause, you can traverse the records in that recordset. It's the order imposed by the recordset that determines "Next" and "Previous".

Applying a different ORDER BY clause, using different values in a different field or fields, changes the resulting recordset so "Next" and "Previous" are now relative to a different sort order.

That's why it is true that the table presented in the sample accdb can't be used to fill in empty fields. There's no way to sort it so that it can be done reliably, time after time after time.

You confuse, apparently, the definition of a table and a recordset based on that table.

 

[arnelgp]

Please explain how a header defines how many records a table holds.

each file has header structures.
i can't define them to you.
maybe you asked microsoft how they put the table together (in low level that is in C/C++).
if you can Read something about Data Structures (these are thought in IT schools and books).
you can even google them.
they discussed about the very basics, Trees/Binary trees, etc.
these are the very foundation of how tables are made.
then they will discussed how each nodes, leaves of the tree connects
to each other (so they know what is on left, right, above and below of each other).
then they will teach you how it can be sorted.

 

[GPGeorge]

each file has header structures.
i can't define them to you.
maybe you asked microsoft how they put the table together (in low level that is in C/C++).
if you can Read something about Data Structures (these are thought in IT schools and books).
you can even google them.
they discussed about the very basics, Trees/Binary trees, etc.
these are the very foundation of how tables are made.
then they will discussed how each nodes, leaves of the tree connects
to each other (so they know what is on left, right, above and below of each other).
then they will teach you how it can be sorted.

All of which misses the basic point, anyway.

Tables do NOT have an inherent sort order. The only way to sort the records in a table in a manner that allows you reliably know which record is above and below any other record AT ANY GIVEN DISPLAY is to apply an ORDER BY clause.

 

[arnelgp]

Tables do NOT have an inherent sort order

who said they have? me

 

[GPGeorge]

Your response in post #8 indicated that you seemed to think so. I am relieved to hear that you do not.

 

[arnelgp]

Your response in post #8 indicated that you seemed to think so

not a slight idea.

 

[Pat Hartman]

Jet/ACE work differently from a true RDBMS and so they tend to mask the way recordsets are produced by other engines that are multi-threaded. Unless a recordset is sorted on a unique identifier, it might be returned as a,b,c today and c,a,b tomorrow.