# Access Query... similar to MS-Excel function "=IF(A1<>A2,E2,E2-E1)"

I have the following listed (sample) record set.   Currently, I use MS-Excel for calculating a "new" selling price and determining the profit once the same unit is sold again.

For instance, in the record shown below, I have 5 unique accounts (properties).   Acct #1 was originally purchased for \$245, then resold for \$265 (giving me a change of \$20)... resold again for \$293 (profit of \$28) and sold yet another time for \$293 (profit of \$42).

Account      Address      HouseNumber      SalesDate      SalesPrice      Change
1      Main Street      100      09/2003      245      245
1      Main Street      100      03/2004      265      20
1      Main Street      100      09/2004      293      28
1      Main Street      100      03/2006      335      42
2      Main Street      105      08/2003      205      205
2      Main Street      105      10/2005      328      123
3      Main Street      120      08/2003      206      206
3      Main Street      120      08/2005      302      96
4      Main Street      123      02/2004      244      244
5      Main Street      130      07/2003      200      200
5      Main Street      130      12/2004      285      85

Now, in Excel, I use the following formula:  =IF(A1<>A2,E2,E2-E1)
... while column A=Account; column E=Sales price; row #1 is "header... w/ no data"

The Excel formula is "smart enough" to determine when I reach a "new account" e.g. "#2"; hence, it simply places the original selling price into column F.

Does anyone know how to do the same in Access?

Thanks,
EEH
###### Who is Participating?

Commented:
I see my mistake:

SELECT a.account, a.Address, a.HouseNumber, a.SalesDate, a.SalesPrice,
(SELECT Last(b.SalesPrice) FROM myTable AS b WHERE b.SaleDate<a.SalesDate and b.account=a.account) as PrevSalesPrice,
a.SalesPrice - PrevSalesPrice AS Change
FROM myTable AS a ORDER BY a.account, a.SalesDate;
0

Commented:
Hey there,

this doesnt look to easy to do in access. you would need a build a vb function to loop through your house nos and update the change col based on the previous  salesprice for that house.

0

Commented:
let me know what kind of help u need
0

Commented:
Try this:

SELECT a.ccount, a.Address, a.HouseNumber, a.SalesDate, a.SalesPrice,
(SELECT Last(b.SalesPrice) FROM myTable AS b WHERE b.SaleDate<a.SalesDate) as PrevSalesPrice,
a.SalesPrice - PrevSalesPrice AS Change
FROM myTable AS a ORDER BY a.account, a.SalesDate;

0

Author Commented:
GRayL:

I tried this... the outcome looks like this:

account      Address      HouseNumber      SalesDate      SalesPrice      PrevSalesPrice      Change
1      Main Street      100      09/2003      \$245.00      \$200.00      \$45.00
1      Main Street      100      03/2004      \$265.00      \$200.00      \$65.00
1      Main Street      100      09/2004      \$293.00      \$200.00      \$93.00
1      Main Street      100      03/2006      \$335.00      \$285.00      \$50.00
2      Main Street      105      08/2003      \$205.00      \$200.00      \$5.00
2      Main Street      105      10/2005      \$328.00      \$285.00      \$43.00
3      Main Street      120      08/2003      \$206.00      \$200.00      \$6.00
3      Main Street      120      08/2005      \$302.00      \$285.00      \$17.00
4      Main Street      123      02/2004      \$244.00      \$200.00      \$44.00
5      Main Street      130      07/2003      \$200.00
5      Main Street      130      12/2004      \$285.00      \$200.00      \$85.00
0

Commented:
nice query. how do you know when the house changes?
should you join a and b on Account ?

0

Author Commented:
Datrias...

Any additional ideas how to fix it?

EEH
0

Commented:

0

Commented:
create a function:

Function GetChange(acc As Integer, ddate As Date) As Double
On Error GoTo ERR

Dim s As String

s = "SELECT mytable.account, mytable.SalesDate, mytable.SalesPrice FROM mytable WHERE (((mytable.account)=" & acc & ")) order by salesdatE;"

r.MoveFirst

If r.RecordCount > 1 Then

r.MoveFirst
Do While ddate > r("salesdate")
r.MoveNext
Loop

r.MovePrevious
GetChange = r("SALESPRICE")

Else
GetChange = r("salesprice")
End If

XIT:
Exit Function

ERR:
GETCHNAGE = 0
Resume XIT
End Function

create a Query :
SELECT mytable.account, mytable.Address, mytable.HouseNumber, mytable.SalesDate, mytable.SalesPrice, [salesprice]-GetChange([ACCOUNT],[SALESDATE]) AS Change
FROM mytable
ORDER BY mytable.account, mytable.SalesDate;

run the query

note: you need to use proper dates
0

Commented:
this solution would not be as elegant or as efficent as GRayL.
0

Commented:
the first record for each new account should not have a previous sales price.
0

Author Commented:
Datrias,

I tried yours... all it does it "repeating" the sales price.   Maybe I did something wrong...

GRayL:
That is simply perfect!!!   Thanks so much for this solution.

EEH
0

Author Commented:
GRayL:

... closed it a bit too quickly... again, it works great.  I was just wondering if the "blank cells" (where new account was found, so "SalesPrice" = null  ... hence, "Change" = 0.

Could they be replaced with "\$0.00"???

EEH
0

Commented:
SalesPrice is never null,  occassionally PrevSalesPrice is null when it is the first price.  Its is far more indicative of reality to leave a previous sales price empty, indicating there isn't one, rather than say it is \$0.00.

However, I agree Change could be 0 so try this:

SELECT a.account, a.Address, a.HouseNumber, a.SalesDate, a.SalesPrice,
(SELECT Last(b.SalesPrice) FROM myTable AS b WHERE b.SaleDate<a.SalesDate and b.account=a.account) as PrevSalesPrice,
Format(Nz(a.SalesPrice - PrevSalesPrice,0),"Currency") AS Change
FROM myTable AS a ORDER BY a.account, a.SalesDate;

0

Author Commented:
GRayL:

I agree... the results are fantastic.

Thanks again,

EEH
0

Commented:
0

Commented:
ExpExchHelp:
<I tried yours... all it does it "repeating" the sales price.   Maybe I did something wrong...>

possibly.

I would go with GRayL solution. Its much more efficient.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.