Solved

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

Posted on 2006-06-30
17
453 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 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