Query returning the same records multiple times

[cheesekernd]

Hi all,

New to access here so I apologize if this is an easy question or has been answered. I have a DB with 4 tables based around a maintenance program. I want to return all PM tasks for all equipment for a fiscal month. For some reason it returns every record for that fiscal month a varying amount of times. Can someone help me out?

Here is my query:

SELECT Schedule.Fiscal_Month, Equipment.Model_Number, Task_List.Task
FROM Task_List INNER JOIN ((Equipment INNER JOIN Equipment_Parameters ON Equipment.ID = Equipment_Parameters.Equipment_ID) INNER JOIN Schedule ON Equipment.ID = Schedule.Equipment_ID) ON Task_List.ID = Schedule.Task_ID
WHERE (((Schedule.Fiscal_Month)=3));

TIA!

 

[cheesekernd]

UPDATE: I just found out about the DISTINCT keyword...

 

[isladogs]

UPDATE: I just found out about the DISTINCT keyword...

Well done!
Note that using DISTINCT it will become a read only query

BTW It looks like the Equipment_Parameters table isn't used. Suggest you remove it from your query

 

[Gasman]

I really would not leave your autonumber fields all called ID either.
Access will soon complain about which one you are talking about?
Best to give each unique names, I would have thought?. Might suffix FK to the foreign keys to differentiate?

 

[cheesekernd]

Equipment Parameters was not needed for this query. That is correct. Thanks for the heads up on removing that! I guess I need more knowledge on relationships and how they affect query's. Anyone know of a crash course on understanding the effects of relationships in querys?

 

[isladogs]

Some articles that may be helpful:

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

A slightly more complex article:

How Access Stores Queries 1

This article explains how Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window.

www.isladogs.co.uk

 

[cheesekernd]

Thanks for the replies. I will get to work so I can further understand these relationships.