Update query to copy previous row

[davea300]

Hello

What's the best way in an update query, using access 2003, to update data in a record based on the previous record having an identical value in a certain field?

For example my data looks something like this, in this example I want row 2 to fill in the blanks with the data from row 1 as they both have the same ID number at the end highlighted in red:


1 2 Anywhere Street 1 2 1 1 5423
2 4 Anywhere Street . . . . 5423
3 6 Anywhere Street 1 2 1 1 6401
4 8 Anywhere Street 1 2 1 1 6402

I've found various threads here on how to copy the whole record but I only want to copy the data from certain fields. More oftem than not the fields I'll be copying to will be NULL

Or if someone can tell me how to reference a field from the previous record within an update query I could probably work it out from there?

 

[plog]

First, there is no 'next' record in a table. Tables have no order. There is no first, no last, no previous, no 203rd.

Order exists on your data when you explicitly give it order. So, you must first tell me how you define the order on your table (is it a date field? an id field?).

Also, what's the end game? It sounds like you are going to be making duplicate rows of data in your table--why? What's the ultimate result you are trying to achieve?

 

[davea300]

The table is just ordered by ID number, it's linked to a main table that holds dates etc but the data I'm concerned with is within the table in question.

The end game is to 'clone' data from one record to many linked using the number in red in my original post.

The subject is repair values for common entry areas within blocks of flats/houses. The red number is the block number and the ID is the address of each individual flat. Each block only has one complete record in it so I need to copy this data to all the other addresses within the same block.

 

[plog]

Sounds like you need to restructure your tables correctly. You shouldn't be storing redundant data. If a 'block' is an entity of data, it needs to be its own table.

If you can post table and field names from what you currently have, we can help you structure your tables correctly.

 

[davea300]

The block isn't being treated as an entity in itself, it's merely a field that has been added to refer to a collection of properties i.e. provide a list of addresses in a report grouped by block number. Each address (house/flat) on the system has it's own record and 99% of the reporting required is based on their unique ID numbers.

I've been asked to provide a block information report that wasn't part of the original spec for this database. I'd rather tell the managers to get lost than restructure the tables for the sake of one report. This database is a replicated system, used over a LAN and various mobile devices with over 60 tables that has been in use for over 7 years.

All I'm really after is a method within a query to refer to data in another row and use this to populate the same fields in related records.