duplication of records with query with multitables (1 Viewer)

NT100 · May 17, 2018

Hi,

I've a query "qryTS_Assigned_PIP1" below to report the tutor-student map. I notice that tblTAssign_PIP is fine for the assignments but I encounter a problem of duplication of the teacher-student assignment with the following query.

SELECT tblTAssign_PIP.TRef, tblStudent.SUID, tblStudent.SName, tblTutor.Title, tblTutor.LastName, tblTutor.OffTel_1, tblTutor.FirstName, tblTutor.OffFax_1, tblTeachTT_PIP.DtTeach, tblTeachTT_PIP.APM, tblTeachTT_PIP.Day, tblTutor.ClinicAdd1, tblTutor.ClinicAdd2, tblTutor.ClinicAdd3, tblStudent.Gp, tblTeachTT_PIP.SSubGp
FROM tblTeachTT_PIP INNER JOIN ((tblTutor INNER JOIN tblTAssign_PIP ON tblTutor.TRef = tblTAssign_PIP.TRef) INNER JOIN tblStudent ON tblTAssign_PIP.SUID = tblStudent.SUID) ON tblTeachTT_PIP.ID = tblTAssign_PIP.TTID;

tblTAssign_PIP contains the assignment of tutor(TRef) to the student(SUID), TimeSession (TTID) in which teacher and student meet.
tblTutor contains primary key "TRef" and tutor's information, ...
tblStudent contains student's "SUID" (=Student UID), "SName" (=Student Name),...
tblTeachTT_PIP contains timetable in which "ID" is the key of the timeslot teacher and student meet.

Pls. refer to the screenshot for the duplication record with the above query.

I'm appreciated for any suggestions.

isladogs · May 17, 2018

No screenshot.
Have you tried using SELECT DISTINCT?

NT100 · May 17, 2018

Sorry. The screenshot is attached.

Regards.

NT100 · May 17, 2018

ridders said:
No screenshot.
Have you tried using SELECT DISTINCT?

I've tried SELECT DISTINCT but I still fail to get rid of duplicated records.

isladogs · May 17, 2018

One or more of the other fields added to the query contains non duplicate data.
Create a new query removing all fields except those needed for this example.
There should be no duplicates now

NT100 · May 17, 2018

Thank you for the advice.

I still have difficulty in resolving it. Anyway, I put the application in Dropbox as at https://www.dropbox.com/s/jhis7726ufxngyx/DuplicateStd.accdb?dl=0 as it's too big to be attached.

I notice that the query is in question. It pops for "Enter Parameter .." when run independently.

One example of the duplicated records is Tutor 41 at page 7 of the report of "rptST_Map_PIP1_Test".

Thanks in advance.

isladogs · May 17, 2018

Sorry but many of us, including me, don't open files at external sites.
Suggest you strip down the db to what is needed to demonstrate the issue, compact and zip it.

plog · May 17, 2018

I on the otherhand have no regard for my work computer so I downloaded your database (the story would be different if I was at home). I don't think your tables are set up correctly. You have so many redundant fields. I see [PiPYr], [SUID], [AcademicYr], [TTID], [Session] in multiple tables, yet they are not linked by them they are linked by other fields. That is incorrect.

I don't understand your data, but I understand normalization and that's a red flag. There should really only be one way to link tables--you have many options. Most likely you are missing a table. That table would hold all common fields, have its own ID and that ID would go into the existing tables.

As for your issue, it is caused by linking on non-unique keys between tblTAssign_Pip and tblStudent_PIP. There are "duplicate" values of SUID in one or both of them. When you join them the number of results multiply into rows of the query.

tableA
ID
1
1
2
3
3
4

tblB
ID
1
2
3
3

When I INNER JOIN tableA and tableB I will get 7 records in the query:

ID=1 - 2 records in tableA * 1 record in tableB = 2 records
ID=2 - 1 record in tableA * 1 record in tableB = 1 record
ID=3 - 2 records in tableA * 2 recrods in tableB = 4 records
ID=4 - 1 record in tableA * 0 records in tabeB = 0 records

Each record matches with every match it finds and produces a record. That's what's happening in your query and supports my not set up theory about your tables.

