Report Last Occurance (1 Viewer)

[khwaja]

I have a complex question. Will try to explain it. I have a query where I have listed stores and various projects that have been completed in that store as well as the date of completion. As part of the projects few things (SOW) were done to the store in its life time. So it is quite possible that a store named Woden may have a refurb followed by another project we call 'One Up' and that was followed by yet another project 2010C. Each of the works in these projects has a life and I can calculate what has expired. My task is to list store with works that may have been 'renewed' and not expired yet. As has been shown in the attached spreadsheet, the store received a service desk in Nov 2005 but it received a new service desk in Mar 2008. So I would like to list store with items that are more recent. I just cannot find a way to manage this in a query. Somehow the SOW value needs to be evaluated by the date and then whether it expired or not. Any help will be greatly appreciated.

 

[CJ_London]

any solution requires that the SOW is consistent in spelling - so 'Service Desk' is always 'Service Desk' and never a typo or abbreviation like 'Sirrvice Desk' or 'Desk'

Can you confirm that is the case

 

[khwaja]

Many thanks for kindly looking into this. Yes the naming convention is quite consistent in terms of spelling. Cheers

 

[CJ_London]

OK. I've assumed your table is called tblProjects and you have renamed the Date column to SOWDate. The reason is that Date is a reserved word (it is a function returning the value of today) so using it as a field name can cause problems and produce unexpected results.

This query returns all unexpired (as of today) SOWs for each location. Where for any given SOW description there is more than one unexpired SOW, then one with the latest expiry date is returned - I've assumed that for example one service desk might last for 5 years and another 10 years

SELECT *
FROM tblProjects
WHERE (((DateAdd("yyyy",[life],[SOWDate]))>Date() 
    And (DateAdd("yyyy",[life],[SOWDate]))=
        (SELECT Max(DateAdd("yyyy",[life],[SOWDate])) FROM tblProjects AS P WHERE Location=tblProjects.Location AND SOW = tblProjects.SOW)));

 

[khwaja]

This is great. I may still need to refine it. Any issues. i will report but generally it is working well.

Cheers