multiple field "or" criteria

  • Thread starter Thread starter nd
  • Start date Start date
N

nd

**** 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
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
 
**** Post for FREE via your newsreader at post.usenet.com ****



my db's are on the web - my instructorinfo table is the "master table" that
i guess is unnormalized - any help appreciated

http://www.pfll.org/dbtesting/

thanx

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
Hi nd

i've downloaded your normalized.mdb and am having a look in the
relationships window - from the menu tools / relationships - add all your
tables, in a "well constructed" database you would expect to see joins (ie
the relationships) between the tables with a 1 & infinity symbol on them
(meaning that they're related and have referential integrity enabled).

there's a bit of work to do to get your database's structure "correct" and
i'm happy to help you sort this out but would rather assist you in reaching
your goal then doing it for you so if you would like to work together on
this a starting point would be reading of a pdf i have on my website
http://www.hcts.net.au/tipsandtricks.htm
called "Creating a Database - The Basics"
and outline what you think needs to happen and then we can look at
implementing the changes necessary.

If however, you'ld rather i just sorted it out for you please post back and
let me know.

Cheers
JulieD
 
**** Post for FREE via your newsreader at post.usenet.com ****

hi, julie

no, no i want to learn this so......i will look over your website tonight,
i see what you said about the reltionships, and it seems after a few
queries access created some relationship lines. but i am completely stuck
on the queries - the multiple regions part for each instructor(salesperson)
is stumping me - but like i said i'll read your site over and plug away at
it tonight - i have uploaded a revised db and will post next time i revise.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
Back
Top