need help with access query

I have two tables:
one table is a system table, huge, let say customer database, let's call  SystemInfo
and another one is small, let's call it Input

the structure of SystemInfo:
Rec#
FName
Lname
code1
code2
code3
the structure of Input
Fullname
phone

i need to connect two tables thru Phone field.
1&code1&code2&code3 from SystemInfo and phone from Input
Roman FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Maybe the following:
SELECT *
FROM SystemInfo, Input
WHERE (SystemInfo.code1 & SystemInfo.code2 & SystemInfo.code3) = Input.phone;

Open in new window

0
Roman FAuthor Commented:
thank you, it takes soooooooooo much time to run!!! so the tables not linked by fields, i mean there is no inner join between tables?
0
PatHartmanCommented:
Modify the small table to break the phone into three fields.  Then you can use a join rather than a Cartesian Product.  You might need to create an index on the three fields in each table to speed up the join even more.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use an INNER JOIN but it won't be because of that, that will run faster:
SELECT *
FROM SystemInfo
    INNER JOIN Input ON (SystemInfo.code1 & SystemInfo.code2 & SystemInfo.code3) = Input.phone;

Open in new window

The problem is the engine is loosing time concatenating 3 fields and then comparing it one by one with the Phone field. And it will do it for each of the records from the SystemInfo table that as you said, is a very big table.
0
PatHartmanCommented:
That is why I said to actually MODIFY the small table to separate the columns and surely an index on the large table would help the query engine to optimize the join.
0
John TsioumprisSoftware & Systems EngineerCommented:
You can always concatenate code1,code2,code3 and create a temp table and then join this temp table with the input table...the time of creating the temp table and then joining should be faster...
0
PatHartmanCommented:
Why would you modify the large table rather than the small one?
0
Roman FAuthor Commented:
thank you all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.