Generate end date from next records start date

[MikeAuz1979]

Hi,

Using Access 97 I'm linking to an oracle table like below (Using a normal access query to a linked table):

CONTRACT_NUMBER START_DATE END_DATE BOOKED_GJ
12345 01-jan-05 100
12345 01-jan-06 150
11111 01-jan-06 500

Where I'm trying to link the Booked_GJ value to another table's daily data on CONTRACT_NUMBER between the start and end dates.

As you can see there's no end dates for the period of the contracts, any ideas how to populate this to the last day before the start of the new contract Start_Date and to default this to 01-Jun-10 if there is no later contract?

For example in the 12345 case I'd like to see End Date, rows 1 and 2 to be 31-Dec-05 and 01-Jun-10 and for row 3 I'd like to see 01-Jun-10

Thanks heaps for any help
Mike

 

[dhookom]

Do you think you need to store the end date back into a table? Do you have a table name?

Why 01-Jun-10? Is this always going to be that date?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MikeAuz1979]

Thanks for answering and Sorry if I wasn't clear, but the result I'm after is a normal old select query (With the populated end-dates) rather than anything fancy making tables.

As a hinted at above once I have the end_dates populated I intend to link a table (tblConsumption) of daily USAGE_GJ to this one (tblBookedUsage), linking these 2 tables on the contract number and putting in a between statement for the contract start and end dates.

NB: GJ is GigaJoules, this is all to do with amounts of natural gas booked and used.

So as an example of what I'm finally trying to acheive:

Date Contract Number USAGE_GJ Booked_GJ
30-Dec-05 12345 78 100
31-Dec-05 12345 87 100
01-Jan-06 12345 68 150

NB: The booked_GJ figure changes on the 01-jan-06 as the new Booked GJ has taken effect.


The 1-Jun-10 thing is the default end date if there isn't a later contract record (if there's only 1 record in this tblBookedUsgae table for that contract number) and I guess this is just a way of saying the contract will end way off in the future. Apologies but I just noticed that the table I drew is skewed. for row1 01-Jan-05 is the START_DATE the END_DATE is blank and the BOOKED_GJ is 100

Thanks again for any help
Mike

 

[dhookom]

I'm confused why you would have two contracts beginning on the same date and the same contract number with two different starting dates. I'm not sure what you are asking is possible without something else to identify the order.

This is the SQL that I have created so far but I'm missing something that differentiates the 2nd and 3rd row since they are the same date:

SELECT tblBookedUsage.*, Nz((SELECT MIN(START_DATE)-1 FROM tblBookedUsage b WHERE b.CONTRACT_NUMBER <>tblBookedUsage.CONTRACT_NUMBER AND b.START_DATE >= tblBookedUsage.START_DATE),#6/1/2010#) AS END_DATE
FROM tblBookedUsage;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MikeAuz1979]

Thanks again Dhookom for caring! I think it's the name CONTRACT thats confusing you think of it as CUSTOMER instead and each record with that same CUSTOMER number is a new contract.

I've had some luck and have acheived this but by using a make table query, which I'd like to avoid if possible. (I tried the stage 1 query as a select query but found that my EndDate function interefered with it.)

My Maketable solution:
--(Stage 1,creating a row id)

SELECT mdq.SUBCONTRACT_NUMBER, mdq.BOOKING_START_DATE, mdq.BOOKING_EFFECTIVE_DATE, mdq.BOOKED_GJ_BASE, mdq.BOOKED_GJ_EXTRA, PlusOne([SUBCONTRACT_NUMBER]) AS RowID INTO tblTest1
FROM CCDR_MDQ_BOOKING_SUMMARY mdq
ORDER BY mdq.SUBCONTRACT_NUMBER, mdq.BOOKING_START_DATE;

Which uses the function:

Function PlusOne(var As Variant)
Static i As Double
i = i + 1
PlusOne = i
End Function

---(Stage 2, adding end dates)

SELECT tblMDQ.SUBCONTRACT_NUMBER, tblMDQ.BOOKING_START_DATE, EndDate([RowID]) AS [Booking End Date], tblMDQ.BOOKING_EFFECTIVE_DATE, tblMDQ.BOOKED_GJ_BASE, tblMDQ.BOOKED_GJ_EXTRA, tblMDQ.RowID
FROM tblMDQ

Which uses the function:

Function EndDate(RowID As Double)
Dim result As Variant
Dim CurrentSubC, NextSubC As String


CurrentSubC = DLookup("[SUBCONTRACT_NUMBER]", "tblTest1", "[RowID] = val(" & RowID & ")")
NextSubC = DLookup("[SUBCONTRACT_NUMBER]", "tblTest1", "[RowID] = val(" & RowID + 1 & ")")

If CurrentSubC = NextSubC Then
result = DLookup("[BOOKING_START_DATE]", "tblTest1", "[RowID] = val(" & RowID + 1 & ")") - 1
Else
result = #6/1/2010#
End If
EndDate = result


End Function