Queries- Running/Display Results?

[eyal8r]

Hey guys-
I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this-
I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another...
Thanks!

 

[pbaldy]

Typically you wouldn't store a calculated number, however if you wanted a history you might have to. It would depend on your table structure. In your case, a query that restricted by area and averaged the values sounds like what you want. From a practical standpoint, is averaging values of homes in the area accurate? It wouldn't seem so, with differing sizes, ages, how long ago the last sale was, etc.

 

[eyal8r]

Right- I would deliniate from there down. I definitely need to take into account the sf range, age, beds/bath, age, etc. Just trying to get the concept down first- then I can include all that in my queries. From a size/speed viewpoint- which is a better way to go? Run the calculations on the fly each time? Or, calculate them all at once, then yank them up in the form from the resulting cell?

 

[pbaldy]

I would run them on the fly, particularly since they'll always change. I'd have a form where I entered the criteria, and the query would use that to restrict the records used to calculate the average. I visualize a form where I enter the criteria, push a button and the matching properties are displayed below, with the average value calculated and displayed. I'm assuming a user would want to review the properties used in the calculation.

 

[eyal8r]

Ahhh- ok- makes sense. Things like +/- sf range (I could change on the fly), or other criteria... makes sense. Now, we're talking THOUSANDS of records at any given time. Does that change anything on this method?

 

[pbaldy]

Thousands that it will have to sift through to find the matching records, or thousands will match the criteria and be displayed? If the first, no; Access should run that query instantaneously. If the second, then you'd probably just want to display the calculations. It wouldn't be practical to try and display that many to a user.

 

[eyal8r]

Well- I believe it's the 2nd. Basically, there are 30,000+ records I need to apply this query to. Then, take THOSE results, and sort them Descending in a grid somewhere- so I can focus on just the top ones. But, yes, each record needs to have this query applied to it in order to determine the results. So if that's the case, you're suggesting to store the results in a table/field FIRST- then use a form to display those results?

 

[The_Doc_Man]

As a starting point, consider the things you would group together. Find a way to make them relatively short. For instance, "area/location" as a code as opposed to spelling out the name of the subdivision. A single query could then be used to group the things you use as search criteria. The shorter the fields used for group, the better.

Depending on how many of these you have, you might wish to index SOME of them in their tables. However, (1) you cannot have more than 10 indexes per table, and (2) the more indexes you have, the worse your performance gets after ANY update. But indexes make searches faster.

Your discussion reminds me of a topic we get asked about a lot. Search this forum for CASCADING COMBO BOXES as a way to refine your searches dynamically. It appears to be relevant to your description of how you want the search to be done.

In the final analysis, on a fast computer with enough memory and disk space, a mere 30,000 records is chump change. We have forum members with another digit in their record counts beyond yours. Some with TWO more digits. So I wouldn't worry TOO MUCH about performance for the raw searches.

 

[eyal8r]

Awesome- Let me ask you something here...

Should I split this DB into front/back ends? It's going to be fairly large, with 100s of thousands of records at any given time. The daily update file will be quite large as well. However, I will be the only user at any given time. Just not sure which way to go here.