Matching data from different columns

[Kristibaer]

Hi:

I have a query using 2 tables. One table has a field containing an item
number and the other table has a field containing the item number and test in
the same record. I need to find a way for the query to match the like data
and ignore the extra. Here is an example of what the data looks like

Table 1 field: FGUI125AT
Table 2 field: FGUI125AT Inventory Adjust - Change

What function will make the query recognize these as being alike?

Thank you,
Kristi

 

[Jerry Whittle]

If there is alway a space after the item number and that is the first space
in the string, something like below will extract just the item number in a
query once you put in the correct field name.

Left([ItemTestField],Instr([ItemTestField], " ")-1)

Still you have to do a join with it and that can be somewhat tricky.

 

[Kristibaer]

Yes there is always a space after the number. What I am planning to so is
split this into 2 steps, so initally all I need to do is eliminate the text
after the number in a separate column. The field name from able 2 is
"descrip". Would this then be the correct command in a new column:

Item2: =Left([descrip],Instr([descrip], " ")-1)

Thanks again!

If there is alway a space after the item number and that is the first space
in the string, something like below will extract just the item number in a
query once you put in the correct field name.

Left([ItemTestField],Instr([ItemTestField], " ")-1)

Still you have to do a join with it and that can be somewhat tricky.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi:

I have a query using 2 tables. One table has a field containing an item
number and the other table has a field containing the item number and test in
the same record. I need to find a way for the query to match the like data
and ignore the extra. Here is an example of what the data looks like

Table 1 field: FGUI125AT
Table 2 field: FGUI125AT Inventory Adjust - Change

What function will make the query recognize these as being alike?

Thank you,
Kristi

 

[John Spencer MVP]

You could use a query that looked like the following. YOU can only do this
type of join in the SQL view, the query design view does NOT support this.

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table2.ItemNumber Like Table1.ItemNumber & "*"

Easy way to do this is to start in design view
-- Add both tables
-- Add all the fields
-- Make a join betwen the two item number fields
-- Select View: SQL from the menu
-- Find the On Clause and change the = to Like and add & "*" after the
itemnumber field that is shortest. That field must be on the right side of
the like and the longer field must be on the left.

Another way to handle this is to skip the join completely and in the criteria
under the Table2.ItemNumber enter
Like [Table1].[ItemNumber] & "*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Kristibaer]

Actually, that did the trick exactly, thank you!

happy Easter

 

[Jerry Whittle]

Below will extract data after the first space.

TheTest: Mid([descrip],Instr([descrip], " ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Yes there is always a space after the number. What I am planning to so is
split this into 2 steps, so initally all I need to do is eliminate the text
after the number in a separate column. The field name from able 2 is
"descrip". Would this then be the correct command in a new column:

Item2: =Left([descrip],Instr([descrip], " ")-1)

Thanks again!

If there is alway a space after the item number and that is the first space
in the string, something like below will extract just the item number in a
query once you put in the correct field name.

Left([ItemTestField],Instr([ItemTestField], " ")-1)

Still you have to do a join with it and that can be somewhat tricky.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi:

I have a query using 2 tables. One table has a field containing an item
number and the other table has a field containing the item number and test in
the same record. I need to find a way for the query to match the like data
and ignore the extra. Here is an example of what the data looks like

Table 1 field: FGUI125AT
Table 2 field: FGUI125AT Inventory Adjust - Change

What function will make the query recognize these as being alike?

Thank you,
Kristi

 

[John W. Vinson]

Hi:

I have a query using 2 tables. One table has a field containing an item
number and the other table has a field containing the item number and test in
the same record. I need to find a way for the query to match the like data
and ignore the extra. Here is an example of what the data looks like

Table 1 field: FGUI125AT
Table 2 field: FGUI125AT Inventory Adjust - Change

What function will make the query recognize these as being alike?

Thank you,
Kristi

The query LIKE operator:

SELECT [Table 1].*, [Table 2].*
FROM [Table 1] INNER JOIN [Table 2]
ON [Table 2].field LIKE [Table 1].field & "*"

Obviously, this is only a problem because Table 2 violates the basic principle
that fields should be "atomic" (storing only one value); the field in Table 2
contains two different values. Any chance of changing the structure of Table 2
so that these are two fields not one?