Filling in missing values

[nlbertram]

Hello -- I would like to create a new table by copying an existing table and also fill in some missing values in the new table.

Here is an example:

Original_table

Row ID Number Name Points

1 100019 CCF RTE 155
2 242
3 132
4 800004 CCF AHP 1
5 3
6 14
7 15

The missing values for the Number and Name fields need to filled in by duplicating an existing value. Here is what I need to have in the end.

New_table

Row ID Number Name Points

1 100019 CCF RTE 155
2 100019 CCF RTE 242
3 100019 CCF RTE 132
4 800004 CCF AHP 1
5 800004 CCF AHP 3
6 800004 CCF AHP 14
7 800004 CCF AHP 15


Any ideas?

 

[PHV]

One way:

SELECT A.[Row ID], B.Number, B.Name, A.Points
FROM [ExistingTable] A, [ExistingTable] B
WHERE B.Name > ' ' AND B.[Row ID] = (SELECT MAX([Row ID]) FROM  [ExistingTable] WHERE [Row ID]<=A.[Row ID] AND Name>' ')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nlbertram]

Thanks PHV. I am starting to understand. It seems a subquery will help solve the problem. However, I don't quite see how this creates a new table. Am I missing something?

Thanks again

 

[PHV]

SELECT A.[Row ID], B.Number, B.Name, A.Points
[!]INTO [New Table][/!]
FROM [ExistingTable] A, [ExistingTable] B
WHERE B.Name > ' ' AND B.[Row ID] = (SELECT MAX([Row ID]) FROM  [ExistingTable] WHERE [Row ID]<=A.[Row ID] AND Name>' ')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nlbertram]

Fantastic! It works great.