Field Relationships

[Guest]

Hello and thank you in advance to anyone who may assist me.
This will be the first of a few posts I will be making.

I am trying to query data between 4 tables (Line, Load, Fuse, Recloser).

The fields I'm attempting to join are:
Line!STR_ID
Load!NAME
Fuse!STR_ID
Recloser!STR_ID

My problem is that no exact unique identifier field exists between the
tables in these fields. But there is a five-digit circuit number buried
within each these fields that match up. i.e. STR_ID= "Z12345-7788996" the
circuit number is always before the hyphen so in this case it is "12345".

The proceeding numbers vary within the fields along with the leading
alphabetic character. Is there any possible way to join these tables so I
may extract data?

Or...
The only way I have thought possible so far is to make another field within
each table. By doing this I'd copy and paste each field listed above into
Excel and find a way to grab the circuit number out of the data. I know
there is a way to do this as well but I can't remember.

Please help ASAP!

Thanks.

 

[Douglas J Steele]

Are you saying that it could be Z12345-7788996 in one table and
X12345-1234567 in another table?

If not, then why can't you join on the entire string?

Another possibility would be to write a function that parses the string and
returns the 5 digit circuit number. Something like the following should do
it:

Function GetCircuitNumber(StringID As String) As String

Dim intDash As Integer

intDash = InStr(StringID, "-")
If intDash > 5 Then
GetCircuitNumber = Mid$(StringID, intDash - 5, 5)
End If

End Function

 

[Steve Schapel]

Nadia,

You can use this expression to extract the circuit number...
Circuit: Mid([STR_ID],InStr([STR_ID],"-")-5,5)

Or, if there is always just one leading character...
Circuit: Mid([STR_ID],2,5)

You could make a calculated field within a query based on each of your
existing tables, and then use these queries as the basis of your main
queries (hope that makes sense). Or you could add a new field to each
existing table, and use the above expression in an Update Query to
insert the circuit number into the new fields.

 

[Guest]

Yes in one table it may appear Z12345-77855697 in another Z12345K-1566874 in
another ZX12345-88569994

Will your suggestion still work?

 

[Steve Schapel]

Nadia,

I see in your reply to Doug that the existing data can sometimes have
more than one leading character. In that case, you would need to use
the first expression I suggested. This is essentially the same concept
as what Doug is suggesting.

 

[Guest]

Thank you for following up on that. I'll give it a try soon.
--
Nadia

Nadia,

I see in your reply to Doug that the existing data can sometimes have
more than one leading character. In that case, you would need to use
the first expression I suggested. This is essentially the same concept
as what Doug is suggesting.

--
Steve Schapel, Microsoft Access MVP

Nadia,

You can use this expression to extract the circuit number...
Circuit: Mid([STR_ID],InStr([STR_ID],"-")-5,5)

Or, if there is always just one leading character...
Circuit: Mid([STR_ID],2,5)

You could make a calculated field within a query based on each of your
existing tables, and then use these queries as the basis of your main
queries (hope that makes sense). Or you could add a new field to each
existing table, and use the above expression in an Update Query to
insert the circuit number into the new fields.