Using "Like" in query relationship

[szag via AccessMonster.com]

Here is my problem:

I have a "Parts" table with a "Part#" field and am trying to create a
relationship to the "Orders_Shipped" table and the respective "Part#" field
in that table. It works fine except for the "Part#" field in the
"Orders_Shipped" table has extensions on it sometimes but I still want them
related.
For example:
In my query I want to be able to relate Part# "123" in the Parts table to
Part# "123A" in the "Orders_Shipped" report. Is it possible to create
something using "Like"? Any other way?

 

[Jeff Boyce]

If your "123A" means two things, not just one, then you are finding why it's
a good idea to store only one fact in one field. The ideal solution would
be to modify the data structure to include two fields, one with "123" and
one with "A".

If you have to work with what you have, consider first creating a query that
"strips" off the extra character(s) from "123A", and returns "123" and all
the other relevant fields. Then build YOUR query using that first query to
join to your table that already has a "123" field.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Then for display or reporting use this --
Part_Ordered: [Part#] & [A_Field]
A_Field being the field with the suffix information.
--
KARL DEWEY
Build a little - Test a little

If your "123A" means two things, not just one, then you are finding why it's
a good idea to store only one fact in one field. The ideal solution would
be to modify the data structure to include two fields, one with "123" and
one with "A".

If you have to work with what you have, consider first creating a query that
"strips" off the extra character(s) from "123A", and returns "123" and all
the other relevant fields. Then build YOUR query using that first query to
join to your table that already has a "123" field.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Marshall Barton]

Here is my problem:

I have a "Parts" table with a "Part#" field and am trying to create a
relationship to the "Orders_Shipped" table and the respective "Part#" field
in that table. It works fine except for the "Part#" field in the
"Orders_Shipped" table has extensions on it sometimes but I still want them
related.
For example:
In my query I want to be able to relate Part# "123" in the Parts table to
Part# "123A" in the "Orders_Shipped" report. Is it possible to create
something using "Like"? Any other way?

Not to counter the good advice from others, but to answer
the question as asked, yes, it is possible, but not in the
Query design window.

Switch your query to SQL view and add another condition to
the ON clause:

ON . . .
And Orders_Shipped.[Part#] Like Parts.[Part#] & "*"