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?

 

[Pat Hartman]

The DISTINCT keyword is NOT the solution to your problem. The problem with the query and the cause of the duplicates is that there are TWO parallel 1-many relationships.

Equipment --> Equipment_Parameters
Equipment --> Schedule --> Task

For every instance of Equipment_Parameters there will be a duplication of the second set of data. So if there are 1, 2, 3, 4, etc actual Parameters records for a piece of Equipment, then the second set of data will be multiplied by however many rows the first join returns.

What is the purpose of Equipment_Parameters? The solution is probably to remove this table from the query.

 

[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

 

[Pat Hartman]

None of the examples or articles would help you with the mistake you made. Being more organized about how you lay out the tables in the queries might set off a warning light if you have defined relationships so Access will show the relationship when you make the join. Notice how I laid out my explanation. You see the same Table on the left and two sets of tables related to the Equipment but not related to each other.

Rearrange the picture you posted in #1 and you will see the parallel relationships. Just because two tables are related to a third, doesn't mean they are related to each other. A silly example is

Students-->Classes
Students-->Pets

Classes have nothing to do with pets although both are related to students. This example will result in something like:
Joe, Math, Spot
Joe, Math, Sparky
Joe, English, Spot
Joe, English, Sparky
Sue, Math, Blinky
Sue, Math, Patches
Sue, Math, Little Kitty
Sue, Geography, Blinky
Sue, Geography, Patches
Sue, Geography, Little Kitty

Clearly the result makes no sense.

When you create a query that contains more than two tables, the query will work in only two situations.
1. The tables are hierarchical. Such as Equipment --> Schedule --> Task
2. The extra tables are Lookups so they will return one and only one row. Such as

Order-->OrderDetails-->Product
Order<--Customer

The difference in this case is is the relationships OrderDetails and Customer have with Order. Order is 1-m with Orderdetails but is m-1 with Customer because an order can have many details but only one customer. In your case Equipment is 1-m with Schedule and also 1-m with parameters.

Your query isn't giving you a visual clue because you haven't defined relationships.

 

[cheesekernd]

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

 

[Pat Hartman]

You're welcome