Two fields in a table referring to the same field in a lookup table

[lmcc007]

I have Two fields in a table referring to the same field in a lookup table. They are:

CompanyName -- tlkpCompanyNames
CrossRefName – tlkpCompanyNames_1

​

In the Relationships window, I did the following:

1. joined CompanyName to tlkpCompanyNames

2. copied tlkpCompanyNames, now I have tlkpCompanyNames_1

3. joined CrossRefName to tlkpCompanyNames_1.
​

But when I create a query using the two tables—tblCompanies and tlkpCompanyNames, both of the fields from the tblCompanies table are joined to tlkpCompanyNames. When I click the View button no records show. Then I delete the joint line that joins CrossRefName to tlkpCompanyNames then my records are displayed.

Basically, all I have done is create the tables and set up relationships. Now I am trying to create a query.

Did I do something wrong?

 

[marianne]

can you attached your db here and save it as version 2000.

 

[lmcc007]

can you attached your db here and save it as version 2000.

here it is.

 

[marianne]

maybe your error is in the ROW SOURCE of CompanyNameXRefID of your tblCompanies_main table. they do not match. look on that one.

 

[lmcc007]

Deleted the row source (lookup info) from the table, but it still did it. Anyway, I was just trying to figure out why. No biggie.

 

[marianne]

well, when you relate the two fields, it must have records because your query will evaluate if it has the same record. if not, eventually no record will be shown.

your companynameid is a number field with number records, your CompanyNameXRefID is a number field with some no record and some text records. that is why no records are shown.

 

[lmcc007]

well, when you relate the two fields, it must have records because your query will evaluate if it has the same record. if not, eventually no record will be shown.

your companynameid is a number field with number records, your CompanyNameXRefID is a number field with some no record and some text records. that is why no records are shown.

Okay, I'm going to let this go b/c I am getting confused. It's not important, I was just curious why.