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

Connie

Even though it's the 4th of July and my wife would kill me if she knew I snuck
into my office to check email, I'm going to step in a bit here.

You are becoming quite the relational DB developer if you recognize Multi-value
fields as not falling into the rules of normalization.

These fields are actually stored in a system table that you cannot get to. MS
came up with this garbage to please the unwashed masses. No serious developer
would use them. It would be like saying lookup fields are a good thing. (see our
twenty-something rules of development in our files folder.

That said, I'm out of here for the rest of the day. Time to enjoy the family and
celebrate our freedom from oppression.

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
MS Access MVP
<https://mvp.support.microsoft.com/profile/Bill.Mosca>
https://mvp.support.microsoft.com/profile/Bill.Mosca

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, July 04, 2012 5:02 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

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
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> Sent: Tuesday, July 03, 2012 11:45 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
>
>
>
>
>
> 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>
> <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%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 11:11 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> > <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%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> > <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]
>

[Non-text portions of this message have been removed]