Hints as to how to set up a form/report where input values affect displayed records?

[Sydcomebak]

Thanks to the contributions of experts on this site, the database I have designed is running without a hitch and my dad could not be happier in his real estate business! Thank God we got him off of those 200+ tabbed Excel spreadsheets!

What I'm trying to do now is set up a form where someone can walk in and say what model of house they own, lot type, and the square footage, and the DB will spit out all homes that are close to their house in recent sales.

That way the potential seller will have an idea of what their house is worth.

My initial thought was a form that generates a report. I'd like to be able to set a few parameters that can be adjusted if more results are needed.

To me, this sounds like something that is an everday Access request, but maybe not.

Can anyone point me toward a tutorial or page that addresses this kind of input/output function?

 

[jdraw]

Please show us a jpg of your tables and relationships. Based on your database design, there may be options to select properties based on parameters. However, the flexibility of such queries really depends on the underlying yables and relationships.

 

[Simon_MT]

Essentially you want to create mutli-dimensional search Criteria:

Function ClientsDialogue_Criteria()
    With CodeContextObject
            ClientsDialogue_Criteria = "[Client Category] = '" & .[Cat1] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' or [Client Category] = '" & .[Cat2] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' or [Client Category] = '" & .[Cat3] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "'"
    End With
End Function

Then you open your Form:

Function ClientsDialogue_ClientsEntry()
    With CodeContextObject
            DoCmd.OpenForm "Clients Details", , , ClientsDialogue_Criteria
    End With
End Function

Simon

 

[Pat Hartman]

You're going to need to develop some kind of fuzzy search. Add 1 to a counter for each match such as fireplace, hot tub, pool, air conditioning, etc. Then sort the results descending by the number of matches to get the best matches.

 

[gemma-the-husky]

what you are asking for is quite tricky

you have a few different parameters

size - lets say you input 2000 sq feet. your search might have a variable parameter to select all houses with +-10% difference. you could easily arrange to vary that parameter

sales price - relatively easy again

lot type (house type) - much more tricky. size is just a scalar - but lot type is discrete values. ie 2 bed, 3 bed, 4 bed, single floor, condo, appartment, detached (not sure of all the US terms ) - but this is harder. you can search for an exact match of the type, but not easily find a similar type, as the lot type values are not as easy to manipulate in a similar fashion

location - might be another tricky one.

then you need to put all these together, and pick just some of these parameters, but not necessarily all.

this is all quite a complex programming task.


-----
just another thought.

you can index a field such as property size, which will return results more quickly.

if you have a field like "lot type" though, then you an index may not be so useful. obviously it helps to find properties with a matching type. but if you need to find several different types, you may end up having to examine all records in the table.

it's probably a matter of trying different things until you get a satisfactory user experience

 

[Sydcomebak]

I have attached a few pics so you can see where I'm coming from:

On the home form, you enter 2 pieces of info - Square footage, and whether the house has a garage or carport from the dropdown.

The square footage is a looooong formula that I have calculated in the attached image of the query. I also added a filter, but it's a static filter. I will want this to be more manageable.

The flow:

Master sheet: Enter footage and carport / garage
2nd form: Displays all homes that fit the criteria. Here the user selects the homes that they want to send to the report for calculations (Avg price, etc...
Report: Displays some info from each selected house for printing what the potential seller should list their house for.

I am now submitting to see how the attached images look...
001: The front page entry form
002: Some table structure
003: The current query
004: Query results

 

[Sydcomebak]

The attached file is a REALLY basic idea of what the 2nd page will look like where you select the homes to compare on the report.

 

[gemma-the-husky]

i think the models table is not normalised - is this holding data for each room in the house? if so, this should be constructed differently. not sure what other fields you have in the "house" table. i would probably put the garage and carports as "rooms" in the models table.

this is a lot of data to setup though. is this normal?

surely estate agents just record the number of bedrooms with/without ensuites, number of receptions/ number of garages etc etc. and total house area / total plot area.

 

[Sydcomebak]

Gemma (Dave):

Nope, this is how it is done. The models set base values for what roomsare in the house and their size. Houses are models, but they can be stretched in various places thus altering room dimensions and total square footage. Also, models don't have garages or carports, that is an add-on to the house when it is built.

Example:
- Model: Antigua - 1800SqFt w/carport
- Model: Antigua - 1848SqFt w/Garage - Length Stretch 16" affecting Master Bdrm, 2nd Bedroom and living room
- Model: Antigua - 1824SqFt w/carport - Width Stretch 20" affecting Master Bdrm, Den.

 

[Sydcomebak]

If I add a "Compare" checkbox to my listings table, I could onClick test all of the listing IDs to see if they match the criteria given in the first form. If so, I can set the "Compare" to true. In the 2nd form where you narrow down the values, I can then remove some compares that are statistical outliers... Hmmm.... Thoughts?