Dear Tony:
Databases are designed to work for information that is structured in a
specific way. You are right on top of one of the major elements of
this structure. Simply stated, there is a very good, easy way to do
what you want, but your current structure is wrong.
You have multiple columns (fields) of the same kind of information,
and a potential that the number of values possible will grow. What
you need is to create another table for these values. Start by
identifying the Primary Key of your current table, and put these same
columns in the new table. Add a column that counts the elements as
they are added to the new table (like the 3 digit number you currently
put on the end of your ABCXXX column names. These columns become the
primary key of your new table. Add the ABC column to this table.
Create a relationship on the new table to the existing table on the
columns of the Primary Key to your existing table.
You can now record as many ABC values as allowed by the column you
added for counting them. If you use a long integer, you should never
run out.
If the values in ABC are unique you can do without the counting
column. Make ABC part of the Primary Key to the new table instead.
You now have virtually unlimited ability to enter as many ABC values
as are needed without ever needing to add columns to the original
table. Aggregate function can now count, sum, average, etc. on the
values.
Nothing like the ABC* wildcard you propose exists.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
