IN function in query

  • Thread starter Thread starter tenbob
  • Start date Start date
T

tenbob

I want a text field that can contain up to four characters ____

then I want an SQL that will determine if a particular character is in
that field. for example. if it has ABMR and another record
has MR I want to select all records that have an M or R.
 
tenbob said:
I want a text field that can contain up to four characters ____

then I want an SQL that will determine if a particular character is in
that field. for example. if it has ABMR and another record
has MR I want to select all records that have an M or R.
Click to expand...

Sheesh, do you have to? Is that imported data? If it is your own
structure, consider splitting that field into a subtable.

But if you must: I hope the criteria 'record' is fixed length, otherwise
it is close to impossible to figure out a decent expression.

In your sample:

instr(datafield,left(criteriafield,1))+instr(datafield,mid(criteriafield,2,1))
0
Click to expand...

will return True for a match. [datafield] should become the name of
the field with the ABMR value, [criteriafield] should be the MR type.

This works because instr returns 0 for not found, and a positive value
when found. If you add a lot of zeroes and one positive value, you will
get a positive value (thus True); if you just add a lot of zeroes, it
will remain zero (and thus False)
 
Why?

Why do you have a field "that can contain up to four characters"? If you
have a one-to-many relationship ("up to four characters"), consider
normalizing your data further. You'll find it much easier to query a single
character field to see if it has "M" or "R".
 
I may be looking at this the hard way but this is my point.

The purpose of doing it my way is to select combinations of parameter
of an entry: For example

A=Permanent customer
B=Summer Only
C=Winter Only
D=Eastern US
E=Western US
G=Under 65 years old
H=Over 65 years old
M=Male
F=Female

These are just imaginary parameters but in this case, I might want t
select Males, under 65 in the Western USA There code would be MDG
If it is also a summer only person, Add the B to get MDGB OR BDGM
etc
So the test would be Select where IN = M and B and D and B

It is not a fixed field because there could be new parameters and
might not care if it is East or Wes
 
I may be looking at this the hard way but this is my point.

The purpose of doing it my way is to select combinations of parameter
of an entry: For example

A=Permanent customer
B=Summer Only
C=Winter Only
D=Eastern US
E=Western US
G=Under 65 years old
H=Over 65 years old
M=Male
F=Female

These are just imaginary parameters but in this case, I might want t
select Males, under 65 in the Western USA There code would be MDG
If it is also a summer only person, Add the B to get MDGB OR BDGM
etc
So the test would be Select where IN = M and B and D and B

It is not a fixed field because there could be new parameters and
might not care if it is East or Wes
 
Hi,


That seems close to a case of competences. The scenario is that you
have a list of employee with their competences. Table Skills, fields
Employee, Skill:

Joe TechnicalWriter
Joe ReadEnglish
Joe WriteEnglish
Joe ReadFrench
Joe ReadSpanish
Mary ReadEnglish
Mary WriteEnglish
Mary ReadFrench
Mary WriteFrench
.... ...

(Index no duplicated on the group (Employee, Skill) )


and then, you look for employee that have ALL from a set of competence,
stored in a table, RequiredSkills, one field, Skill:


WriteEnglish
ReadSpanish

(no duplicated values)


A possible solution is then:

SELECT Employee
FROM Skills As s INNER JOIN RequiredSkills As r
ON s.skill = r.skill
GROUP BY Employee
HAVING COUNT(*)=(SELECT COUNT(*) FROM RequiredSkills)

(see also http://www.mvps.org/access/queries/qry0016.htm)


Here, with just the data shown, that will list Joe.



Indeed, it is far easier to add or remove data in a design where on quality
is stored by record, just add or delete a record, than in a string: try to
remove the "G", or the "A" in "BGMD". Try to append a "G", or a "A". Now,
do the same with a normalized design, much easier, isn't it. And finally,
make a research. The normalized approach can use indexes, while the
"personal encoding" like "BGMD" just can't ! Hey, it is your application,
use what you want, I am just supplying alternative idea in how to make the
stuff... in my opinion, easier, overall.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top