Re: multiple field "or" criteria

From: JulieD (julied_ng_at_hctsReMoVeThIs.net.au)
Date: 03/31/04 

Date: Thu, 1 Apr 2004 00:43:09 +0800

Hi nd

from the sound of it your database isn't really "normalized" which makes
what you're trying to do quite difficult ... if, however, you're still
relatively early in the designing of your database and can make structural
changes to it then you might like to look at "normalizing" it and then what
you're after (and probably other things that you'ld like to do now & in the
future) will be a lot easier.

i'm assuming you have a table for your salespeople (let's call it
TBL_Salespeople) and you have a table of regions (let's call it TBL_Regions)
and if one salesperson can visit many regions and each region can be visited
by many salespeople then you have what is known as a "many to many"
relationship. The most effective way to store "many to many" relationships
in a database is via the use of a third table (which is known as a
"junction" or "resolver" table) this could be called TBL_SalespersonRegion
table which has fields such as:

Salesperson (which is the primarykey from TBL_Salespeople)
Region (which is the primarykey from TBL_Regions)
Date (if applicable)
and any other fields applicable to the salesperson's visit to the region

this way your salespeople can visit many regions and you can easily extract
the type of query that you mentioned.

the TBL_SalespersonRegion would also need to have a primary key which could
be a combination of Salesperson, Region & Date or you could use an ID
(autonumber) field here.

Hope this helps, let us know if you need further assistance.

Cheers
JulieD

"nd" <thebesthorizon@hotmail.com> wrote in message
news:Xns94BD75C8B4953thebesthorizonhotmai@208.33.61.208...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi i have not tried to make a db since school, so please be
> understanding
>
> is there a way for a query to search in multiple fields for a particular
> value. then return the entire record based on the value being true in
> ANY of the fields searched.
>
> i.e. - i have a db that has 3+ "geographic" fields (one sales person can
> visit up to 3 regions - each region being a field on the the table -
> visitregions1,2,or 3. i have a form that will allow the user to select
> any of the possible geographic regions - lets say regions x,y,and z - if
> the the user selects region x i want the query that uses the selection
> input to search "visitregions" fields 1,2,3 for the value x and return
> the record whether it is true in field 1,2,or3.
>
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Relevant Pages

  • Re: How to show multiple records in the same line
    ... >DISTINCT in your query should get you a single instance of each. ... JOIN (tblDisease RIGHT JOIN tblDiseaseTitle ON tblDisease.DiseaseID = ... By "database schema" do you mean the output created by Analyze / ... If you see any room for normalizing, ...
    (microsoft.public.inetserver.asp.general)
  • Re: time limit on empty fields
    ... You can then create a query to identify the records where this field is: ... the database opens. ... with salespeople not filling in all fields on the form. ... them required fields but about a third of the fields wouldnt be filled out ...
    (microsoft.public.access.forms)
  • Re: multi user
    ... sales persons clients private(no snooping eyes from other salespeople) 2) i ... have done so far is split the database for each guy with the front end on ... backends are in, backend are named after the salesperson. ... absolutely right that replication is a big bite to chew and MichKa knows it ...
    (microsoft.public.access.security)
  • Re: OOP relationships. Advice needed!! PLEASE
    ... I'd change the design of the database because currently each ... from the Companies table and add a CompanyID to the Salespeople table. ... Then a have tblSalesPerson ... If i am going to design this, is it best OOP practice to include the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Report Page Breaks
    ... I have a report that is generated from a query which identifies salespeople and the dealers that each one is associated with. ...
    (microsoft.public.access.gettingstarted)