My form is showing duplicate records

[5hadow]

I'm thinking that it's because of many-to-many relationship in my tables, but I just cannot figure out how to solve the problem.

Here's the problem:

Here are relationship tables:

Form SQL

SELECT tblMember.*, tblMemberSection.*, tblSection.*, tblWI.*
FROM (tblSection INNER JOIN (tblMember INNER JOIN tblMemberSection ON tblMember.fldMemberID = tblMemberSection.fldMemberID) ON tblSection.fldSectionID = tblMemberSection.fldSectionID) INNER JOIN tblWI ON tblSection.fldSectionID = tblWI.fldSectionID;

 

[plog]

First, you need to fix your relationships. There should only be one way to draw a path between tables. You've essentially got a loop and that is incorrect. Most likely tblSection is related to too many other tables. Instead, I would add a new field to tblMemberSection called "fldRole" and then in that field you can designate if a Member is the DocOwner or the AppAuth. That fixes your relationship issue which is bigger than your form issue.

To help you with the issue you are posting about we need to know the big picture. What are you actually trying to accomplish? How many records do you expect to see? What is the purpose of the "form"? I use quotes because that "form" looks like a query to me. Many-many will explain "duplicate" records, but you might have an issue with some records not showing which would be the fault of the INNER JOIN. You're query is working perfectly like it should given how you set it up, so now we need to know what do you expect to see?

 

[LanaR]

What is the purpose of the two tables tblMember and tblMember_2 they both seem to be holding identical information about members

 

[June7]

@LanaR, There is only one tblMember but it is used twice with two fields. Access automatically named the second instance in the Relationship builder with a _1 suffix.

Use form/subform arrangement for data entry/edit.

 

[LanaR]

@LanaR, There is only one tblMember but it is used twice with two fields. Access automatically named the second instance in the Relationship builder with a _1 suffix.

Use form/subform arrangement for data entry/edit.

Ah of course

 

[LanaR]

Perhaps a GroupBy/Totals query would be the way to solve the duplicate issue?

 

[June7]

@LanaR, not if requirement is to data entry/edit to master and child records.

 

[arnelgp]

table you are concerned is tblWI, right? the others
are but to show Related fields.
Use Left Join whenever possible.
This will make your query updateable.
You don't need to Involve all tables in
the relations.

SELECT
    A.*,
    B.fldSectionName AS [Section],
    [C_1].[fldFirstName] & " " & [C_1].[FLDLASTNAME] AS Owner,
    [C_2].[fldFirstName] & " " & [C_2].[FLDLASTNAME] AS Author
FROM ((tblIWI AS A LEFT JOIN tblSection AS B ON A.fldSectionID = B.fldSectionID)
    LEFT JOIN tblMember AS C_1 ON B.fldDocOwner = C_1.fldMemberID)
        LEFT JOIN tblMember AS C_2 ON B.fldAppAuth = C_2.fldMemberID;

 

[LanaR]

@June7 True, but then again the OP has to date only stated that they wish to eliminate the duplicates and has not outlined their wider objective

 

[5hadow]

First, you need to fix your relationships. There should only be one way to draw a path between tables. You've essentially got a loop and that is incorrect. Most likely tblSection is related to too many other tables. Instead, I would add a new field to tblMemberSection called "fldRole" and then in that field you can designate if a Member is the DocOwner or the AppAuth. That fixes your relationship issue which is bigger than your form issue.

To help you with the issue you are posting about we need to know the big picture. What are you actually trying to accomplish? How many records do you expect to see? What is the purpose of the "form"? I use quotes because that "form" looks like a query to me. Many-many will explain "duplicate" records, but you might have an issue with some records not showing which would be the fault of the INNER JOIN. You're query is working perfectly like it should given how you set it up, so now we need to know what do you expect to see?

Hello,

Maybe this might help:

Each section (tblSection) can have many documents (tblWI). Each section also has one unique doc owner (fldDocOwner) and one unique approving authority (fldAppAuth). As an extension, each document (tblWI) also has the same doc owner and approving authority as the section that document belongs to.
I want to make a sub-form which has all tblWI fields and be able to display who the doc owner is and who the approving authority is for each of the documents. As per my screen grab above, I have made that work, but it makes duplicates.
I also want to be able to add new members, and assign to which section they belong to and weather they're doc owner or approving authority.

Relationships were all messed up because I was making drop-down boxes so it auto added them. Included is a picture of what my actual relationships look like:

 

[5hadow]

table you are concerned is tblWI, right? the others
are but to show Related fields.
Use Left Join whenever possible.
This will make your query updateable.
You don't need to Involve all tables in
the relations.

SELECT
    A.*,
    B.fldSectionName AS [Section],
    [C_1].[fldFirstName] & " " & [C_1].[FLDLASTNAME] AS Owner,
    [C_2].[fldFirstName] & " " & [C_2].[FLDLASTNAME] AS Author
FROM ((tblIWI AS A LEFT JOIN tblSection AS B ON A.fldSectionID = B.fldSectionID)
    LEFT JOIN tblMember AS C_1 ON B.fldDocOwner = C_1.fldMemberID)
        LEFT JOIN tblMember AS C_2 ON B.fldAppAuth = C_2.fldMemberID;

Wow, that works great, but I'm trying to understand it...
By the way, I'm a bit new to all this.

 

[arnelgp]

By the way, I'm a bit new to all this.

you need to read more about MS Access Query.
there are lots in the web.