Looking for suggestions

K

Kevbro7189

I have a table in it I have a [Date] in which an [Event] occurred this event
could have happened multiple times during that date.
Example:
[Date] [Event]
1/1/08 2
2/1/08 3
3/1/08 1
4/1/08 1
Problem:
I’m trying to make a form that will display the [Date] at which time the
[Event] from Now back will = 3. The formula will have to tally the [Event]
from now backwards and once it reaches a total of 3 it will display the
[Date]. However like in the example the [Event] may equal 4, and in this case
I would want to display 2/1/08.
 
J

John Spencer

I think the following might work

SELECT TOP 1 A.[Date], SUM(B.Event] as TheTotal
FROM SomeTable as A INNER JOIN SomeTable As B
On A.[Date] <= B.Date
WHERE A.[Date] <= Date()
GROUP BY A.[Date]
HAVING SUM(B.Event) >= 3
ORDER BY SUM(B.Event) Desc

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Top