Update a field on a table

[Steve_T]

Hello all,
I am sure what i am asking is very simple but as i am a mear starter in the world of Access your patients and wisdome is required.
I have a table called "Master" with multiple fields but this question revolves around just two of them.
I have a field called "Vendor" and a field "Vendorkey" i need a querie to look at another table called "Vendor Information" which has the same field names. So when it see's the Vendor number in the "Master" table it returns the vendorkey assigned in the table called "vendor information".
I know this is basicaly a V-Lookup in Excel but i havent a clue how to do it in Access.
HELP!!

 

[georgedwilkinson]

It's called a join in Access. All data access through Access is done via queries...that's where you implement the join.

A typical query:

select * 
from Master inner join [Vendor Information] 
on Master.Vendorkey = [Vendor Information].Vendorkey
;

You can write query code simply by using the nice Access visual query writing tool.

By the way, naming objects with special characters and spaces is a no-no. You'll make your life easier by taking out the spaces.

 

[Steve_T]

Hi, thanks for help but i am looking for a way to update a table, your suggestion looks like a SQL.
Sorry if i am missing something

 

[ctechnumber1]

basically it seems like you are just trying to update information.
I am assuming you either have a LOT of entries to update or this needs to be a repeated process. ???

One way to solve this issue is to create two record sets from the tables of interest with only the desired fields.

what you can then do is loop through one table and for each entry traverse the other table looking for a match.

when you find a match you can then update appropriately

However, this will require some VBA coding. Are you familiar with using VBA code??

 

[Steve_T]

I have used it but only very simple stuff. I originaly exported the table to Excel and ran a V-Lookup., but when i imported the table back 1000's of data was missing and placed in an import error table. I have also tried to copy and paste as well. In regard to the size of the entries there are over 17,000. Also the Vendor number and Vendor key repeat on a weekly basis.

 

[ctechnumber1]

Ok, so this should point you in the correct direction

if you create a Button and view properties,
go to Event >> OnClick
and Enter "CODE"

Look through the following and paste it in:
I warn you that I am less than 2 weeks into access and VBA
but I am sure something like this can help you

you may not be able to get it all at once and I may have screwed something up, just let me know

First things first:
-Table names with spaces are bad. but if you notice the brackets it is to by pass that problem ex. [Vendor Information].TableFieldName
-There are many modification we can make to the below code if necessary.
-the value in the Vendor fields has to be exactly the same to make a match
-Most important, this may solve your immediate problem; however, there may be an easier way of doing things give the entire scope of your problem
-Let me know how this goes

**If you are unable to get this, then send me a copy of the database you are working with.


Private Sub RunButton_Click()

'I have taken the naming convention from the info
'you have already provided
'if it is acutally different then it may be best to change
'some of this stuff

'Set up everything to create a RecordSet(Data Collection)
Dim objDB As DAO.Database
Set objDB = CurrentDb
Dim rsMaster As DAO.Recordset
Dim rsMasterField As DAO.Field
Dim rsVendor As DAO.Recordset
Dim rsVendorField As DAO.Field

Dim strMasterSQl As String
Dim strVendorSQL As String

'Create the queries to gather table data
strMasterSQl = "SELECT Master.Vendor FROM Master;"
strVendorSQL = "SELECT [Vendor Information].Vendor FROM [Vendor Information];"

'Set the Collections with data from the tables
Set rsMaster = objDB.OpenRecordset(strMasterSQl)
Set rsVendor = objDB.OpenRecordset(strVendorSQL)

'Only run if values exist in tables
If (rsMaster.RecordCount <> 0 And rsVendor.RecordCount <> 0) Then

'Housekeeping
rsMaster.MoveFirst
'So I am also new to Access, 1.5 weeks, and this is the only way
'that I know to do this but it should work
Do While Not rsMaster.EOF
Dim Name As String
For Each rsMasterField In rsMaster.Fields
Name = rsMasterField.Value
rsVendor.MoveFirst
Do While Not rsVendor.EOF
For Each rsVendorField In rsVendor.Fields
If Name = rsVendorField.Value Then

'this is true if vendorkey is an integer
'let me know asap if not case
Dim Value As Integer
' "& Name &" is to be able to insert varialbes
Value = DLookup("Vendorkey", "Master", "Vendor =" & Name & "")
DoCmd.RunSQL ("UPDATE [Vendor Information]SET Vendorkey = " & Value & " WHERE Vendor = Name;")

End If
Next
'watch inf loop
rsVendor.MoveNext
Loop



'loop
Next



'DONT FORGET OR INF LOOP STARTS
rsMaster.MoveNext
'Loop back to rsMaster
Loop
End If


End Sub

 

[ctechnumber1]

May be a good idea to create a back up or do this on a copy of the database