Updating One Field in All Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Against all odds, I have another question. I need to go through the DB
after reading in a file and update one field in each record for the whole
table. And I have no idea how to do it.

Once I've read the file, I need to sort it by Part_Num, Serial_Num,
Store_Num, Date, Time (all are fields). Then I need to add to the Delivery
field based on the number of deliveries.

ie. There will be repeating Part_Num, Serial_Num, Store_Num, but varying
Date and Time. The earliest delivery gets a Delivery value of 1. The latest
delivery gets a value indicating the total number of deliveries. So if there
were 5 deliveries, the latest record will have a Delivery value of 5.

And then it repeats from 1 to whatever for the next Part, Serial, Store. I
need to perform this check EACH time a file is inserted into DB. And there
may be older files added, so I have to perform the check for every record
everytime. So if there is a value in delivery already, I can't assume the
value will remain the same.

I hope that explaination makes sense...

So I'm not sure if I need to create a query that would order the table as I
need it. And if so, I don't know how to do that programmatically. I can
create a query using CreateQueryDef, but once created, I can't find an
example of going through the results of that query and updating a field of
each record.

Might a DoCmd.RunSQL be more appropriate?

Again, any help is appreciated. Or maybe a relevant link?

Thanks,
Jay
 
Doing it in SQL is almost always more efficient than using a recordset.
However, I question your database design if you have such a need. Perhaps
there's some additional normalization you can do that will eliminate this
requirement for wholesale updates.
 
I can't see any other way to do it, based on how the DB currently exists.

This is the only wholesale update that's performed, and it's only because I
can't control what order the files are parsed in, or what data is in the
files.

This fix was suggested only because no tables have to be added/changed.
We're willing to sacrifice speed of operation at the moment.

Can this type of wholesale change be done using SQL? I'm not terribly
strong in anything but the most basic SQL.

Thanks,
Jay
 
I looked into your suggestion of additional normalization, and I can't see
anything else I can change. Regardless of whether or not I 'can' change the
tables, I was curious if I 'should'.

When looking at Part_Num, Serial_Num, Store_Num, Date, Time and Delivery, it
seems to me that that is as low as I can go, so to speak. They are all
dependant on each other. The only thing I don't have when the row is created
is the delivery number. I can't see a way to add that as I insert into the
DB, since I don't control the order the files are read. And the file is read
from top to bottom, which is actually from latest to earliest, for the given
date range.

I can't change the file format (legacy system), so I have to make the Access
DB do what I need it to do. If I do this wholesale change, then I can ensure
that the record with the highest Delivery number is the latest delivery.

The query (or sorting) SQL code is this I believe:
SELECT tblDelivery.Part_Num, tblDelivery.Serial_Num, tblDelivery.Store_Num,
tblDelivery.Date, tblDelivery.Time
FROM tblDelivery
ORDER BY tblDelivery.Part_Num, tblDelivery.Serial_Num,
tblDelivery.Store_Num, tblDelivery.Date, tblDelivery.Time;

Now I'm not sure how I could loop through the resulting list of records and
go through the records that have identical Part_Num, Serial_Num, Store_Num,
increase the Delivery field by one as I go through the date/times.

Any suggestions?

Thanks again,
Jay
 
Alright, I managed to figure out how to do the wholesale change using
Recordsets. I haven't been able to figure out a SQL solution, but this works
so I'm happy.

I'll post the answer just in case someone can use it. Or maybe there's a
more elegant solution...?

Note: I used a query (qrySortData) to sort the data as I need it sorted.
Without the appropriate query, this code does nothing helpful.

Dim dbs As Database
Dim rstData As Recordset
Set dbs = CurrentDb
Set rstTryData = dbs.OpenRecordset("qrySortData")

Dim currentPartNo As String
Dim currentSerialNo As String
Dim currentStoreNo As String
Dim delivery As Integer

rstData.MoveFirst

Do While Not rstData.EOF

rstData.Edit

If rstData!PART_NO = currentPartNo And _
rstData!SER_NO = currentSerialNo And _
rstData!STORE_NO = currentStoreNo Then

delivery = delivery + 1
rstData!DELV = delivery

Else
delivery = 1
rstData!DELV = delivery

currentPartNo = rstData!PART_NO
currentSerialNo = rstData!SERIAL_NO
currentStoreNo = rstData!STORE_NO
End If

rstData.Update
rstData.MoveNext

Loop
rstData.Close
dbs.Close

Jay
 
Back
Top