best way to build this query (and form)?

[Jerome]

Hi,

I'm looking for the best way to build a certain query.

Each record has a field containing several codes, like this for example:
V6,F14,VIP,Y2,Y6,Y7,
or V841,B,
or F12,S1,A,
etc

Now I'd like to be able to say: SELECT all the records that include "V*"
and "Y*" but not "B*".

I've tried this with several form fields and then using the values of
these form fields in the criteria field of the query to include or
exclude them, but that's not very elegant and gets too complicated and
too long at a certain point. Since I may need 8 or 10 include codes and
5 exclude codes for example ...

What would be very cool is if I could use one field like this as
selection criteria: +v +y -b

But I'm not sure if that's feasible? Or is there an other way to do this
whole stuff?

Any help is greatly appreciated,

Jerome

 

[Jeff Boyce]

Jerome

While it will likely be possible to do what you are asking for (there are a
lot of VERY creative folks reading these posts), I wish to point out that
you are making your task much HARDER than it needs to be.

Putting multiple values in a single field violates one of the basic
principles of relational database design ("one fact, one field"). By
storing multiple facts in a single field, you create a situation that
requires considerable programming to work around (much like yours!).

Access is a relational database, and provides an easy mechanism (OK, so the
concepts are tough, but the tool makes the implementation easier) for
handling what appears to be a many-to-many relationship. One of your
"records" could have zero, one, or many "codes"; and I suspect one of your
"codes" could belong to zero, one, or many "records".

This requires three tables to resolve in Access (or other relational
databases). You didn't mention what the records store, but that "entity" is
the first table. Your list of possible codes is your second table. A third
table hold valid pairs of Table1_ID and Table2_ID.

With this design, you can easily find all, any, or any combination of
Table2_IDs (points to codes) belonging to a specific Table1_ID. Or, you
could find ALL Table1_IDs that have a specific code (Table2_ID).