Yearly Planned Maintenance Report

[Groundrush]

Got a little system going whereby when a job is created it will display the date that the job is next due by.

I'm trying to work out how to create a qry that will display all due dates & not just the next one.

Here is how it should work.
If a job is created on the 1st of April 2007 & the type of job is MONTHLY then I want it to list the rest of the monthly orders that are planned for the year.

Here are the different PPM types that I'm using to calculate with
8784= Yearly
43848= 5 Yearly
2208= 3 Monthly
744= Monthly
168= Weekly

Has anyone done anything similar cause I can't see how it can be done?

This is what I'm using to create the next date due

NextDue: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported])

Here is my qry that the above code is in.

SELECT tblPPMType.PPMID, tblBuildings.PropertyName, tblPPMType.PPMType, tblTasks.DateReported, tblPPMType.PPMNextDue, DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported]) AS NextDue, tblContracts.ConDescription
FROM tblContracts INNER JOIN (tblBuildings INNER JOIN (tblPPMType INNER JOIN tblTasks ON tblPPMType.PPMType = tblTasks.JobDetails) ON tblBuildings.ProID = tblTasks.BuildingID) ON tblContracts.ContID = tblTasks.ContractNo
ORDER BY tblPPMType.PPMType;

This is the result.

DateReported--------- PPMNextDue-------- NextDue
14/03/2007 14:35:34---- 8784---- 14/03/2008 14:35:34
15/03/2007 15:08:00---- 8784---- 15/03/2008 15:08:00
15/03/2007 10:07:11---- 8784---- 15/03/2008 10:07:11
14/03/2007 14:52:56---- 2208---- 14/06/2007 14:52:56
15/03/2007 15:10:21---- 2208---- 15/06/2007 15:10:21
15/03/2007 10:19:35---- 43848---- 15/03/2012 10:19:35
14/03/2007 14:54:13---- 43848---- 14/03/2012 14:54:13
14/03/2007 14:55:15---- 4416---- 14/09/2007 14:55:15
15/03/2007 22:36:27---- 744---- 15/04/2007 22:36:27
14/03/2007 14:56:30---- 744---- 14/04/2007 14:56:30
15/03/2007 20:34:27---- 744---- 15/04/2007 20:34:27
18/03/2007 22:38:41---- 744---- 18/04/2007 22:38:41
15/03/2007 13:39:02---- 744---- 15/04/2007 13:39:02
15/03/2007 10:58:55---- 168---- 22/03/2007 10:58:55
14/03/2007 14:59:21---- 168---- 21/03/2007 14:59:21

Any suggestions will be welcomed

thanks

 

[DrSnuggles]

If your VBA is quite strong you could alter the query in code based on PPM type.

But, I would recommend splitting this Report up by PPM type.
The reason for this is that based on the PPM type your query structure is going to change.

For instance, for more months to be added in your query add new columns up to 11 more times, i.e.:
NextDue_2: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[NextDue])
NextDue_3: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[NextDue_2])
etc
etc

So this won't work for your other PPM types, do you see what I mean??

 

[Groundrush]

Does that mean for the weekly ones I would have to run 52 queries

I was hoping for a way to have them appear in one qry rather than splitting them, maybe have blank entrys to fill in the gaps or something

Not sure if I'm making any sense, lets see.....perhaps something like a what you would see in a calandar.

Looks like it's more complicated to do than I first thought.