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
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
let me know what kind of help u need
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;
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;
ASKER
GRayL:
I tried this... the outcome looks like this:
something doesn't add up...
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
I tried this... the outcome looks like this:
something doesn't add up...
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
nice query. how do you know when the house changes?
should you join a and b on Account ?
should you join a and b on Account ?
ASKER
Datrias...
are you "asking" GRayL???
Any additional ideas how to fix it?
EEH
are you "asking" GRayL???
Any additional ideas how to fix it?
EEH
Yes I was asking GRayl.
create a function:
Function GetChange(acc As Integer, ddate As Date) As Double
On Error GoTo ERR
Dim r As New ADODB.Recordset
Dim s As String
s = "SELECT mytable.account, mytable.SalesDate, mytable.SalesPrice FROM mytable WHERE (((mytable.account)=" & acc & ")) order by salesdatE;"
r.Open s, CurrentProject.Connection,
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([AC
FROM mytable
ORDER BY mytable.account, mytable.SalesDate;
run the query
note: you need to use proper dates
Function GetChange(acc As Integer, ddate As Date) As Double
On Error GoTo ERR
Dim r As New ADODB.Recordset
Dim s As String
s = "SELECT mytable.account, mytable.SalesDate, mytable.SalesPrice FROM mytable WHERE (((mytable.account)=" & acc & ")) order by salesdatE;"
r.Open s, CurrentProject.Connection,
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([AC
FROM mytable
ORDER BY mytable.account, mytable.SalesDate;
run the query
note: you need to use proper dates
this solution would not be as elegant or as efficent as GRayL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the first record for each new account should not have a previous sales price.
ASKER
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
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
ASKER
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
... 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
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),"Currenc
FROM myTable AS a ORDER BY a.account, a.SalesDate;
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),"Currenc
FROM myTable AS a ORDER BY a.account, a.SalesDate;
ASKER
GRayL:
I agree... the results are fantastic.
Thanks again,
EEH
I agree... the results are fantastic.
Thanks again,
EEH
Thanks, glad I could help.
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.
<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.
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.