Need a faster way to do these Process 2

[villica]

Hello everyone, I was wondering if anyone can help me speed up this process.

I have a table that looks as follow.
PO LINE DATE PART QTY
111 1 06-23-03 A 36
0
222 1 06-23-03 A 48
333 1 07-01-03 A 72
444 1 07-01-03 A 48
444 2 07-01-03 A 36
555 1 06-23-03 B 0
0
12
777 2 06-24-03 D 0
36
888 1 06-24-03 C 66
0
0
999 1 06-24-03 C 48

I have a query that replaces the empty po with the po from the previous line.
I have a query that replaces the empty line with the previous line number.
and one for every other field

In total I have about 8 queries inside a loop

do while not .eof
query1
query2
query3
etc



Example query 1 to replace pn

select table1.id(select table2.[part]
from table1 as table2
where table2.id = table1.id -1) as newpart into table3)
from table1
where table1.pno is null

====>this query makes another table.
It copies the Id of the row where the Part is null to table3
It also copies the Part from the previous row to table 3



Example query 2
Update table3 inner join table1 on table2.id = table1.id
set table1.part = newpart

======>this query copies back the part from table3 to table1



As you can see there are two queries needed for every field that I need to field.



The problem I am having is that it runs very slow and in some computer with windows 98, I can not even open other applications until these process are done.


I was wondering if anyone has any suggestions as to how I can speed this up or if there is a better way of doing this. I really need your help.

thank you kindly




villica

 

[dzepeda]

This code will make the replacement:

Sub ReplaceField()

    Dim dbs As Database
    Dim rst As Recordset
    Dim Po_Previo As Integer
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM [Table]")

    With rst
        .MoveLast
        .MoveFirst
        Do While True
           If !Po = 0 Then
                .Edit
                !Po = Po_Previo
                .Update
            Else
                Po_Previo = !Po
            End If
            .MoveNext
            If .EOF Then
                Exit Do
            End If
    Loop
        .Close
    End With

    dbs.Close
End Sub
[code]

Greetings from Chile

 

[scriverb]

Try this code. This code will loop through all of your records and pass the previous records value for each field if the field is equal to Null. The only logic problem is you must make sure that the first record has values in all of its field. Otherwise, it will be passing null/empty data to the second record if datafields are null. If you are running ACCESS 2k be sure to have the library reference to Microsoft 3.6 DAO Object Library selected.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim vPO as Integer, vLine as Integer, vDate as Date, vPart as String, vQty as Integer
Set db = CurrentDB
Set rs = db.OpenRecordset("tblYourTableName", dbOpenDynaset)
rs.MoveFirst
vPO = rs("PO&quot
vPO = rs("Line&quot
vPO = rs("Date&quot
vPO = rs("Part&quot
vPO = rs("Qty&quot
rs.MoveNext
Do
rs.edit
If IsNull(rs("PO&quot) then
rs("PO&quot = vPO
End If
If IsNull(rs("Line&quot) then
rs("Line&quot = vLine
End If
If IsNull(rs("Date&quot) then
rs("Date&quot = vDate
End If
If IsNull(rs("Part&quot) then
rs("Part&quot = vPart
End If
If IsNull(rs("Qty&quot) then
rs("Qty&quot = vQty
End If
rs.Update
vPO = rs("PO&quot
vPO = rs("Line&quot
vPO = rs("Date&quot
vPO = rs("Part&quot
vPO = rs("Qty&quot
rs.MoveNext
Loop Until rs.eof
rs.close
db.close

Post back with any questions or problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

More generally, the data input routine(s) need to be 'fixed' to not place the patrtial or incomplete records in your db / tables.

Regardless of the origin, this is simply a disaster waiting for the moment of opportunity to cause grief, angst, and potential carrear opportunities -althouigh the later may be for someone else, or for you in another 'location'.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

MichaelRed: Point well taken. This database sounds like someone really dropped the ball in the initial design in carrying forward data from one record to the next. Whatever the reason he still has to fix the table. But, fixing the data is just the first step in the fixing the database problems.

Like you say the Input process that created this situation is a disaster and needs to be addressed.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[villica]

I want to thank all of you who replied with comments. I would like to thank specially dzepeda and scriverb for the help with your code.

Scriverb, your code help me the most. It used to take at least 10 minutes to do the replacement. Now the whole process is done in less than a minute. I will give you both a star.

P.S This is an excel file that I imported to a table. this is the reason the data looks very funny.

thanks everyone.


villica

 

[scriverb]

vicilla, Thanks for the Star. I am glad that I could help you in some way with your problem. Good luck.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???