CODEWITHSUNDEEP
sqlvbams-access
Wsi
Wsi

Reputation: 111

MS Access pull down column via query

I have the following table in MS Access:

   ID | column1 | column2           | column3                
   ---+---------+-------------------+--------------
    1 | A       | Publishers        | Publishers
    2 | 01      | Commercial        |  
    3 | 02      | University Press  |  
    4 | B       | Place             | Place 
    5 | 01      | United States     |  
    6 | 04      | Western Europe    |  
    7 | 05      | Other             |  
    8 | C       | Language          | Language  
    9 | 01      | English           |  
   10 | 02      | French            |

I am looking for the following result

   ID |column1  | column2           | column3                
   ---+---------+-------------------+--------------
    1 | A       | Publishers        | Publishers
    2 | 01      | Commercial        | Publishers
    3 | 02      | University Press  | Publishers
    4 | B       | Place             | Place 
    5 | 01      | United States     | Place 
    6 | 04      | Western Europe    | Place 
    7 | 05      | Other             | Place 
    8 | C       | Language          | Language  
    9 | 01      | English           | Language  
   10 | 02      | French            | Language

So basically pulling down column3 heading. I have tried searching the net and asking other pals with some ms access knowledge. But really couldn't find any "pull down" query. Copy/paste wouldn't suffice as this will be performed many times in a day and with much larger data set. Can this be done without vba (looking to get this done through a query)?

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269833

If you have a column that specifies the ordering, you can do this with a correlated subquery:

select column1, column2,
       (select top (1) t2.column3
        from t as t2
        where t2.id <= t.id and
              t2.column3 is not null
        order by t2.id desc
       ) as column3
from t;

Upvotes: 1

Related Questions