Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2006-06-30
17
Medium Priority
?
499 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
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

585 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