Autofill Qry

[Arne]

I have a qry with following structure. How to make the qry autofill row2 and
3 with name1 and then autofil row5 with name2 and so on?

Column name
Row1 Name1
Row2
Row3
Row4 Name2
Row5

 

[Allen Browne]

This example assumes:
- the table is called Table1
- the fields are F1 (primary key) and F2 (the field to be filled)

UPDATE Table1
SET F2 =
(SELECT TOP 1 F2
FROM Table1 AS Dupe
WHERE Dupe.F2 Is Not Null
AND Dupe.F1 < Table1.F1
ORDER BY Dupe.F1 DESC)
WHERE F2 Is Null;

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

 

[M.L. Abram]

I am looking for a query such as this so I had set up a DB with the scenario
Arne had provided to test. When I executed the query I received an error
"Operation must use an updateable query". Reviewing the query it is setup as
an Update Query and I also reviewed the Help with no additional success. What
am I missing?

This example assumes:
- the table is called Table1
- the fields are F1 (primary key) and F2 (the field to be filled)

UPDATE Table1
SET F2 =
(SELECT TOP 1 F2
FROM Table1 AS Dupe
WHERE Dupe.F2 Is Not Null
AND Dupe.F1 < Table1.F1
ORDER BY Dupe.F1 DESC)
WHERE F2 Is Null;

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

 

[Allen Browne]

This is a limitation in Access.

There is no logical reason why the UPDATE query won't run; it's just that
Access doesn't cooperate (perhaps erring on the side of caution for some
other non-obvious scenario.)

You may be able to use a function call instead of a subquery. DLookup()
isn't powerful enough if you need the ORDER BY clause (as in the example),
so we wrote a replacement that does allow you to specify how to sort the
records (which defines which is the TOP 1.) See:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am looking for a query such as this so I had set up a DB with the
scenario
Arne had provided to test. When I executed the query I received an error
"Operation must use an updateable query". Reviewing the query it is setup
as
an Update Query and I also reviewed the Help with no additional success.
What
am I missing?

 

[M.L. Abram]

Thank you for the response.