Query for overdue maintenance

[Rzadziu]

Hi,



I have a problem with building the query for overdue maintenance.

Based on help from JoeM https://www.accessforums.net/queries...tml#post141350 i have built a query to get the last date of the maitenance (for each machine_id and Ctype)
Now I would like to filter that to get those records for which DiffDate is larger than Ctype (for each machine_id and Ctype).

At the end I would like to get the effect as in this picture where the window on the right saw all I will need to perform maitenance.





This is a query which i have create

SELECT Machinesmaintenance.MDate AS [Last Maitenance],  Maxofcontmachines.Machine_ID, Machinesmaintenance.Controler,  Machinesmaintenance.CType AS [Control type], Maxofcontmachines.DifDate,  Machines.Machine_name
FROM Users INNER JOIN ((Machines INNER JOIN  Machinesmaintenance ON Machines.Machine_ID =  Machinesmaintenance.Machine_ID) INNER JOIN Maxofcontmachines ON  Machines.Machine_ID = Maxofcontmachines.Machine_ID) ON (Users.EmpID =  Machines.U1.Value) AND (Users.EmpID = Machinesmaintenance.Controler);

But some records are shown several times.

[June7]

Could you provide db for analysis? Follow instructions at bottom of my post.

[Rzadziu]

Please have a look on attached file.

Machines.zip

[June7]

You are using lookups in tables. I never do that. Review http://access.mvps.org/access/lookupfields.htm

Consider:
SELECT Maxofcontmachines.Machine_ID, Maxofcontmachines.MaxOfMDate, Maxofcontmachines.DifDate, Machines.Machine_name, Maxofcontmachines.CType
FROM Machines INNER JOIN Maxofcontmachines ON Machines.Machine_ID = Maxofcontmachines.Machine_ID
WHERE (((Maxofcontmachines.DifDate)>[CType]));

[Rzadziu]

That works perfectly for me thanks a lot.