Update query take spaces out of entry, compare to table, populate different column

[Demosthenes&Locke]

I have one table with missing data, but it's the most recent version of what we're working with. It has a list of equipment numbers broken down by area and type, etc., to be easier to read by us. XXXX XX XXXX XX is almost the way it is, but sometimes the last number can vary in length. I know how to make a query update the table, I know how to make the query search other table, but I don't know how to make the query take the current value in our table, search the other table and taking the entry on this table in a way that ignores the spaces in it to compare it to the entry on the other table, then retrieve the value from another column on the other table, and populate this table with the stuff retrieved from the other column if there's not currently anything in this column.

This type of action seems so much easier in an Excel vba macro.

If anyone can point me in the right direction for this, even with just a made up table name, 2 made up field names, and clear notation on how to put this into a query (an insanely egregiously commented sql query would work in this instance), I would really appreciate it!

[PianoMan64]

Can you provide some examples of what is in each table based on your description. I'm having a hard time following what is coming from where.

Thanks,

Joe P.

[Demosthenes&Locke]

Table 1:
ID             Location       TechID         Etc...
1              FJC13D1        10331          Other stuff/columns
2 etc...
 
 
 
Table 2:
ID             Location       TechID         Other different etc....
1              FJC??          10 33 1        Other stuff/columns
2              FJ             11 32 3        Other stuff/columns
3                             31 32 6        Other stuff/columns

Table 1 contains complete entries, table 2 contains some entries, but mostly partial or missing entries, so I will need to be able to handle a null and keep going for either the id or location columns(in either table). And I need it to take the "techid" from table 2 and pull the entry into a variable, remove the spaces from the variable, then search the other table for a corresponding entry, then entering the information into the correct spot on table 2.

I know parts of what I need to know concerning update queries and such, but with queries I have never had it interpret the data before it does what it does with other tables and I don't know how to make what I need. I know how to do this with an excel macro, but I need to be able to do it in Access.

[PianoMan64]

Table 1:
ID             Location       TechID         Etc...
1              FJC13D1        10331          Other stuff/columns
2 etc...
 
 
 
Table 2:
ID             Location       TechID         Other different etc....
1              FJC??          10 33 1        Other stuff/columns
2              FJ             11 32 3        Other stuff/columns
3                             31 32 6        Other stuff/columns

Table 1 contains complete entries, table 2 contains some entries, but mostly partial or missing entries, so I will need to be able to handle a null and keep going for either the id or location columns(in either table). And I need it to take the "techid" from table 2 and pull the entry into a variable, remove the spaces from the variable, then search the other table for a corresponding entry, then entering the information into the correct spot on table 2.

I know parts of what I need to know concerning update queries and such, but with queries I have never had it interpret the data before it does what it does with other tables and I don't know how to make what I need. I know how to do this with an excel macro, but I need to be able to do it in Access.

From the looks of it, you're going to have to write a VBA code to do what it is that you do in excel, the difference here is that you'll be doing it against an update query.

Since I'm still not following how the two tables are related to each other, I still need to get more detail as to how they work together, and what it is that you're exactly trying to update.

Hope this helps,

Joe P.