ACCESS FEED: Re: [MS_AccessPros] Save from form to table where each item is 1 record; multiselect unbound boxes

John,

It seems like having several values in a field goes against normalization rules. Is there a benefit to this or reason to go this route?

Also, our goal is to make it obvious to the users what to do so I'd prefer to have a way for them to enter the multi data in a way that they've seen on other forms. I'd like to use a multiselect box but that seems inherently problematic (especially with repopulating it). So i'm wondering about using the table idea that graham suggested for house types and cities. Then use subforms ( continuous or data form) for the cities and house type lists. Do you see problems with this approach?

I really appreciate your input--Thanks!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
>
>
> Tell the users they can enter a list of values:
>
>
>
> Hancock, Morris
>
>
>
> You probably don't want to train them to select "IN" as the operator, so your
> code will have to accept "=" and convert it to IN when you see a list of values.
> Will probably need some front-end validating to ensure the user is entering
> something you can later parse into a real predicate.
>
>
>
> 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 11:45 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
> record; multiselect unbound boxes
>
>
>
>
>
> How would I enter into the table with the saved search that they want a house in
> Hancock or Morris? Don't really want to have a field for each city as the cities
> that we service could change.
>
> Thanks John!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Connie-
> >
> >
> >
> > The secret is in the "comparison" operator. When multiple criteria apply (per
> > your example for cities and number of stories), use IN as the operator and
> > provide a list of acceptable values. If they want exactly 2 bathrooms, then
> the
> > entry is:
> >
> >
> >
> > NoBathrooms = 2
> >
> >
> >
> > If they're happy with 2 or more bathrooms, then:
> >
> >
> >
> > NoBathrooms >= 2
> >
> >
> >
> > If they want 2 or 3 bathrooms, then:
> >
> >
> >
> > NoBathrooms IN 2, 3
> >
> >
> >
> > Capiche?
> >
> >
> >
> > You would have to write code to assemble the appropriate filter given the
> field
> > name, the comparison, and the value.
> >
> >
> >
> > 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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > Sent: Tuesday, July 03, 2012 11:11 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Save from form to table where each item is 1
> > record; multiselect unbound boxes
> >
> >
> >
> >
> >
> > 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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > Sent: Tuesday, July 03, 2012 8:26 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.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]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>