John--Yes this definitely helps! Only question that pops up is how to work with multivalued criteria like Cities and Types of houses. For example, lets say they would be okay with houses in Morris or Hancock and they are looking for a two story or 1.5 story house.
I currently have LookingEvent which which connects buyers together and is created each time they are looking for a property. (A buyer can be looking for a personal home, investment property, and business property at one time so that's 3 LookingEvents). So I'll put the search criteria in that table. Seems a lot easier this way than the other way.
thanks!
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
>
>
> Each "search" is a complete entity, correct? Then rather than save the search
> criteria as individual items, save it as a single row that has values entered in
> columns for each criteria. The "search" row should be linked back to the buyer
> who has those criteria. When a new property is entered as a listing, you can
> perform a "search" using the saved buyer criteria to come up with buyers that
> might match. Note that the match doesn't need to be 100%. You could mark
> certain criteria as critical. For example, if buyers are searching for a 3
> bedroom house, no sense getting a "match" on 1 bedroom even though all the other
> criteria match. But number of bathrooms could be a "maybe". Buyer wants 2
> bathrooms, but a match on 1 or 3 bathrooms would be a "maybe" if all the other
> criteria are good. And price could have some flexibility. For example, if
> someone says they don't want to spend more than 250K, you should still come up
> with a "maybe" on houses newly listed at up to some percentage above that.
>
>
>
> Does that help?
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, July 03, 2012 8:26 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Save from form to table where each item is 1 record;
> multiselect unbound boxes
>
>
>
>
>
> Hi All!
> I am working on creating a form where the parameters that a buyer has can be
> entered. A couple of criteria can have several items in one category (several
> cities, several types of houses) and the rest have one response (such as min
> beds, etc). Those criteria that have several items are selected in a multiselect
> box. Right now the boxes are unbound and are working to the point that a button
> can be clicked and it creates a filter which is then applied to another form so
> that all the houses meeting the parameters are displayed.
>
> ***Now I need to change it so that the entries are saved to a table.
>
> The bigger picture is that the next project will be having Access look up buyers
> that match a new house when it's entered. i.e. Right now I'm working on finding
> houses when a buyer is entered, and next I'd like to find buyers when a house is
> entered.
>
> As it stands now the house features are entered into one line on the Listings
> table. The buyers parameters are supposed to be entered into the SearchParams
> table with one record per parameter.
>
> Graham had given me input back in March but has not responded to current
> questions, so I'm reposting this. I am fine if I have to redo because you want
> to do it a different way.
>
> Info is below.
>
> What would I do without you!! XOX ;-)
> Connie
>
> Here are the tables I currently have:
>
> SearchCriteria
> ==============
> CriteriaID (autonumber)
> CriteriaName (unique text)(min Bedrooms, max Bedrooms, min bathrooms, max
> bathrooms, rural, city, housetype, etc.)
> CriteriaField (name of field to filter)(Beds, Baths, Rural, City, HouseType,
> etc.)
> CriteriaType (code for text, numeric, etc)
> Operator (comparison operator for the WHERE clause)
> AllowMulti (boolean - more than one allowed per search)
>
> My SearchCriteria records look like this (I followed Grahams advice but do not
> know how to use the AllowMulti:true portion) and since I had already created a
> search form for houses I used that code for creating a filter rather than his
> operators.
>
> ID:1 Name:MinBedrooms Field:NumBedrooms Type:Numeric Operator:>=
> AllowMulti:false
> ID:2 Name:MaxBedrooms Field:NumBedrooms Type:Numeric Operator:<=
> AllowMulti:false
> ID:3 Name:MinBathrooms Field:NumBathrooms Type:Numeric Operator:>=
> AllowMulti:false
> ID:4 Name:MaxBathrooms Field:NumBathrooms Type:Numeric Operator:<=
> AllowMulti:false
> ID:5 Name:City Field:City Type:Numeric Operator:IN
> AllowMulti:true
> ID:6 Name:HouseType Field:HouseType Type:Numeric Operator:IN
> AllowMulti:true
> (I have tables for Cities and HouseTypes with numeric
> PKs)
>
> LookingEvent
> ==============
> LookingID
> BeginDate
> EndDate
> Agent
> BuyerType
> Notes
> SaleID
> SearchParams (a junction table joining the above tables)
> ============
> LookingID (foreign key to LookingEvent table)
> CriteriaID (Foreign key to SearchCriteria table)
> ParamValue (text)
> Listings (where the info re house parameters resides)
> ============
> ListID
> ..
> Price
> Beds
> Baths
> Rural
> HouseTypeID
>
> The Price will have to be gotten from a query.
>
> Graham had said the following but I don't know how to save from a form to a
> table where each item is one record and where some data is entered into a Multi
> select box. I'm leaving my questions regarding the last paragraph until I've
> been able to save the parameters.
> When you save a search, create a Searches record and then create as many
> related SearchParams records as required. For example, if the search
> specifies at least 2 bedrooms, housetypes 1, 3 or 4 and cities 48 or 56,
> then (assuming SearchID is 1) your SearchParams records would look like
> this:
>
> 1 1 2
> 1 5 48
> 1 5 56
> 1 6 1
> 1 6 3
> 1 6 4
>
> Note that the values in the last five records correspond to the selected
> items in the Cities and HouseTypes listboxes.
>
> Similarly, when you load a saved search, you can read the SearchParams
> records and set/select values in textboxes/listboxes. You could facilitate
> this by setting the Tag property of the textbox/listbox to the corresponding
> SearchID value.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar