Yet Another Query Question

[merc007]

Ok, let me see if i can explain this. I am creating a real estate database. When a house comes on the market we assign it a "List Date". Then we take the "Current Date" and subtract it from the "List Date" which gives us "Days On Market" (ex. List Date: 5/10/2006, Current Date: 5/12/2006, Days On Market = 2) That part is easy, however, we need to be able to pull up properties based on a specific date. (Ex. Using the example above, if I want to see all the properties that are currently on the market from 5/11/2006 to now, I want all the ones that were listed on or after that date that are still on the market. (Ex. I listed a property on 2/2/2006 and it has not sold, ie. it is still on the market. I want to run a query that says, "show me all the properties that were on the market on 3/5/2006" Based off that query I would want the property to show up. However, if I say, "show me all the properties that were on the market on 2/1/2006" then based off of that query it would not come up. So far, I am having the time of my life trying to create this. I would appreciate any help on this... Thank you very much.

 

[Golom]

Seems that you need a [SoldDate] field as well. Otherwise, how do you tell if it's still on the market?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[merc007]

I neglected to put that in the examples above... I do have a Sold Category

 

[Golom]

Select ... Whatever fields you need ...

From myTable

Where [List Date] >= [Enter Earliest List Date]
  AND [SoldDate] IS NULL

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

So find all the records where list date is greater than some date you enter and it's not sold?

Something like:


SELECT FieldsYouWant WHERE ListDate > [Enter Date] AND SoldCategory = 'Not Sold'

 

[PHV]

WHERE ListDate <= [Enter on the market date]
AND (SoldDate IS NULL OR SoldDate >= [Enter on the market date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[merc007]

This is where it gets a little tricky. If a property sold today then "Sold" will no longer be null... And the problem with that is when I run a query for a date older than today, it won't show up when I need it to show up. Even though it is off the market today, it was on the market a week ago. (Ex. My boss comes to me and says, show me all the houses that were on the market 5/5/04. I run the query and it shows all the houses. Now those houses may have sold since then, but I still need to show them in that query.)

 

[lespaul]

then you would need to store the SOLD DATE in order to determine if it was still on the market on the requested date.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Seems you haven't tried my suggestion ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[merc007]

To PHV: Where would I put this statement?

 

[PHV]

What is your actual SQL code ?

 

[lespaul]

PHV - we don't even know if there's a sold date in the table yet!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Leslie:

http://www.tek-tips.com/viewthread.cfm?qid=1229846&page=1

 

[lespaul]

nothing like information spread all over the place, huh!

les

 

[merc007]

Sorry for the delay, here is the SQL Code I have so far

SELECT tblHouse.House, tblHouse.ListDate, tblHouse.SoldDate, tblHouse.CurrentDate, IIf(IsNull([SoldDate]),[CurrentDate]-[ListDate],[SoldDate]-[ListDate]) AS OnMarket
FROM tblHouse;

 

[PHV]

And you wanted this ?
SELECT House, ListDate, SoldDate, CurrentDate, IIf(IsNull([SoldDate]),[CurrentDate]-[ListDate],[SoldDate]-[ListDate]) AS OnMarket
WHERE ListDate <= [Enter on the market date]
AND (SoldDate IS NULL OR SoldDate >= [Enter on the market date])
FROM tblHouse

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo (bad copy/pasting ...)
SELECT House, ListDate, SoldDate, CurrentDate, IIf(IsNull([SoldDate]),[CurrentDate]-[ListDate],[SoldDate]-[ListDate]) AS OnMarket
FROM tblHouse
WHERE ListDate <= [Enter on the market date]
AND (SoldDate IS NULL OR SoldDate >= [Enter on the market date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886