UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Ranges - If In, Count It; If Out, Don't, Access 2003    
 
   
TinyGiant2010
post Jul 20 2017, 09:31 AM
Post#1



Posts: 158
Joined: 1-June 10



I have a form (from one table) that helps me track property sales. A property will either be in "A" status for available, or in something "Not A" when it is either sold, expired, or withdrawn from market. The property will be listed for sale on a certain date called "listed" .

What I want to be able to do is look at certain market snapshots based on a certain date and/or date range. For instance today 7/20/17; I may want to know what properties were in "A" status in March; but the problem is that properties that were "A" in March have been marked as "S" in April.

Each status designation; E, W, S, has a date associated with it; as does the original "listed" date. The listed date is the day the home comes onto the market for sale. A property can only have one "status" designation associated with it.

So, for me to examine "A" properties in March, I need to make sure that all the other listed dates are

#1 GREATER THAN ([listed]-1) and
#2 [Enter relevant range of dates for examination] as a query item, and
#3 perform some sort of evaluation to make sure that even though a property may be in E status today; over the relevant range it was in A status and therefore should be counted.

To make things probably impossible (due to my lack of forethought back in 1995) is the fact that a single home can transition from Active, to Expired, then to Active again. So if a property came on the market as Active in March, Expires in April, is dormant for 30 days, becomes Active again in June and at different prices; I don't think these dates or prices can be evaluated properly. (FWIW, each property has a "starting price" and a "current price" and when prices change, the "current price" value gets changed with no real method to track the change date, other than an annotation in a memo field)

My goal of all this is to be able to answer correctly, based on my collected data, this question: What was the median price of homes on the market in March; and how do those median prices trend from Date 1 thru Date 2? (March isn't a magic month, just one I selected randomly.)

I'm pretty sure I've shot myself in the foot on this one.

Today it is easy enough to retrieve all Active properties based on their STATUS and not the date, sort, then find the Median and record it. I'm already recording counts on a daily basis, so I guess I could just add median data as I go.

Sorry - as I read this I believe I don't have the right variables to solve this problem. I'm guessing I should have taken at least one DB class! lol. Anyway, live and learn the painful way. I thought I'd toss this one out to you guys anyway, in case I'm missing something.

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
BruceM
post Jul 20 2017, 09:47 AM
Post#2


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


It is difficult to suggest anything without a clearer idea of the database structure. However, if you are keeping all of the data you describe in a single table, and you do not have related records of status changes over time, you have a situation for which there is likely no single table solution.

QUOTE
find the Median and record it

That is not likely to be a sound strategy. The median is a calculation, and as such should not be stored, but rather calculated on the fly. There are some circumstances where storing a calculation result is necessary, but if so the difficult part is not storing the calculation result, but rather assuring it is recalculated when the inputs change.

It may help to review some information about how to build databases best suited to achieving what you need.
Go to the top of the page
 
doctor9
post Jul 20 2017, 10:24 AM
Post#3


UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin


TinyGiant2010,

> For instance today 7/20/17; I may want to know what properties were in "A" status in March; but the problem is that properties that were "A" in March have been marked as "S" in April.

Here's a basic concept to get you started:

tblStatuses
StatusID [Autonumber, Primary Key]
strStatus (4 records: "Listed", "Sold", "Expired", "Withdrawn". "Available" is redundant to "Listed" I think, as they both occur at the same time, don't they?)

tblPropertyStatuses
PropertyStatusID [Autonumber, Primary Key]
lngPropertyID - Foreign key to the primary key of the table of properties
lngStatusID - Foreign key to tblStatuses.StatusID]
dteEffectiveDate - The date that this status took effect

Now you can query what a property's status was on any given date. Let's say a property has a "Listed" record for 2/1/17 and a "Sold" record for 5/1/17, then it was available between those dates. The key to finding the most recent status is to use the DMax() function on the dteEffectiveDate value, with a criteria of "<=The date I am checking".

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
TinyGiant2010
post Jul 20 2017, 01:08 PM
Post#4



Posts: 158
Joined: 1-June 10



Dear Bruce,
Thanks for helping. Although I have a clumsy method to begin, which is better than nothing, here's what I've just started to do:

I examine my table (named FREIS) that contains nothing but properties in A status. There are only 140 records in this table, so I simply select the column with current price and sort low-high. I jump to record #70 and look at the price of that property.
Next, I open another table (named InvCounts), enter a new record where I record today's date, the number of records in total, and the price value from record #70. Over time, this information will be useful but only from today forward.

I could not agree with you more completely that an "on the fly" calculation is perferred; and this is the solution I am wondering if I can achieve. This is the fundamental reason I posted here.

I will be sure to review your link for guidance so hopefully I won't goof again. All and all, considering I've only used F1, a smattering of Access books over the years, and online resources such as UtterAccess; I think I've done okay for myself.

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
TinyGiant2010
post Jul 20 2017, 01:33 PM
Post#5



Posts: 158
Joined: 1-June 10



Hello Dennis,
Thanks for your attention to my predicament(s).

In the interests of clarity, I have only one table that contains all of this information regarding dates and status. Although there are folks that would look at the field "status" and determine that since I can only have A, S, E, W, (and a few more-irrelevant really) for codes, that a separate "status" table should have been invented to populate the results in the Status field. I understand that, but I did not do it.

When a property goes into the database, it's original status=A; as there can be no other logical option for the initial state of this record. When events occur that render the status=A info incorrect, I just change the value of A to whatever it needs to be. I also record this action in a memo field should I need to remember what I did, when I did it, and sometimes why I did it. Perhaps this would have been more efficient to capture with a different table and an attached subform; but this isn't the way I did it.

Status is a basic Text Field
All dates for Listed, S, W, E (and irrelevant others) are Date/Time fields m/d/yy format. The field named "Listed" would be the implied origination date for A=Active properties; namely those available for sale. It is not redundant per-se, because that would imply listed 2x when it is only 1x.
There is only one record created per listing. Over it's "life" it can experience many changes to the record for a variety of reasons, but it is still one record. The old record of A isn't closed, then a new record created for the same property now S (sold). So your advice of analyzing a Listed record and Sold record as 2 separate entities really don't fit with the data I have to work with. I wish it did.

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
BruceM
post Jul 20 2017, 02:48 PM
Post#6


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Doug Steele (UtterAccess MVP) posted something about this here. Look for the October 2005 article.

There are also some articles listed in one of the replies https://answers.microsoft.com/en-us/office/...de-0e22d41935a6. Doug's link is included in this link.

Simple version would be a TOP 50% query, sorted to put the midpoint value at the top, then do a TOP 1 (or Max) on that query. However, there could be some vagueness about the result as the TOP 50% could include ties, and other related considerations. You can sort on the value, then the PK as one way to do a tie-breaker, but I haven't done enough investigation to know the details. This is pretty much off the top of my head.
Go to the top of the page
 
doctor9
post Jul 21 2017, 08:59 AM
Post#7


UtterAccess Editor
Posts: 17,902
Joined: 29-March 05
From: Wisconsin


TinyGiant2010,

It sounds like you don't want my advice, so I'm bowing out of the discussion. Just to be clear:

> I may want to know what properties were in "A" status in March;
> There is only one record created per listing. Over it's "life" it can experience many changes to the record for a variety of reasons, but it is still one record.

You need to pick one of these. You can't have both. If you want to know the status in March, you cannot have just one record where you change a stored value. You need to store each change as separate records.

Good luck,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 06:25 AM