Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2021
    Location
    Calgary Alberta Canada
    Posts
    3

    Hello from Calgary Alberta Canada

    Hello Access Forum Members!

    I am a Realtor in Calgary that uses Access at a very basic level to slice and dice home sales data for the Real Estate Market in Calgary.
    Because I work with a lot of fellow Real Estate Investors, I'm always coming up with new ideas that might help us.

    My latest project is to create a list of recent flips in Calgary that had a large difference in the Before and After Sale Prices in a short Time Frame.
    I have the data in one Access table, one query to find duplicates between dates. Sorted by address, dates, sold prices.

    MY PROBLEM is I then have to use Excel to calculate the Date Differences and Sold Price Differences.

    Is there a way to do that in Access that some one can help me with?

    Here is a snapshot of an access report that shows Property Address, Community, Sold Dates, Sold Prices, MLS#, Size, SoldPricePerSqFt

    Click image for larger version. 

Name:	2021-12-04_7-53-43.jpg 
Views:	13 
Size:	131.1 KB 
ID:	46825

    This is a snapshot from the excel file I used to answer the questions: What was the Difference Between Sold Prices, Sold Dates and Sold Price Diff / Months = Per Month

    Click image for larger version. 

Name:	2021-12-04_8-14-10.jpg 
Views:	13 
Size:	142.6 KB 
ID:	46826

    Sorry if this post is a bit of a ramble - I usually don't ask for help. I just muddle away until I solve things myself or shelve it until later if ever....



    Buy if anyone can get a sense of what I'm trying to do and can help or point me in the proper direction it would be most appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A calculated textbox on your report might be what you want: = ListPrice-SoldPrice or create a calculated field in your report query in the same manner (you'd drop the = sign).
    Doing this in your query would make the calculations available to any other form/report so that's where I'd do this. To get months between list and sell use DateDiff function. Access has a lot of functions available to do the simpler calculations. However, I might be wrong about the price calc because I don't really understand some of this
    What was the Difference Between Sold Prices, Sold Dates and Sold Price Diff / Months = Per Month
    A house can't have 2 sold prices for 1 listing, no? So is that the difference between 2 different listings?

    EDIT - after a closer look (showing all of the headers would have been better) I think you want the differences between 2 listings for the same address. For that, you'd need a subquery: http://allenbrowne.com/subquery-01.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Dec 2021
    Location
    Calgary Alberta Canada
    Posts
    3
    Here is the Query - the MLS # is the Unique ID
    (MLS#'s are different transactions, each time a property gets listed, it gets a New MLS# assigned from the Real Estate Board)

    So my Sold Dates and Sold Prices are in the same columns.... but different records by MLS#

    Click image for larger version. 

Name:	2021-12-04_9-14-05.jpg 
Views:	10 
Size:	294.5 KB 
ID:	46827

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might be easier if you GroupBy in the report on Address and use calculated controls for your calculations in the group footer. Hopefully all address data is correct (i.e. there are no misspellings for the same address). A table that only allows an address to be recorded once and whose PK field would be the FK field in the data you show might be safer. Or you can try the sub query route as I mentioned.
    Last edited by Micron; 12-04-2021 at 10:46 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jerry,

    Please have a look at the attached sample that illustrates how to use a subquery to get the differences between consecutive records.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Join Date
    Dec 2021
    Location
    Calgary Alberta Canada
    Posts
    3
    Thank You!!

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Happy canada day
    By CHEECO in forum General Chat
    Replies: 0
    Last Post: 07-01-2016, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums