Complex Problem

[Neil07979]

Hi all,

I have a fairly complex (In my eyes ) calculation/model that I need to create - and hoping that some one can point me in the best way to get this done.

I have a simple Query in Access pulling across data such as below:

Ref : 123
Start Date : 01/12/07
End Date : 01/01/08
Impact : +0.3

Ref : 123
Start Date : 18/12/07
End Date : 17/01/08
Impact : +0.4

These relate to errors that have occured and the impact these errors have. Ther may be multiple instances of the Ref, but relating to a different time period.

I am trying to create something within Access that will allow me to enter a start date and end date, along with a reference number and give me a result of the maximum impact that occured between those dates.

For instance: Using the above 2 examples:

I enter Start Date: 01/12/07 and End Date : 01/01/08 and Ref : 123

The maximum impact that I would be expecting is +0.7 between the dates of 18/12/07 and 01/01/08.

Can anyone help ?

 

[WayneRyan]

Neil,

If EITHER your Start or End date entered is within the "Start-End" for
any Ref entry, this should do it:

Select Ref, Max(Impact)
From   YourTable
Group By Ref
Having Forms![YourForm]![YourStartDate] Between [StartDate] And [EndDate] Or
       Forms![YourForm]![YourEndDate] Between [StartDate] And [EndDate]

Wayne