UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code To Update Data In A Loop, Access 2013    
 
   
lamore
post Feb 12 2019, 10:10 AM
Post#1



Posts: 96
Joined: 25-August 14



Hi All,
Working on a database that grabs records from an Excel spreadsheet and formats and inserts into my access database, and I'm slowly fumbling my way through. So, in my table is a field of classification names(i.e. Assembler, Engineer, Designer, etc) what I need to do is go through all the data...approximately 1000 records and convert classification names into occupational numbers for example Assembler classification is 456221, and Engineer classification is 457822, and etc. so I'm thinking the SQL statement should be some kind of loop that goes through and says if this field is = "Assembler " Then that field is updated to "456221". But I don't know where to start.....Can someone point me in the right direction with some pseudo code of how I might accomplish this.....


Thanks in Advance,
Lenny
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2019, 10:31 AM
Post#2


UA Admin
Posts: 34,584
Joined: 20-June 02
From: Newcastle, WA


Actually, I would go to a table-driven solution, not a code driven solution for two reasons.

Maintainability--keeping a table up-to-date is far easier than constantly updating code.

Simplicity--with a mapping table to correlate text strings to occupational classifications is going to be simpler to implement as an update query.

Build the Mapping table with a field for Classifications and a field for the corresponding Occupational Classification Codes.

That also allows you to assign multiple categories to a single Code. E.g. Engineer and Engineering could be be entered as separate records in the table, both with corresponding Code 457822.

Join your import table to this mapping table and update the destination table as appropriate.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Feb 12 2019, 10:32 AM
Post#3


UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill


Hi,

It would be good to have a table for classifications - you would only need two fields, ClassificationNo and ClassificationName.

Make ClassificationNo the primary key (but not autonumber), place a unique index on ClassificationName.

Enter the appropriate data, eg:
CODE
ClassificationNo    ClassificationName
  456221              Assembler
  457822              Engineer


Then, in your other table, create a new field ClassificationNo.

Then, you can use a query like:
CODE
UPDATE YourTableName t
INNER JOIN tblClassification c
        ON t.ClassificationName = c.ClassificationName
  SET t.ClassificationNo = c.ClassificationNo;


If you wish you can then delete the classification name field from your original table.

Voilà!

hth,

d

[EDIT: George beat me to it, but I think suggests the same thing!]

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2019, 11:47 AM
Post#4


UA Admin
Posts: 34,584
Joined: 20-June 02
From: Newcastle, WA


Yup, just without the detail.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Feb 12 2019, 12:36 PM
Post#5



Posts: 2,178
Joined: 4-February 07
From: USA, Florida, Delray Beach


If the Class Codes ([ClassCode]) are Static and the number of Class Names ([ClassName]) are limited, I actually prefer a purely Code-based solution eliminating the Table and Update Query, as in:
CODE
Dim MyDb As DAO.Database
Dim rst As DAO.Recordset

Set MyDb = CurrentDb
Set rst = MyDb.OpenRecordset("tblClassifications", dbOpenDynaset)

With rst
  Do While Not rst.EOF
    .Edit
      Select Case ![ClassName]
        Case "Assembler"
          ![ClassCode] = 456221
        Case "Engineer", "Engineer Asst."
          ![ClassCode] = 457822
        Case "Fitter"
          ![ClassCode] = 123982
        Case "Mechanic"
          ![ClassCode] = 773812
        Case Else
          ![ClassCode] = 999999
        End Select
    .Update
    .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

P.S. - Not really sure if cheekybuddha and GroverParkGeorge would agree with this approach. shrug.gif
This post has been edited by ADezii: Feb 12 2019, 12:40 PM
Go to the top of the page
 
lamore
post Feb 14 2019, 08:41 AM
Post#6



Posts: 96
Joined: 25-August 14



cheekybuddha I did what you suggested and created another table with Classification names and Codes and ran 1 update query in my code and it worked great!!


Thanks for your help!

Lenny
Go to the top of the page
 
cheekybuddha
post Feb 20 2019, 12:52 PM
Post#7


UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill


I'm sure George and ADezii will join me in saying:

yw.gif

Pleased we could help.

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 10:19 AM