Searching records though a multi-value look up column

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

Hi,

I am building a database of Attorneys for a small law firm. The objective is
to be able to find attorneys by their practice areas. So I created look up
column with multiple values. What can I do so that some one can search the
records using only the values I entered for the look up column.
 
The tabel and form are exactly the same as far as fields go. Table consist of
First Name, Last name, Addrress, City State, Zip, Website, Practice Areas,
and Martindale-Hubbell Peer Review Rating. the Last to fields are multie
value Look up columns. I would like to be able to have my users to run a
search of tha data base where they can only choose the pre-defined values in
the Practice areas look up column.

Thank You!
 
I'm not sure how the Table and Form can be exactly the same if you have a
Lookup column in one.

We need to check if we are both talking about the same thing.
Usually, a lookup column means that the user has created a table and allowed
Access to put a sort of feeble version of a combo box into the table so
that, instead of seeing the Primary Key field of a linked table eg PractID -
the Autonumber Primary Key field of TblPracticeAreas - they see the
user-friendly field PracticeAreaName.
The Primary Key field may have the value 4 but the user sees South Town in
their table. These sort of Lookup Columns cause some confusion, especially
to beginners.
But what you are describing below *sounds* like a combo box which you have
put into say the main form of a form with a subform or into Single Form

Or do you mean that you have a field in your eg Contacts table which you
want to use to look things up? If yes, then this is not how it works

Do you mean that you have added a combo box to your form and instead of
basing it on a table or query, you have typed some values into your combo
and want to find things in your form based on the values which you have
typed into the combo?


A simple, Wizard-driven way to filter your practice areas is to create a
main form based on your Practice Area Table - and if they aren't in a
seperate table then you and I need to start talking about redesigning that
database :) Your Practice Area table should have a Primary Key field (say
PractID) (often an Autonumber field) which should be the Foreign key field
in your Contact table (a number field called PractID)
Your subform, which should be based on your main contacts table will be
linked to the Main form by this PractID field
You add a combo box to your main form and the wizard (if your tables are
designed correctly) will ask give you the option to look up a value in your
form. This will allow you to turn to the correct page.

If you are thinking 'Silly woman! I already know that. I'm not that much of
a beginner!' then have a look at Allen Brownes Search Criteria database on
how to search on multiple criteria. He has commented the code page so that
you can adapt it to your own database form

http://www.allenbrowne.com/ser-62.html


Evi
 
ok well..... I am a super newb like seriously! so I will try any thing you
suggest.
Im sure my febel attempt is all wrong but let me try to describe better and
then maybe you can correct me.

I have a Table with 12 fields. First Name, Last Name, Phone Number, Fax,
Firm, Address, City, State, Zip, E-mail, Website, Practice Areas, and
Martindale-Hubbell Peer Review Rating.
The Practice Areas Field is like a dro down menu allowing me to check off
multiple values that I defined when creating that field. Now the form, I
created only to make entering in data easy, but the fields in the form are
identical.

My intent is to have users search the records in the database based on the
predefined values in the Practice area fields.

Example: I need to find an Attorney who practices Administrative law. so I
select that value and a list of all matching records pops up.

Now I personally would just use the search box at the bottom of the access
window and type it in, but im trying to make this super easy for the users.

Thanks for all your help and i hope im not confusing the heck out of you!
 
Ah, you don't mean Geographical areas, you mean the sort of law that an
Attorney practices eg Divorce, litigation, corporate

And you are saying that you have just typed the practice areas into a combo
box and you don't have a seperate table listing all the different types of
practices. Ooops!

And Attorneys sometimes have more than one practice area?

The structure you need is

