A method other than DLookUp

[Abedecain]

Edit, on review this is more of a query issue than anything else!



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!


Edit: Relationship table:

[orange]

You might want to start with a statement of what exactly you are trying to do in plain English --- no access, no sql, no forms, no reports.
Once we have an understanding of What you are trying to do, then suggestions/advice might follow.
12 DLookups for each record shouts out DESIGN.

Do you have tables and relationships? Did you Normalize your tables?

Also, when you show us infomration, please show the whole thing. A piece of a diagram, a query or two out of context do not help convey your issue to the reader (at least in my view).

[Abedecain]

You might want to start with a statement of what exactly you are trying to do in plain English --- no access, no sql, no forms, no reports.

Thanks for the advise.

I need a yearly planning calendar with under each month for each contract a X when there is a document to be handed in and a Y when it is.

When the work is to be completed is to be handed in is calculated by the servicing start and frequency of service and will affect if there will be servicing on any given month.

When a document is submitted, the date is recorded when the work was completed which should affect the X or Y.

Once we have an understanding of What you are trying to do, then suggestions/advice might follow.
12 DLookups for each record shouts out DESIGN.

Do you have tables and relationships? Did you Normalize your tables?

Yes, as shown in this picture:

Edit: Relationship table:

Also, when you show us infomration, please show the whole thing. A piece of a diagram, a query or two out of context do not help convey your issue to the reader (at least in my view).

What do you mean by the whole thing? The other tables and report don't influence the result of the query which gives the information to the report?

Thanks