How to do DLOOKUP in Field (1 Viewer)

I have two tables. The main table is named "Master Table" and the table I want to pull information from is called "RefData".

I want to read the field [Master Table].[Company ID] if it isn't blank and populate the field [Master Table].[Company] by reading from my RefData table. My RefData table is simply Company ID | Company (Just those two fields). This is relatively simple I know but I wasn't sure the best and most efficient way to do this (or at all how to do it to be honest).

Any tips/help is appreciated, thanks so much!!

M

You should be able to join the two tables on the [Company ID] field and then both [Company] fields can be in the same recordset. Does that help?

RuralGuy said:

You should be able to join the two tables on the [Company ID] field and then both [Company] fields can be in the same recordset. Does that help?

Click to expand...

I don't think that's what I'm going for, I'll try to elaborate.

I have my master table which has a bunch of information about our sales. It's got a full list of client IDs, prices, quantities, salesperson's name, date, etc. It has one field though that's COMPANY ID (who we sold to). I want to use a dlookup or something similar to populate that field (not add it in, populate it) by using the lookup to another table I have saved that has a list of all COMPANY IDs and COMPANY NAMES. Let me know if you understood completely and if I can answer any more questions. I always thought this would be easiest through a DLOOKUP or an expression builder but I can't seem to find an expression in Design View that would fit what I need.

E: Is this just an update query? I think that would solve it.

I'm also having a big problem with my table (on a separate note) and if you could help me brainstorm that would be amazing.

I have a unique ID field and it's kind of a s***show.... for lack of a better term.
It contains a 4-6 unique ID followed by a space and then another ID that is irrelevant.
How can I pull out this 4-6 number/letter ID to look it up in another table and find a related record?

It is rarely a good idea to have atomic data in more than one table. Usually a reference to the table with the atomic data is adequate. There are exceptions, but I don't think this is one of them. As for your second question, the string functions of Left or Mid should help here.