fill down table in access

Ok Ive looked and looked and read a multitude of threads to no avail. Please help me on this one.

I have imported a table into Access, I had no control over the original format of the table so Im stuck with the data as it is in that original doc.

So as it sits now in access..
index mnem client name
--------------------------------
1 310055 blah foo
2 blah2 john
3 blah4 joe
4 310099 grr12 bob
5 blah2 john

etc. * about 50,000 literally in size.

Looking at the empty cells in the 'number' column I need each of those to be filled. to end up with:
index mnem upin name
-------------------------------
1 310055 blah foo
2 310055 blah2 john
3 310055 blah4 joe
4 310099 grr12 bob
5 310099 blah2 john

I did this once in VB/excel but it was prohibitively slow. Im positive there is a better SQL/Access solution but just cant quite get it. My best effort to date is:
DLookUp("[mnem]","upin","[index]=" & [index]-1)
criteria mnem Is Null
In an update query updating mnem.

Downside all I get is say there is a block of 4 empty spaces in a row before the next mnem comes along, only 1 cell will be filled per run of the query.

i.e.
index mnem upin name
-------------------------------
1 310055 blah foo
2 310055 blah2 john
3 blah4 joe
4 310099 grr12 bob
5 310099 blah2 john


I figure #3 is somehow looking up at #2 before its updated and taking that empty value. ..just cant figure a way around it though.

I know this has been done before but no matter how much googling and forum diving Ive done I havent found something to specifically address this.

Thanks in advance for your help

The should get you started

Table1



SQL




Results




S-

Wow.. that is great! Now I have been trying to convert that into a Update query but keep getting the dreaded...
'Operation must use an updateable query' error.

..*sigh* so at any rate.. I have been studying that as well and have found nothing useful. bunch of stuff about permissions and then a few things about the jet engine not being able to find a unique record to update, but that doesnt seem right in this case. Is it illegal to use a subquery in an update?

..on a second note.
Just for my own edification. (Since I posted it in an odd format above.) If my old non-working SQL was

UPDATE upins SET upins.mnemonic = DLookUp("[mnemonic]","upins","[index]=" & [index]-1)
WHERE (((upins.mnemonic)="" Or (upins.mnemonic)=" " Or (upins.mnemonic) Is Null));

Why would that only update the first empty record per set. Say theres 5 empties in a row only the first would be updated. Just doesnt make sense to me. If you feel like guiding the blind a bit here I would thank you heartily on that as well.

Just trying to gain some understanding instead of taking your code and running you know.

Thanks endlessly..

Here is what you do

you change the query I gave you into a "Make Table Query"





The you write an update statement based on the new Temp table




This works

You have to base the update off the table and not a query, at least this is the way that works for me when I do this. Don't have to do this very often.

S-

Not that I didnt like the last one.. but the make table query alone took 27 hours to compete. I think cause Im now looking at a database of almost 100k entries on not so state of the art machines.

I think having a subquery going through the entire databse for each entry is the killer. ..isnt that like 100k*100k queries?

So I cant quite get it straight in my head but isnt there something that can be done along these lines...

index mNem ClientName
1 310055 blah foo
2 blah2 john
3 blah4 joe
4 310099 grr12 bob
5 blah2 john
index mNem ClientName
1 310055 blah foo
2 310099 grr12 bob


Taking data from the second table to fill in the first. So you get to a NULL in table 1 and lookup the corresponding data in table 2 that has mnem less or to what you've passed?

Still working.

If you have that many records in a table, it may be time to move to a new DB (SQl Server, MySQL)

The other option is to accept that this is a one time process and you can live with the system taking time for it.


Quote:


NO it could be view as 100K+100K, but it is really only query


If you thought of setting bu a new table like:

index mNem ClientName
1 310055 blah foo
4 310099 grr12 bob

As long as you keep the orginal Index, you might be able to get this to work, you will have to try.

S-

Thanks SBaxter, you've saved me countless tedious hours filling rows in Excel.

So did you respond to this post 5 years later because your machine finally finished running the query? LOL