Select records Crystal Reports with one field that matches many fields

Using Crystal Reports 2013 SP6.

Table 1,
|Short Code field| and other fields that I need for the report
|IPL                        |
|ICL                        |
|GLSS                     |

Table 2
|Inv Code field|GL Code|COS Code |
|IPL                   |ICL          |GLSS          |    

I need to report data from the fields from Table 1 with matches to fields in Table 2. There is no other link.
What is the best way to tackle this report?
Who is Participating?

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

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.

Try this as the selection

{Table1.short code field} = {table2.Inv Code field}
{Table1.short code field} = {table2.GL Code}
{Table1.short code field} = {table2.COS Code}

Open in new window

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
mlmcc answer would solve the issue if all you need is the selection logic.  But if you need other columns from Table 2 you need an actual join.
One solution is to create a 'UNION' VIEW from Table 2 with one SELECT for each of the 3 codes (in each SELECT, a different code column is selected as a NewCODE column. Then, join Table 1 to the VIEW using Table1.Short Code field = UnionView.NewCODE

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
trilianAuthor Commented:
How do you create a view in crystal reports?
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
VIEWs are created in the DBMS.
In Crystal, you would create a COMMAND.
When you specify the database, there is an option (on some not all databases) to use a COMMAND.  YOu then write the SQL required to get the fields you want.

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
Crystal Reports

From novice to tech pro — start learning today.