Copy values into following fields

[Heidestrand]

Hello,

in order to get a better data base I want to fill some empty fields in my data record with values from another data record.

In my table I have 5 columns, I created my table here very quickly because I'm not allowed to post pictures:

ID | Sales Receipt | Description | City | Country
1 | 21646521 | MEDICAL CENTER | San Diego | USA
2 | ________ | _______________ | Denver | _____
3 | 65475101 | LITTLETON HOSP | LITTLETON | USA

As you can see I have data records that are full, so all fields have something written in it. But then I also have a data record where there is a only one value written, the rest of the columns of this record is empty.
So now I want to create a procedure that should copy the values of the existing fields into the ones that are empty until a new sales receipt number comes in that column.
And it should perform this with my whole table.

Hope you can understand what I want to do. Does someone have a nice way on how to do it?

Thanks in advance,

Heide

 

[CJ_London]

you need to be clearer about what you want

copy the values of the existing fields

from which record?

until a new sales receipt number comes in that column.

what does this mean

suggest you need to post a bigger data sample including a 'before' (i.e. including empty records) and after (completed)

 

[Heidestrand]

@CJ_London:

Maybe it gets clearer when you see the end result. With copying values I mean copying the "content" of the field in column country in record 1 (here 21646521) into record 2 column country. And do this with all the other clomuns, but stop there where a new number appears (here 65475101).

ID | Sales Receipt | Description | City | Country
1 | 21646521 | MEDICAL CENTER | San Diego | USA
2 | 21646521 | MEDICAL CENTER | Denver | USA
3 | 65475101 | LITTLETON HOSP | LITTLETON | USA

This is how the data record should look like after the procedure.

I mean, is this possible? Does it make sense to do this? Or should I do something else?

 

[Galaxiom]

You need to learn about normalized database structure.

The locations should be stored in that table as a LocationID. The rest of the information about the location should be stored just once for each location in another table.

 

[CJ_London]

It is potentially doable, but need to clarify some more.

1. does ID really exist and is this the order that needs to be completed i.e. row 2 would be completed with the information in the record with a lower ID

2. Are there any gaps in the ID values? i.e. 1,2,3,5,6,9 no 4,7 or 8)

2. row 2 has Denver is the city column - is this to be overwritten with San Diego? Or does it have to stay Denver?

3. Is this a full example as it is very little to go on - for example do you have records with a sales receipt but no other data? Is the first record with a sales receipt always complete? Do you have two records with the same sales receipt but different values in all the other fields.

5. It is also worth asking why you need do this, it doesn't look like your data is normalised (you would normally have one record with all the detail) and there may be a better way.