Get a record from a previous field and insert multiple records

[Markf]

I’ve just imported a bunch of records and am trying to cleanup the data. Here
is an example of what the table looks like:

ID SKU Fruit Status Location
--------------------------------------------------------------------------
1 922968 Apple In RF
2 924626 Orange In JF
3 Grape
4 Cherry
5 924630 Apple Out RF
6 Grape
7 924631 Orange Out RF
8 925178 Banana In RF
9 Cherry
10 Orange
11 Grape
12 925203 Orange Out JF
13 Cherry
14 926348 Apple Out JF

The records that have no data other than ‘Fruit’ are part of the previous
record but just were outputted on a separate line. The ID field Access added
as a key.

Is there any way to take the previous SKU and fill down the next null SKUs
or optimally output to a separate related Fruit table?

Thanks!
Mark

 

[David Lloyd]

Mark:

The following query seems to work in updating the SKU values. You can
expand the UPDATE query to include fields other than SKU.

UPDATE Fruit, Fruit AS Fruit_1 SET Fruit.SKU = [Fruit_1].[SKU]
WHERE (((Fruit.SKU) Is Null) AND ((Fruit_1.ID)=(SELECT MAX(ID) FROM Fruit as
Fruit2 WHERE Fruit2.ID<Fruit.ID)));

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I've just imported a bunch of records and am trying to cleanup the data.
Here
is an example of what the table looks like:

ID SKU Fruit Status Location
--------------------------------------------------------------------------
1 922968 Apple In RF
2 924626 Orange In JF
3 Grape
4 Cherry
5 924630 Apple Out RF
6 Grape
7 924631 Orange Out RF
8 925178 Banana In RF
9 Cherry
10 Orange
11 Grape
12 925203 Orange Out JF
13 Cherry
14 926348 Apple Out JF

The records that have no data other than 'Fruit' are part of the previous
record but just were outputted on a separate line. The ID field Access added
as a key.

Is there any way to take the previous SKU and fill down the next null SKUs
or optimally output to a separate related Fruit table?

Thanks!
Mark

 

[Markf]

Hey, that works perfectly. Thanks David!

 

[Markf]

Thanks again for your help David. I have another question. The table that I
am updating has…at this point…about a 1000 rows and for the query to update
it, it takes about 8 mins…on a Dell Optiplex SX280. Am I missing something in
the design or would this be faster in VB? If so, would you have any idea how
to convert the SQL to VB…very new to VB.

Cheers,
Mark