Insert missing data

[razchip]

I've been trying to figure out how to insert missing data in a table. Another software produces the data, it contains the recipe and the ingredients, it doesn't replicate the recipe number for each row, so if I try to do some special sorts, I lose the connection. Below is an example:

Recipe Ingredient
10027 05243
07654
12695
10031 06457
07732

What I am trying to accomplish
10027 05243
10027 07654
10027 12695
10031 06547
10031 07732

Some recipes may be a single row, others may have twenty rows or more.

I've tried various null and insert statements, but keep missing the boat.

Thanks for any ideas.


Thanks for the help.
Greg

 

[dhookom]

Keep in mind records in a table are like marbles in a box. There is no particular order. You may get lucky if you create a recordset and loop through it capturing and updating the blank Recipe column with previous values.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Another software produces the data" in what formst? A table? CSV file (text file)?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[razchip]

The data is generated from a recipe software as a csv file.

Thanks for the help.
Greg

 

[SkipVought]

You might be better off importing into Excel and using a formula to propagate that recipe value.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[razchip]

Skip,

You're right, this will make it much easier and will only take a few minutes to set up. Thanks.

Thanks for the help.
Greg

 

[dhookom]

If this is a process that is repeated regularly, I would add a primary key during the import so there is a field that can be used as the sort order. Then create a little code that (or possibly and update query) to fill in the missing information.

Duane
Hook'D on Access
MS Access MVP