Show Beginning and Ending Date/Data Range 1

[leicklda]

Field 1=Beginning Date
Field 2=Beginning Balance

The dates are progresssive entries which have 3-4 month intervals. Each date has a balance associated with it.

I need a query where I can have the following fields:

Field 1=Beginning Date
Field 2=Beginning Balance
Field 3=Ending Date
Field 4=Ending Balance

Where Ending date is the NEXT date from the list of Beginning Dates. And Ending Balance is the balance associated with that next date.

I want this query so that I can have a form which always shows a range of two dates, and the balance associated with each date.

Any help is greatly appreciated.

Thanks!

 

[rac2]

One approach to this problem would be to store the date and balance twice whenever you add a balance. This might work if the balance for a date cannot change once it is calculated.

This would use a two-step process to add a new balance. Step 1, update the ending balance of the latest record. This can be found by searching for the record where the ending balance is null or searching for the record with the maximum date. Step 2, insert a new record with the balance in the beginning balance field. Anytime you want to display a balance for some date that record will also have the next balance. The advantage of this approach is that these are relatively simple operations.

The other approach which I think you have in mind is to write a query that searchs for the record with the minimum date following the date of the beginning balance. This can be done with a correlated subquery, however I don't know whether this is possible in Access.

SELECT a.balance, a.date, b.balance, b.date
FROM history a,
     (  SELECT balance, date
        FROM history
        WHERE date = 
        (  SELECT MIN(date)
           FROM history
           WHERE date > a.date
        )
     ) AS b

Possibly the beginning date will be a parameter value entered when the query runs, call it [Begin Date].

SELECT a.balance, a.date, b.balance, b.date
FROM history a,
     (  SELECT balance, date
        FROM history
        WHERE date = 
        (  SELECT MIN(date)
           FROM history
           WHERE date > [Begin Date]
        )
     ) AS b
WHERE a.date = [Begin Date]

Perhaps the Domain Aggregate minimum function can be used.

SELECT a.balance, a.date, b.balance, b.date
FROM history a,
     (  SELECT balance, date
        FROM history
        WHERE date = 
          DMin("[date]","history", "[date] > #[Begin Date]#")
     ) AS b
WHERE a.date = [Begin Date]