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