Help with joining tables in MS Access

Hello everyone,

I'm joining two linked excel tables in an access query. The fields are work order numbers. However, on one table the work order number is the main, top level leg only (W123456/1). On the other table there are work order numbers just like that but there are also work order numbers with leg numbers as well, (W123456-2/1). I want my join to include the legs in my query however, I only know how to do an exact match and not a close match in my query design. I need to match the first 7 charactors of the field. Can anyone help me do this?

Thank you,
Paul

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

ztrue

You want to lookup string functions inside of Access. They should allow you to dynamically pull everything to the left of the "/" character.

www.techonthenet.com/.../

unknown

Thanks Zach. I checked the link but being the novice I am can you be a little more specific? I tied to use a couple of examples but didn't get the result I need.

ztrue

You want to use a combination of INSTR and LEFT.

Basically, think of the functions as ways to filter your string (as in a string of characters).

The LEFT function tells access that you want to only look at the first N letters starting on the left. It takes two arguments: your string and a number indicating home many characters to return.
Left("abcdefg", 4) should return abcd
Left ("abcdefg", 6) returns abcdef

INSTR (in string) returns the digit number of a given string that you are searching for inside of another string. It takes three arguments: two strings and a number. THe number is what character to start counting from, the two strings are what you are searching for and what you are searching through.
Instr(1,"abcdefg", "c") should return 3
Instr(1,"abcdefg", "g") should return 7
Instr(3,"abcdefg", "g") should return 4

As I really believe in learning by doing, the last crumb that I will give you is that you can nest functions inside of one another (like Russian Nesting Dolls).

[Updated on 1/27/2015 4:51 PM]

unknown

Thanks Zach. That's good information but doesn't solve my issue.

ztrue

Then I do not understand your issue.

My understanding is that you want to know how to compare two text fields. The whole of the first and only the first 7 characters of the second. If this is correct, then the above is everything you need to not only do it, but to do it in such a way as to future-proof your query (once you get to an 8+ character WO Base ID).

If this is incorrect, please restate the question.

unknown

Thank Zach. Your statement is somewhat correct. That is what I am comparing. The first 7 digits of the 2 tables but the result is only the first 7 digits. I need it to report out the whole number including everything after the first 7 digits that match. Does that help?

ztrue

In access queries, the line between selection and criteria is pretty blurry. SQL and Oracle queries make this much more directly understandable.

The previous posts were about the criteria statements. Assuming you understand how to apply those functions, you just have to mark the criteria selection as not visible (the Show row, see attached pic) and then make sure to add in the actual fields you want to see.

unknown

The original question sounds to me like a question about joins. If one spreadsheet contains all the work orders then you should use that as your starting point, and join it to the other spreadsheet using a left outer join. As opposed to an equal join that looks for an exact match. The left outer join will return everything from the first table regardless of the second table.
I am assuming here that Access works like SQL and the spreadsheets will behave like tables.

Copyright © 2025 Infor. All rights reserved.