Bonus--there's an abandoned TRef value in tblTAssign_PIP (or perhaps its null, I didn't check). That is, the value is in tblTassign_PIP but not tblTutor. Which means, you are actually short a record as well as over (see ID=4 above).

NT100 · May 17, 2018

I have reduced the database size and uploaded as attached for tests.

The tables were signed to meet the business rules. I hope to reduce the redundant incurred. More suggestions on the tables design and query for the report are appreciated.

NT100 · May 18, 2018

plog said:
I on the otherhand have no regard for my work computer so I downloaded your database (the story would be different if I was at home). I don't think your tables are set up correctly. You have so many redundant fields. I see [PiPYr], [SUID], [AcademicYr], [TTID], [Session] in multiple tables, yet they are not linked by them they are linked by other fields. That is incorrect.

I don't understand your data, but I understand normalization and that's a red flag. There should really only be one way to link tables--you have many options. Most likely you are missing a table. That table would hold all common fields, have its own ID and that ID would go into the existing tables.

As for your issue, it is caused by linking on non-unique keys between tblTAssign_Pip and tblStudent_PIP. There are "duplicate" values of SUID in one or both of them. When you join them the number of results multiply into rows of the query.

tableA
ID
1
1
2
3
3
4

tblB
ID
1
2
3
3

When I INNER JOIN tableA and tableB I will get 7 records in the query:

ID=1 - 2 records in tableA * 1 record in tableB = 2 records
ID=2 - 1 record in tableA * 1 record in tableB = 1 record
ID=3 - 2 records in tableA * 2 recrods in tableB = 4 records
ID=4 - 1 record in tableA * 0 records in tabeB = 0 records

Each record matches with every match it finds and produces a record. That's what's happening in your query and supports my not set up theory about your tables.

Bonus--there's an abandoned TRef value in tblTAssign_PIP (or perhaps its null, I didn't check). That is, the value is in tblTassign_PIP but not tblTutor. Which means, you are actually short a record as well as over (see ID=4 above).

Thank you for your idea.
It seems that I worked out the method. I redesigned the tables and relationships. Now, the query doesn't produce duplicate records.

As a matter of efficient tables structure, are my tables design appropriate?

I brief describe the tables and their relationships as follows:-

tblTutor - TRef (key), tutor name, etc.
tblStudent - SID (key), student name, contacts,
tblStuent_PIP - SID (foreign key from tblStudent); student takes PIP program
tblTAssign_PIP - TRef (foreign key fromtblTutor), SID (foreign key from tblStudent), to assign tutor to student relation for PIP program
tblTeachTT_PIP - ID (Key), contains PIP program teaching session date/time.

plog · May 18, 2018

Probably not. First, can you give me an explanation of the real world system this models? No database jargon, explain to a 6th grader what it is your organization does. Then, 1 more paragraph which is light on database jargon and explains what you hope your database will accomplish for you.

I do see some things with that structure--

1. I think Student and Tutor data should be combined into 1 table--tblPeople. In there you can designate if they are a tutor or a student.

2. I don't see how Students/Tutors are assigned to TeachTT_PIP. How does that table fit into all this.

It would be best if you set up your relationship tool and posted a screenshot of it

NT100 · May 21, 2018

plog said:
Probably not. First, can you give me an explanation of the real world system this models? No database jargon, explain to a 6th grader what it is your organization does. Then, 1 more paragraph which is light on database jargon and explains what you hope your database will accomplish for you.

I do see some things with that structure--

1. I think Student and Tutor data should be combined into 1 table--tblPeople. In there you can designate if they are a tutor or a student.

2. I don't see how Students/Tutors are assigned to TeachTT_PIP. How does that table fit into all this.

It would be best if you set up your relationship tool and posted a screenshot of it

I'll. I'm a novice, what relationship tools you would suggest to use for the work?

Thanks in advance.

plog · May 21, 2018

In Access go to the ribbon, click on 'DATABASE TOOLS', then Relationships, add all your tables and then connect them appropriately.

duplication of records with query with multitables (1 Viewer)

NT100

Registered User.

isladogs

MVP / VIP

NT100

Registered User.

Attachments

NT100

Registered User.

isladogs

MVP / VIP

NT100

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

NT100

Registered User.

Attachments

NT100

Registered User.

plog

Banishment Pending

NT100

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread