Join Tables

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I have a table name tech_id contains field CORP,Contractor. There is
another table name LastValidTech contains four fields, Account1,
Account2, Account3, Account4. Table structure is first 4 digits of
LastValidTech (Account1,Account2,Account3,Account4) is CORP.

tech_id
CORP,CONTRACTOR
1211,ABC
1222,EFG
1333,KKL

LastValidTech
TicketNo, Account1, Account2, Account3, Account4
1122,01211-12345-01
1123, ,01222-32456-93
1124, 01333-73737-04

Some times the Account1 has a value, Some times Account2 in another
record has a value and same some times Account3 and Account4 has a
value. How to i join both tables in a sense that if Account1 has a
value and match with first 4 digits of CORP then it will connect to
CORP or if Account2 has a value and match with first 4 digit with CORP
it will connect to CORP or if Account3 and Account4 has a value and
match with first 4 digit with CORP then it will connect to CORP.

Thanks.
 
I have a table name tech_id contains field CORP,Contractor. There is
another table name LastValidTech contains four fields, Account1,
Account2, Account3, Account4. Table structure is first 4 digits of
LastValidTech (Account1,Account2,Account3,Account4) is CORP.

tech_id
CORP,CONTRACTOR
1211,ABC
1222,EFG
1333,KKL

LastValidTech
TicketNo, Account1, Account2, Account3, Account4
1122,01211-12345-01
1123, ,01222-32456-93
1124, 01333-73737-04

Some times the Account1 has a value, Some times Account2 in another
record has a value and same some times Account3 and Account4 has a
value. How to i join both tables in a sense that if Account1 has a
value and match with first 4 digits of CORP then it will connect to
CORP or if Account2 has a value and match with first 4 digit with CORP
it will connect to CORP or if Account3 and Account4 has a value and
match with first 4 digit with CORP then it will connect to CORP.
Click to expand...

Was there a question in there, Eric?

Your LastValidTech table is incorrectly designed in two ways: the
AccountX fields are repeating fields - they should be in separate
*records*, not separate fields; and the fields are not "atomic", since
the first four (five, it looks like!) characters have their own
independent existance as links to Corp.

It may be possible to create a Join looking up Contractor for each of
the four accounts; you'll need to add the Tech_ID table to the query
*four times*, with no join line at all on any of the instances. On the
first instance of tech_id use a criterion on [CORP] of

Mid(NZ([Account1], "00000"), 2, 4)

assuming that CORP is a Text field; use

Val(Left(NZ([Account1],"00000"), 5)

if it's a number datatype.

Do the same for the other three instances using Account2, Account3 and
Account4 instead of Account1.

The resulting query will not be updateable and may be quite slow. This
also assumes that 0000 is never a valid CORP.

John W. Vinson[MVP]
 
Back
Top