Planning calendar

[Abedecain]

Hi, this report has been annoying me and I cant think of a simpler solution to the one I have. It is at the moment taking a awful long time to query as there is 12 DLookUp's for each record.

Currently I have a Contract starting date and service frequency which dictates when something needs to be serviced, i.e. quaterly.


In another table that is linked is the maintenance documentation which has the related contract id and date it was done.

All I need is a x when there is maintenance to be done but not uploaded for that month (give or take a months depending on the frequency), and a y when there is maintenance done and uploaded for that month (give or take a months depending on the frequency).

SELECT Contracts.comp_name, Contracts.equipment, Contracts.type, Contracts.start_date,
IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,1,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,1,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,1,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Jan2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,2,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,2,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,2,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Feb2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,3,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,3,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,3,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Mar2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,4,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,4,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,4,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Apr2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,5,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,5,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,5,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS May2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,6,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,6,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,6,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Jun2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,7,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,7,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,7,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Jul2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,8,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,8,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,8,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Aug2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,9,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,9,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,9,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Sep2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,10,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,10,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,10,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Oct2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,11,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,11,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,11,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Nov2, IIf([Frequency].[months]<1,"✘",IIf(DateDiff("m",[servicing_start],DateSerial(2012,12,1)) Mod [Frequency].[months]=0,IIf(IsNull(DLookUp("Documents.start","Service Planning Calander Doc Lookup","(Contracts.contract_ID)=" & Contracts.contract_ID & " AND (DateDiff('m',[Documents].[start],DateSerial(2012,12,1))>=-Round([frequency].[months]/4) And DateDiff('m',[Documents].[start],DateSerial(2012,12,1))<=Round([frequency].[months]/4))")),"✘","Y"))) AS Dec2, Contracts.archive, Contracts.frequency_service, Frequency.months, Contracts.void_unit, Contracts.contract_ID
FROM Frequency INNER JOIN Contracts ON Frequency.[frequency] = Contracts.[frequency_service]
WHERE (((Contracts.archive)=False) AND ((Contracts.frequency_service)<>"N/A") AND ((Contracts.void_unit)=False));

The DLookUp relates to:

SELECT Documents.category, Documents.attachments, Documents.start, Frequency.months, o.contract_ID
FROM Frequency INNER JOIN (Contracts o INNER JOIN Documents ON o.contract_ID = Documents.contract_ID) ON Frequency.frequency = o.frequency_service
WHERE (((Documents.category)="Maintenance Records") AND ((Frequency.months)>0));

I would really appreciate any help that can be given as this has been giving me a headache!

Thanks,
Lucas

[ssanfu]

Any chance you would post a demo dB with sample data?

I've been trying to create the tables and data so I could try a couple of ideas, but those are some big query columns.