Can I parameter query multiple fields in the same table?

[jharting]

I am setting up a database for our search company that is filled with
information on our clients. I need to be able to track the
organizations to which our clients are applying. This could be
multiple organizations at one time.

Then, I need to be able to list which clients have applied to a
certain company.

I have set up a dropdown list of the companies to make entering them
easier for each client. However, the only way I know to do it now is
to set up about 10 separate yet identical dropdown fields in each
client's record. then, as they apply with different companies, I
simply add the new company to the next available dropdown menu.

First, is that the best approach? Second, if so how can I set a
single parameter query that will search each of those ten separate
fields?

 

[Michel Walsh]

I would rather define a junction table, a table with basically two fields:
the clientID and the CompanyID that the client applied to.


I will use either a form-subform, either a multi-selection enabled Listbox
inside a form. The form-subform is relatively trivial, with Access. The
listbox requires more work: present all the possible companies in it, then
"select" each ones that the actually displayed client has applied to (in the
onCurrent procedure handling event). You also have to track if a record is
to be added in the junction table (if a new line in it has been selected) or
deleted (if a line has been un-selected).


Vanderghast, Access MVP

 

[John W. Vinson]

I have set up a dropdown list of the companies to make entering them
easier for each client. However, the only way I know to do it now is
to set up about 10 separate yet identical dropdown fields in each
client's record.

STOP!!!

You're "committing spreadsheet upon a database", a venial sin punishible by
being required to read up about normalization.

If each Client can be involved with several Companies, and each Company can
involve several Clients, you have a "many to many" relationship. The proper
way to handle such a relationship (and it's universal in databases!) is with
*three tables*:

Clients
ClientID
<personal information about the client>

Companies
CompanyID
<identifying information about the company>

ClientCompanies
ClientID <link to Clients, who's involved>
CompanyID <link to Companies, what company they're involved with>
<any information about this client's involvment with this company>

On a Form you would use a Subform based on ClientCompanies, with a combo box
on the subform to select the company. Rather than one company per *field* you
would have a new record for each company - so only one field needs to be
searched.

John W. Vinson [MVP]