Query Help

[gibsonsgman]

Hello,
This may be a very easy thing to accomplish, but I am at a loss. I
have two quite large tables that I imported from two separate sources.
Both have a field named 'Equipment_ID' and I want to join these two
fields and take fields from both tables to form a new table. However,
my problem comes in here because in one table, the field 'Equipment_ID'
contains values such as 'CV-1014' where as the other table will contain
values such as ' CV-1014 VALVE' Is there anyway i can join these two
fields and have the query display these two as a match? Again there
may be an easy solution, but I am at a loss right now.

THanks for any help you can give.

 

[KARL DEWEY]

If your table2 is always the same as table1 but with additional information
in the field the try putting both tables in the query design view and do not
join. Use criteria for table2 like this --
Like [Table1].[YourField] & "*"

 

[John Vinson]

Hello,
This may be a very easy thing to accomplish, but I am at a loss. I
have two quite large tables that I imported from two separate sources.
Both have a field named 'Equipment_ID' and I want to join these two
fields and take fields from both tables to form a new table. However,
my problem comes in here because in one table, the field 'Equipment_ID'
contains values such as 'CV-1014' where as the other table will contain
values such as ' CV-1014 VALVE' Is there anyway i can join these two
fields and have the query display these two as a match? Again there
may be an easy solution, but I am at a loss right now.

THanks for any help you can give.

Try creating a query in the grid, joining by Equipment_ID; open the
query in SQL view and change the portion of the query like

INNER JOIN table2 ON Table2.EquipmentID = Table1.EquipmentID

to something like

INNER JOIN table2 ON table2.EquipmentID LIKE Table1.EquipmentID & "*"

to "wildcard" the join.


Alternatively, and perhaps better: open the second table in design
view and add a new field ItemType. If you can *reliably* count on all
the equipment ID's being in the format above - a valid ID, a space,
and then text indicating the Item Type - run an Update query updating
ItemType to

Trim(Mid([EquipmentID], " "))

and EquipmentID to

Left([EquipmentID], " ") - 1)

This will move the offending mismatch data into another field and
allow you do do a direct join on the ID.

John W. Vinson[MVP]