 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008Searching records though a multi-value look up column
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. Is this 'Lookup column' in a table or in a form? What is your table structure? Evi > 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! > Is this 'Lookup column' in a table or in a form? What is your table > structure? [quoted text clipped - 6 lines] > > 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. 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 > 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, [quoted text clipped - 15 lines] > > > 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. 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 well..... I am a super newb like seriously! so I will try any thing you > suggest. [quoted text clipped - 19 lines] > > Thanks for all your help and i hope im not confusing the heck out of you! 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? > Ah, you don't mean Geographical areas, you mean the sort of law that an > Attorney practices eg Divorce, litigation, corporate [quoted text clipped - 78 lines] > > > > Thanks for all your help and i hope im not confusing the heck out of you! 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, 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 [quoted text clipped - 85 lines] > > > > > > Thanks for all your help and i hope im not confusing the heck out of you! 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 > 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! 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 "Jordan" <Jordan@discussions.microsoft.com> wrote in message news:D3C381FF-BA18-40AB-A078-1D3D1A0F09CF@microsoft.com... > 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. > [quoted text clipped - 4 lines] > > > > 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! > > [quoted text clipped - 9 lines] > > > > (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 > > [quoted text clipped - 3 lines] > > 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. > > [quoted text clipped - 3 lines] > > news:C5EFD1F8-16B8-45AC-9C1A-F47E1D612F65@microsoft.com... > > > 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! If you dont mind please e-mail me JordanTSnow@Yahoo.com > Hi Jordan > See if the form works by entering some data. Add some Attourneys to [quoted text clipped - 218 lines] > > > > Thanks for all your help and i hope im not confusing the heck out of > you!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.