Suspiciously basic question about how to enter data in 1 form for 2 different tables

[eurojourney]

Hello,
I am new to MS-Access and this site has been great in answering some of my questions. I know that my questions are rather basic for the most part but so far I think I have managed to have my database close to working conditions. However, someone pointed out in this site that using lookup fields are not a good idea; thus, I have the following situation:
I am in real estate working for a developer and I need a way to register clients and brokers in a simple database. Some clients have no brokers, one broker or more than one broker associated to them. Some brokers have no clients, one client or many clients associated to them. I want to keep track of the relation between brokers and clients, their contact information and details of the clients’ visit to my sales office.
I created two main tables with the contact information for brokers and clients, i.e. Broker table and Client table. The Client table also has information regarding the clients’ visits such as who was the sales associate that took care of the client, the date of the visit and the property they showed interest the most. In its current iteration, it also has a lookup field that pulls the fist name, last name and name of the company of the broker if there is a broker involved in the deal. Also, I have other secondary tables employed in the Client form that are used to select by means of combo boxes the specific choices for the Sales Associate involved in deal, name of property chosen by client and such to keep everything consistent and avoid data entry mistakes by the receptionist. I also have a separate Broker form where any broker that visits or contacts our office can be registered so I can place him or her in an email list or mail-out list by exporting the database to Outlook if I want it to. Finally, based on what I read thus far, I created a Registration table to relate the Client table and Broker table since there is a many-to-many relation between the two. I included in that table the Primary Key of the Registration table, the Primary Key of the Client table and the Primary key of the Broker table. I think I have structured the database properly so far, right?

(See below for rest of post)

 

[eurojourney]

Now, since I’ve been told that having lookup fields are not necessarily a good practice, I need to find a way to relate my two main tables, Client and Broker, presumably by using my Registration table so I can use my Client form and choose a broker associated with the client in the same manner as I have it now with the combo box / lookup field setup. In other words, in the Client form I want to be able to enter the contact information of the client (i.e. Name, Address, phone, email, notes, etc) choose the date of the visit, the sales associate involved, name of property and most important of all, the name of the broker or brokerS related to the client by means of using, I suppose, a pull-down window/combo box where I could type the first few letters of the last name of the broker and quickly choose the right broker or brokers involved in the deal (another problem with the way it is now is that with the combo box and the lookup field I can check the boxes next to the brokers I want to relate the client with but I cannot type the first letters of the name I am interested in locating amongst the hundreds of brokers I have registered to find the right one so it becomes rather cumbersome to find a broker in the small pull-down window of the combo box).
So, the question I guess is how do I related these two tables, Client and Broker so I can within one Form (Client form) select by using a combo box or similar the right First Name/Last Name/Company record for the right broker in the Broker table and successfully associate Broker and Client together.
My other two questions regarding the same subject would be how I can use the combo box as a pseudo search tool where by typing the first letters of the last name of the broker within the pull-down window, it would point to those names with those letters in the last name that I typed. I also must be able to select more than one broker. The second concern is to use a check box to select whether there is a broker or no broker involved in the transaction. As it is now, I have a check box that answers the questions “Is there a broker involved?” – if checked the answer is “yes” otherwise is “no” – and if it is not check, ideally it will keep the combo box with the names of the brokers “greyed-out” so the receptionist does not make a mistake of choosing a broker by mistake when there is no need.
I know this was a lengthy explanation but I hope someone can help me with these issues. Again, thank you for all the great information I’ve received in this site.

Jorge/Eurojourney

 

[Steve R.]

You need to read-up on normalization.

Your various tables need to be "linked" by a primary key, usually a long integer or auto-number field used only for that purpose. To have the form work with the various tables you will need a query as the data source for your form.

You may want to look on Amazon.com for some introductory books on using Access.

 

[eurojourney]

I have primary keys in all my tables and everything works and have the proper relations between them - i.e. one-to-many for the small tables feeding the combo boxes in the client's table, one-to-many for the client table and registration table, one-to many for the broker table and the registration table, which together with the Registration table creates the many-to-many situation. I will read about normalization as you suggested.

Any other tips are welcome!!!!

Thank you,

Jorge/eurojourney...and yes, when all else fails, I will read the manual as you said...

 

[eurojourney]

One question, when I do a query for the two tables using the registration table, do I just use the Primary key of the registration table or do I have to include also the primary key of the registration table as well as the Client and Broker table...Thank you.

Jorge/Eurojourney

 

[Steve R.]

You would use the primary key (PK) of the registration table as your "base" PK. The other tables would contain a long integer field that contains the same number as the PK in the registration table.