Linking problem

[Imbriani]

Hi guys,

I have a question regarding linking tables together. I've run out of my expertise for this, so I'm hoping someone here has a solution (as you always do - almost).

I have a table (LabNumber) with a primary key (OldLabNumber) and a field (CompanyID). I want to link that to a table (Reports) with no primary key, but with an autonumber field (ReportID) and a number field (CompanyID), linking on the CompanyID fields. Try as I might, I can't get this to work. I have tried redefining the Reports table with the CompanyID field as a primary key (that didn't work) and defining the ReportID field as a primary key (that didn't work either).

The need here is to link one more table beyond this (a company data look up table), also with a CompanyID field for use on a Crystal report. The third and last table, and the Reports table are serving as sort of look up tables. Am I overlooking something simple here?

 

[rac2]

...can't get this to work. ...

Do you receive an error message when you save the query?
Does the query yield no rows?
Or too many rows?
Or the wrong rows?

There are so many ways things dont work.

What are the data types for company id in the two tables?

 

[Imbriani]

I forgot to mention that these are MSAccess tables in an access database.

The message I get when I try to create the link is: "No unique index found for the reference primary table" The primary table has a unique index, but not the field I am trying to join the second table to. Is this join an impossibility??

 

[rac2]

I created two tables and a query joining them like this.
Tables
LabNumber
OldLabNumber Number Integer not required not indexed
CompanyID Number Integer not required not indexed
this table has no key column.

Reports
ReportID AutoNumber
CompanyID Number Integer not required not indexed
this table has no key column.

Data
LabNumber
OldLabNumber CompanyID
1701 791
1702 792
1703 793
1705 792

Reports
ReportID CompanyID
1 792
2 793
3 794

Query
SELECT LabNumber.OldLabNumber,
LabNumber.CompanyID,
Reports.CompanyID,
Reports.ReportID
FROM LabNumber
INNER JOIN Reports ON LabNumber.CompanyID = Reports.CompanyID;


I filled in some rows 4 in LabNumber, 3 in Reports with two rows having CompanyID in common in both tables. The LabNumber table also has two rows with the same company id but different OldLabNumbers.

The query was saved without any warning and yields 3 rows as expected.

LabReportsView
OldLabNumber LabNumber.CompanyID Reports.CompanyID ReportID
1705 792 792 1
1702 792 792 1
1703 793 793 2

So there must be something else going on that is producing the error you reported.

 

[Imbriani]

Could be. This computer is on a state government system and the Db is on a server. Sometimes weirdness happens. I'll work on it a little more and see what happens. Thanks for your help.

Kathy

 

[LARiot]

Don't you have to make the join a one to many relationship first?

 

[lespaul]

Joins are different from relationships. Access allows you to create relationships. The query builder then uses these relationships to insert the correct joins into the query. You can join on any field, not just fields that have relationships associated with them.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[LARiot]

I used join and relationship not Join and Relationship.

My point is, if I understand the situation properly, is that the "link" needs to be one to many.