Loop code to lookup and change records

[michaeljryan78]

Okay, I am a noob and I setup my table wrong and now I am trying to get the structure and data normalized. Here is the problem:

I have a table [counsel originating] that has 4 fileds: ID (PK), First, last, and Office(Lookup to [OfficeLookup] table. I have another table [counseldetails] that uses a query to lookup the full name based upon the offce defined and then selects the full name. What I want is to have the relationship directly tied from [cousel originating] to[counseldetails] and eliminate the query. the problem is when I try to create the lookup, it is marking the field as a number and I will lose the data becuase now it is set to text.

My solution is trying to find a loop code that will change the value of [counseldetails].[requestername] to the numeric value that is in the [counsel originating] table based upon the ID and the text value stored in the table. I can use 150 update queries to do so, but I am trying to find a bit more automated process to speed it up.

Thanks! I just want everything Normal!!!

 

[jdraw]

What is the structure of CounselDetails? What is the link field between Counsel Originating and CounselDetails?

Get rid of the spaces in your names and objects. It will reduce your frustration over time.

 

[michaeljryan78]

Counseldetails has an ID(PK) and then has a field [couseldetails].[office originating] one to many to [office origination lookup].[ID] [Office Origination Lookup] has ID and [office]. Cousel Originating has a one to many relationship to [office ogination] again on ID. (one[cousel originating.offce] to many [office origination.ID]).

Cousel details has the field that i am ultimately trying to relate which is [counseldetails].[requester name] to [cousel originating].ID

I know...I have been hating the names, but I am try to correct a database that was built when I wasn't so concerend and now I am think about migrating, so I am trying to correct what I can befire I do!!! Its on the to-do list.

 

[michaeljryan78]

Figured it out. I created an update query and joined the query that housed the data that needed to be update orgcouselfullname which had the ID and joined with counseldetails.name. ran the update and BAM it worked for all records, a real timesaver!