Partial Key within Access

[Guest]

I have two table. One with an ID# of, for example, "00028". I want to join
this to another table with the ID# "BS-00028-000A" obviously just using
positions 4 thru 8 on the second indicated table.

How can I do this within Access?

Any help is GREATLY appreciated and I appreciate your respons.

Thanks

wnfisba

 

[Guest]

Create a query to extract that part of the field then you can join the table
and query in a query.

 

[Allen Browne]

1. Create a query that joins the 2 tables as if you were joining on the
whole field.

2. Switch the query to SQL View (View menu in query design), and use Mid()
in the JOIN clause.

For example, change:
ON [Table1].[ID#] = [Table2.[ID#]
to:
ON [Table1].[ID#] = Mid([Table2.[ID#],4,5)
You will not be able to switch the query back to design view, but you can
run the query.

An alternative approach would be to create the query with no join between
the tables. Then type this into the Field row in query design:
Mid([Table2.[ID#],4,5)
and in the Criteria row under this field, enter:
[Table1].[ID#]

 

[Roger Carlson]

As long as your positions are always 4-8, Allen's solution is easiest.
However, you can generalize it a bit more and look for any string between
the first and second hyphens like this (using Allen's third example):

SELECT [Table1].[ID#], [Table2].[ID#]
FROM Table1, Table2
WHERE [Table1].[ID#] =
Mid([Table2].[ID#],InStr([Table2].[ID#],"-")+1,InStr((InStr([Table2].[ID#],"-")+1),[Table2].[ID#],"-")-1-InStr([Table2].[ID#],"-"));

Where this:
InStr([Table2].[ID#],"-")+1
finds the position of the first hyphen,

and this:
InStr((InStr([Table2].[ID#],"-")+1),[Table2].[ID#],"-")-1-InStr([Table2].[ID#],"-")finds the length.----Roger Carlson MS Access MVP www.rogersaccesslibrary.com"wnfisba" <[email protected]> wrote in messagehave two table. One with an ID# of, for example, "00028". I want to join> this to another table with the ID# "BS-00028-000A" obviously just using> positions 4 thru 8 on the second indicated table.>> How can I do this within Access?>> Any help is GREATLY appreciated and I appreciate your respons.>> Thanks>> wnfisba

 

[Roger Carlson]

I suppose I should also mention that this could be very slow on a large
dataset. It might be worth while to break the field in Table 2 into three
separate fields and just concatenate them when you need the whole number.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Joseph Meehan]

I have two table. One with an ID# of, for example, "00028". I want to
join this to another table with the ID# "BS-00028-000A" obviously
just using positions 4 thru 8 on the second indicated table.

How can I do this within Access?

Any help is GREATLY appreciated and I appreciate your respons.

Thanks

wnfisba

You should consider breaking up that information on the second table as
it appears to violate the good design by storing more than one kind of data
in a single field.