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 ?

 

[Peter Bellamy]

As very simple way, but a bit clumsy, would be to select the data with parameters in the Query.
Place:
Between [Start Date?] And [Finish date?]
in the Criteria of both the Start Date field and Finish date fields.
and
[Reference?]
in the criteria of the Ref field.
Then run the query.
It will ask for the date ranges and reference numbers and give all the matching records.
You can then manipulate the results in a report or form by using it as its Control source

Peter

 

[Neil07979]

Thanks Peter - but this doesn't really give me what I need - this will show all of the data that falls between the 2 dates but no indication as to what the max impact will be (See example above)

I may have 100 instances all with impacts between 2 dates, but the maximum impact could be on one particular day - once the impacts have been added up.

I need to try and get something to look at each day - and total up the impacts, and then compare against the rest of the period stated and then give me the maximum of all the days.

 

[Brianwarnock]

The problem for Peter was that your example was poor, add
Ref : 123
Start Date : 16/11/07
End Date : 15/12/07
Impact : +0.8

and we see that the answer that you want is 1.1, correct?

You are going to have fun writing that code.

Brian

 

[Peter Bellamy]

Neil, it seems I haven't understood your question!
You want to sum the impacts with the same ref no, in a date range, and just see which one has the highest value? Or see the day that has the highest sum of a single reference?

Please give us some more to work with!

Peter

 

[Neil07979]

Example attached

Sorry guys,

Didn't really give enough detail.

Brian - you are correct in what you said.

Peter - Yes, I want to establish what the max impact would be, by summing each of impacts, but clearly not all of the instances will fall across the whole range, therefore I need to establish at what point, the maximum occurs. (I dont necessarily need the date, just what the maximum would be. (Although establishing the date that this occured would be a benefit)

I have attached a spreadsheet with a few examples to try and explain what I'm trying to do.

Thanks, Neil

 

[Peter Bellamy]

Are the impact figures daily measurements? It is difficult to handle if they are associated to varying date ranges.
If the dates are not important can you just not sum all positive impacts for each reference, and the last entry date detail?

Peter

 

[Neil07979]

Hi - The inpact figures are linked to a range and not to each day

 

[Neil07979]

Hi - The inpact figures are linked to a range and not to each day

The max impact could also be a negative figure so an impact of -0.7 would be greater than +0.6. Very confusing.

 

[Brianwarnock]

The max impact could also be a negative figure so an impact of -0.7 would be greater than +0.6. Very confusing.

You would have to use the ABS function in the comparison to resolve this.

Brian

 

[Peter Bellamy]

Then I suggest you add an extra field to your table for 'cumative total' and sum all the records in to that each time they entered.
That way you can query the table to find the max ABS value of the cum' total and it associated date detail.
Peter