Table Join 1

[KirillToronto]

Hellow gurus, I have a question about a join that I use in one of my reports. Please suggest alternative solution if possible. Here is what I have
Table 1 (left table) is an inventory master file and table 2 is the GL master file. All I need to do is link those tables in such a way that when I have part AA101 listed in detail I get a TEXT value of a description from table 2.

Table 1 (left table) Table 2 (right table)
Part # AA101 GL # Description
ExpenseGL 1000 1000 Text 1
ClearingGL 1001 1001 Text 2
APGL 1002 1002 Text 3
ARGL 1003 1003 Text 4
Return GL 1004 1004 Text 5
Tax1GL 1005 1005 Text 6
ItemDescription Hydro Valve Currency CAD

What I did so far is I brought in the same table (Table 2) 6 times and linked it with a left outer join, First link joins ExpenseGL from table 1 to a GL # in Table 2. 2nd link joins ClearingGL from table 1 to a GL # in table 2, etc for every acount.

My way works, but it is ugly and very time consuming, is there a better way to solve this.

Thanks for your help.

 

[lbass]

I don't understand what the fields are for Table 1--are each of the TextGL's listed separate fields or instances of one field? If separate fields, then are the numbers the content of the field? If so, then I think you have to do exactly what you did.

-LB

 

[KirillToronto]

Yes, they are separate fields.
I was wondering though if there is a neater wey of doing it.

 

[KirillToronto]

thanks for your comment