Solved

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

Posted on 2006-06-30
17
418 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now