Solved

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

Posted on 2006-06-30
17
442 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How Can I Grab This Information off a PDF Form 23 48
Access query expression 6 20
Trying to open FORM in specific record !! 6 44
Access Update Query 1 20
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

776 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