Solved

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

Posted on 2006-06-30
17
475 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:ExpExchHelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 5
17 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018341
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018396
let me know what kind of help u need
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17018416
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:ExpExchHelp
ID: 17018475
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
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018497
nice query. how do you know when the house changes?
should you join a and b on Account ?

0
 

Author Comment

by:ExpExchHelp
ID: 17018824
Datrias...

are you "asking" GRayL???

Any additional ideas how to fix it?

EEH
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018865
Yes I was asking GRayl.

0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018878
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, adOpenKeyset, adLockReadOnly

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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17018926
this solution would not be as elegant or as efficent as GRayL.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 17018962
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17018969
the first record for each new account should not have a previous sales price.
0
 

Author Comment

by:ExpExchHelp
ID: 17019342
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 Comment

by:ExpExchHelp
ID: 17019366
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17019729
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 Comment

by:ExpExchHelp
ID: 17019933
GRayL:

I agree... the results are fantastic.

Thanks again,

EEH
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17020219
Thanks, glad I could help.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17029467
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

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question