Query to Pull Previous Record Value to Use in Calculation 3

[laudieda]

Be patient with me as I am a new, very inexperienced user of Access 2000 and my question my be very simply to solve. However, I am an Accountant that has made a database to track the Market Values of our employees 401k’s. I am trying to use a query to Pull the Contract#, Date, MValue, PreviousValue, and Gain/loss. My problem lies in being able to pull the Previous Market Dates’ Market Value into the Previous Value to be able to perform the calculation to get the Gain/Loss for each Contract#.

This is my table structure:

Table: MarketValue

Field Name: MarketValueID (Primary Key)
Data Type: Autonumber

Field Name: Date
Data Type: Date/Time

Field Name: MValue
Date Type: Currency

Field Name: Contract #
Date Type: Text


Table: ContractOwner
------------------------------
Field Name: Contract# (Primary)
Data Type: Text

Field Name: Employee Number
Data Type: Number

Field Name: LastName
Date Type: Text

And so on…


View of table in Market table:

Market ID Date MValue Contract #
1 06/30/2005 35,147.19 200
2 06/30/2005 50,000.00 300
3 09/30/2005 36,000.0 200
4 09/30/2005 48,000.00 300


What I would like to be able to create in a query

Contract # Date MValue PreviousValue Gain/Loss
200 06/30/05 35,147.19
200 09/30/05 36,000.00 35,147.19 852.81
300 06/30/05 50,000.00
300 09/30/05 48,000.00 50,000.00 (2,000.00)


I know how to calculation the Gain/Loss ,

Gain_Loss: [MValue] – [PreviousValue]




I have tried the following Expression using DLookup

PreviousValue: DLookup(“[MValue]”, “MarketValue”, “[ContractNo] =” & [ContractNo] & “ AND [Date] <#” & [Date] & “#”)


I have also tried Making a query placing an Alias on the Date field as Date1

I used the MarketValue Table. Added the ContractNo, Date, and MValue fields

I then set the Alias on the Date Field as Date1


PrevValue: (SELECT [MValue] FROM [MarketValue] WHERE [MarketValue].[ Date] < [Date1].[Date] AND [MarketValue].[ContractNo] = [Date1].[ContractNo])

Any help will be greatly appreciated.

 

[PHV]

Something like this ?
SELECT A.[Contract #], A.Date, A.MValue, Sum(P.MValue) As PreviousValue, A.MValue-Nz(Sum(P.MValue),A.MValue) As [Gain/Loss]
FROM MarketValue As A LEFT JOIN MarketValue As P
ON A.[Contract #] = P.[Contract #] AND A.Date > P.Date
GROUP BY A.[Contract #], A.Date, A.MValue
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[laudieda]

I can not thank you enough... I have read...search...tried one thing after another for several days trying to get this.... As I said....Very new user and trying to learn.

Once Again thank you that is exactly what I needed.