Relationships

[AccessHawaii]

I'm creating a database that's a little different in the fact that I can't define one single field as a primary key, I have to assign several.

I have 3 tables, 1 is used as a primary table and has a primary key set to to fields (Field1 and Field2), Another one is also used as a primary table and has a primary key set to (Field3 and Field4), and my third table is the relational table, which is linked to the other 2 tables and has primary fields for the combination of (Field1,Field2,Field3, and Field4).

The problem is when I try to build a query off this relationship, I'm returning more records than I actually have. It's duplicating some records and leaving some out all together.

What I want to do is return all the records from the relational table and show elements from the primary table. Does anyone know how to do this?

 

[lespaul]

What does your query look like?


Leslie

 

[AccessHawaii]

It's just a regular select query with the relationships set to show records from each table that are equal. I've tried every type of relationship too and it still displays the wrong results.

 

[lespaul]

Ok, it would be a lot easier to help if I could actually see your SQL. But here's a shot:

tbl1 tbl2 tbl3
F1 F3 F1
F2 F4 F2
F3
F4

SELECT *
FROM TBL3
INNER JOIN TBL2 ON TBL3.F3 = TBL2.F3 and TBL3.F4 = TBL2.F4 INNER JOIN TBL1 ON TBL3.F1 = TBL1.F1 AND TBL3.F2 = TBL1.F2

try that.


Leslie

 

[AccessHawaii]

Sorry. Here's the SQL statement:

SELECT tblEntry.[First Name], tblEntry.[Last Name], tblProviders.Middle, tblProviders.Title, tblProviders.[Speciality Code 1], tblProviders.[Specialty Name 1], tblProviders.[Speciality Code 2], tblProviders.[Speciality Name 2], tblProviders.[Speciality Code 3], tblProviders.[Speciality Name 3], tblEntry.[TIN #], tblClinics.[Clinic Name], tblClinics.[Location Address], tblEntry.LocationNum, tblClinics.City, tblClinics.State, tblClinics.Zip, tblClinics.Island, tblClinics.Phone
FROM tblProviders INNER JOIN (tblClinics INNER JOIN tblEntry ON (tblClinics.LocationNum = tblEntry.LocationNum) AND (tblClinics.[TIN #] = tblEntry.[TIN #])) ON (tblProviders.[First Name] = tblEntry.[First Name]) AND (tblProviders.[Last Name] = tblEntry.[Last Name]);

 

[lespaul]

OK, the probable reason for :

The problem is when I try to build a query off this relationship, I'm returning more records than I actually have. It's duplicating some records and leaving some out all together.

is that you have more than one record in tblEntry for each Location Number and TIN# that are in tblClinics or you have records in tblClinics that aren't in tblEntry or the same two situations in tblProviders. Since you have INNER JOINS that's what happens when there are multiple records (more data than you expect) or missing data. In order to "fix" it, you need to edit the joins. To do this in the query design grid, right click on the lines between the tables and select Edit Relationship. Then choose which ever of the three choices best fits what you are trying to do. You may want to review the data first and make sure it's not a data issue.

HTH

Leslie