query returning duplicate rows (1 Viewer)

I have three tables named Teacher Attendance, Teacher, Teacher Fine

Teacher Attendance table contains the following fields :
Teacher ID
Attendance Date
Status
School Timing
Arrival Time
Late Time Duration
Remarks

Teacher table contains the following fields :
Teacher ID
Teacher Name
Qualification
Class Assigned
Mobile No
Remarks
Inactive

Teacher Fine Table contains the following fields :
Teacher ID
Date
Absent
Late
Inactive

Now I create a Query with Table Teacher and Table Teacher Fine name Query 44 with contains the following fields:
Teacher ID
Teacher Name
Date
Absent
Late

This query works fine

When I Join this Query 44 with Teacher Attendance (only one field from teacher attendance named "Late Time Duration". It returns the duplicate record with the same data

The query expression is as under

SELECT Query44.[Teacher ID], Query44.[T Att ID], Query44.[Teacher Name], Query44.Date, Query44.Absent, Query44.Late, [Teacher Attend].[Late Time Duration], Month([Date]) AS Expr1
FROM Query44, [Teacher Attend]
WHERE (((Month([Date]))=Month(Now())-1));

Anyone help me why the second query returning in duplicate
I will be grateful for this kindness.

Hi. Your SQL statement indicates a cartesian product (no join) and your WHERE clause is probably not filtering the duplicates out.

Mr theDBguy

When I join the both (Querry 44 and table Teacher Fine (with Teacher ID))
the query produced the same result

The query expression is as under

SELECT Query44.[Teacher ID], Query44.[T Att ID], Query44.[Teacher Name], Query44.Date, Query44.Absent, Query44.Late, [Teacher Attend].[Late Time Duration], Month([Date]) AS Expr1
FROM Query44 INNER JOIN [Teacher Attend] ON Query44.[Teacher ID] = [Teacher Attend].[Teacher ID]
WHERE (((Month([Date]))=Month(Now())-1));

ijaz196 said:

Mr theDBguy

When I join the both (Querry 44 and table Teacher Fine (with Teacher ID))
the query produced the same result

The query expression is as under

SELECT Query44.[Teacher ID], Query44.[T Att ID], Query44.[Teacher Name], Query44.Date, Query44.Absent, Query44.Late, [Teacher Attend].[Late Time Duration], Month([Date]) AS Expr1
FROM Query44 INNER JOIN [Teacher Attend] ON Query44.[Teacher ID] = [Teacher Attend].[Teacher ID]
WHERE (((Month([Date]))=Month(Now())-1));

Click to expand...

Okay, with an INNER JOIN matching the TeacherID, you would get, in return, as many rows as there are matching values in both tables. For example, let's say in Table1, there are 1 TeacherID with a value of 99, and in Table2, there are 3 TeacherID with the same value of 99, then the result of the query will be 3 rows where TeacherID is 99. Does that seem like the case with your tables?

Thanks for reply
Mr theDBguy

yes both table has the same number in teacher id field

ijaz196 said:

Thanks for reply
Mr theDBguy

yes both table has the same number in teacher id field

Click to expand...

And there's more than one? If so, that's where the duplicate is coming from. For example, let's say table1 has TeacherID=1 with say TeacherName=Joe and table2 has TeacherID=1 twice with two different associated remarks like Yes and No. Then your query will return both rows like this:
You get two Joe's (duplicates). Make sense?

Thanks for reply
Mr theDBguy

yes both table has the same number in teacher id field

ijaz196 said:

Thanks for reply
Mr theDBguy

yes both table has the same number in teacher id field

Click to expand...

Looks like this is a duplicate post. Did you see my reply above?

how about if you introduced another join field:

SELECT Query44.[Teacher ID], Query44.[T Att ID], Query44.[Teacher Name], Query44.Date, Query44.Absent, Query44.Late, [Teacher Attend].[Late Time Duration], Month([Date]) AS Expr1
FROM Query44 INNER JOIN [Teacher Attend] ON Query44.[Teacher ID] = [Teacher Attend].[Teacher ID] And Month([Date]) = Month([Teacher Attend].[Attendance Date])
WHERE (((Month([Date]))=Month(Now())-1));

Thanks a lot, Mr theDBguy
It helps me and solves my problems

Thanks a lot again

ijaz196 said:

Thanks a lot, Mr theDBguy
It helps me and solves my problems

Thanks a lot again

Click to expand...

Hi. You're welcome. Mr Arnel and I were happy to assist. Good luck with your project.