TblAttourney
AttourneyID (primary key_
AFirstName
ASurname
ATelephone number

(note that this table does not contain anything to do with practice areas,
just details about that particualr attourney


TblPracticeArea
PracticeID
PracticeArea (eg litigation, divorce, corporate)
(note that this table contains nothing about the attourney just about the
different practice areas

TblAttourneyPractice
APID (Primary Key)
AttourneyID (foreign key field linked from TblAttourney)
PracticeID (FK linked from TblPractice
MHRating (I would shorten that field name, if I were you, you can always put
the whole caboodle in form and report labels and can put the full thing in
the Description section of the table design.)
I'm guessing that the rating is given to the Attourney *in that particular
field of law* rather than to the Attourney as a whole. If the rating is
given to the Attourney, whatever field of law he practices in, then this
goes in the TblAttourney
any other fields you want, dealing with that attourney when he is involved
with that particular practice area


For your needs, create a main form based on TblPracticeArea,
Add a subform based on TblAttourneyPractice allowing the wizard to link this
to the main form by PracticeID
Open the subform in design view.
Add a combo box to in the subform based on TblAttourney to let you add the
Attourneys who practice in that area.

If you add a combo to the Main form the wizard should give you the option
to Add a combo to find records in your form. Add PracticeID and PracticeArea
to this combo and select Remember this value for later use when that option
appears,
With this, you can select a practice area and your db will turn to the
correct page of your main form, showing you a list of all the attourneys who
are involved in that practice area.

Evi
 
ok, as you advised, I createated the 3 tables Attorney:Table, Practice
Areas:Table, and Attornry Practice Table. But you kinda lost me when it came
to the forms. Can you instruct me on how to cret these forms, becuse I think
what I am doing is wrong.

Thank You so much for your help?
 
Sure, we'll do it with the tables first (you can change to a query later
on)#

First, open the Relationships window and drag AttorneyID FROM Attorney Table
and onto AttorneyID in Attorney Practice.
Choose to Enforce Referential Integrity
Drag PracticeID FROM Practice Areas and onto PracticeID in Attorney
Practice.
Again, enforce Referential Integrity.
You have now created a proper Relational Database.
Save and close the Relationships window


With your tables closed, click on Practice Areas Table to select it,
without opening it.

(I hope that colon is a typo, you don't want symbols in table or field
names - the ideal is to stick to shortish but descriptive names for both
with no symbols or spaces - its far easier for you when you start coding -
a good naming convention is call all Tables Tblxxx, all Forms Frmxxx etc.
Don't be concerned about what they look like in the database window - no-one
need ever see that)

Go to Insert, Form

Choose Autoform, Columnar (this gives you a single form based on Practice
Areas Table )

Save and open the form in Design View shrinking it enough so that you can
see it and the database window. Drag the Details area down so that it
becomes a bit taller.

Click on the Attorney Practice Table in the main database window, and,
without opening it, drag it onto the Main Database window.

The Wizard should kick in here and spot that the two forms have a common
key - PracticeID - and offer you the option ot create this link in a line of
text in a box, the relevant part of which usually disappears off the edge of
the box!!! (don't panic if the Wizard doesn't play ball, everything can be
done without the Wizard)

Give your newly created subform the same name as your sub

You now have a form with a linked subform, You won't see the Attourneys
yet, just AttourneyID

Save and close.

Type an entry into your Attorney Table, just to get you started

Open the new Subform in Design view.

On the Toolbox toolbar (or whatever monstrosity Acc2007 has, to replace it)
Click on Combo Box and then click on the Detail section of the Subform. The
wizard should kick in. Tell him that you want to base your combo on the
Attorney Table. Choose the fields AttorneyID and AttorneySurname for now.
The Wizard will hide the AttorneyID field which, although it won't be
visible in the combo, will be the 'Value' of the combo.

Choose to 'store the value' of the combo in AttorneyID

Save and open the form in normal view. Drag closed the fields you don't want
to see. Open your main form to see the results.

write back if you get stuck, stating which bit didn't work.

Evi
 
Ok I did everything as instructed, as of now the subform shows ID, Attorney
ID, and a combo box. both the attorney id and combo box are drop down menu.
So whats next?


I really do appreciate this Im learning quite abit from you!
 
Hi Jordan
See if the form works by entering some data. Add some Attourneys to
TblAttourney just to get you started.

Can you add/edit the Practice Area details to the main form? When you have
done this, try using your combo (the one based on TblAttourney) to add new
records to the subform. Does this work? Does the subform filter to show only
the Attorneys which are in that Practice Area as you turn the main form from
1 page to the next?

Open the main form in Design View. Add a combo box. Because your main form
has a primary key and shows unique records, the wizard should present you
with the option to Find a Record on My Form...
Choose this option, Choose PracticeID and Practice. On the last 'page'
choose to 'Remember the Value for Later Use'
You will now be able to use the combo to turn to any page of your form to
see a list of attorneys in that Practice Area.

One more thing
Is 'ID' the name of the Primary key field of TblPracticeArea? Is it the only
PK field called ID? If you have allowed Access to call more than one field
ID then you will find this very confusing as your database continues and I'd
recommend changing that name to something short but explanatory eg PracID,
before you continue with your forms - its worth doing it at this stage even
if it means scrapping the form which you have created.

Evi









Jordan said:
Ok I did everything as instructed, as of now the subform shows ID, Attorney
ID, and a combo box. both the attorney id and combo box are drop down menu.
So whats next?


I really do appreciate this Im learning quite abit from you!
Click to expand...

Sure, we'll do it with the tables first (you can change to a query later
on)#

First, open the Relationships window and drag AttorneyID FROM Attorney Table
and onto AttorneyID in Attorney Practice.
Choose to Enforce Referential Integrity
Drag PracticeID FROM Practice Areas and onto PracticeID in Attorney
Practice.
Again, enforce Referential Integrity.
You have now created a proper Relational Database.
Save and close the Relationships window


With your tables closed, click on Practice Areas Table to select it,
without opening it.

(I hope that colon is a typo, you don't want symbols in table or field
names - the ideal is to stick to shortish but descriptive names for both
with no symbols or spaces - its far easier for you when you start coding -
a good naming convention is call all Tables Tblxxx, all Forms Frmxxx etc.
Don't be concerned about what they look like in the database window - no-one
need ever see that)

Go to Insert, Form

Choose Autoform, Columnar (this gives you a single form based on Practice
Areas Table )

Save and open the form in Design View shrinking it enough so that you can
see it and the database window. Drag the Details area down so that it
becomes a bit taller.

Click on the Attorney Practice Table in the main database window, and,
without opening it, drag it onto the Main Database window.

The Wizard should kick in here and spot that the two forms have a common
key - PracticeID - and offer you the option ot create this link in a line of
text in a box, the relevant part of which usually disappears off the edge of
the box!!! (don't panic if the Wizard doesn't play ball, everything can be
done without the Wizard)

Give your newly created subform the same name as your sub

You now have a form with a linked subform, You won't see the Attourneys
yet, just AttourneyID

Save and close.

Type an entry into your Attorney Table, just to get you started

Open the new Subform in Design view.

On the Toolbox toolbar (or whatever monstrosity Acc2007 has, to replace it)
Click on Combo Box and then click on the Detail section of the Subform. The
wizard should kick in. Tell him that you want to base your combo on the
Attorney Table. Choose the fields AttorneyID and AttorneySurname for now.
The Wizard will hide the AttorneyID field which, although it won't be
visible in the combo, will be the 'Value' of the combo.

Choose to 'store the value' of the combo in AttorneyID

Save and open the form in normal view. Drag closed the fields you don't want
to see. Open your main form to see the results.

write back if you get stuck, stating which bit didn't work.

Evi
 
Back
Top