Many problems...very confused! Need help!!!

[eurojourney]

Hello,

Ok, I am hitting a wall with my simple database that I need to create. I need to register in my real estate company clients who might be associated with a broker, many brokers or no brokers. Also, I need to register brokers who may have one client, many clients or no clients at all. The following is the relationship I came up with my tables, having two major tables, Client and Broker tables and a Registration Table to take care of the Many-to-Many relationship between the two.

Initially, I did not use the Registration table and instead created a lookup field in my Client table to be able to pick a broker and associated with the client. Several people have said this is not recommened, that I need to use my Registration table instead. Furthermore, I have read too that I need to create a Query using the fields of all the tables I need using the Registration table Primary Key. Now, I tried this and I am not sure how to do it, which fields I can add, if I can add the name, last name, company, etc. of the broker and the client into the same query (I get an error when doing this which I'll post below) or I I should only include the primary key of the registration table and the Client and Broker table or what.

Basically what I want to achieve is to have a form where the receptionist can enter first all the Broker's information (that would be one form, very straight forward - no problem). The second form, which is the Clients form, seems to be more tricky because it would be quite similar to the Broker form but in addition, it would include a combo box where it will list all the brokers registered already and from which I would pick the correct one or correct ones if more than one that are associated with that particular client. This I don't know how to achieve, I don't know how to see in two tables in one form - apparently I need to do the query but that I can't get to work either. I am a little lost and I know this is basic stuff but I think I might just need a little push in the right direction and perhaps I can take it from there. I have spent many hours already, read quite a bit, and had it all working between my Client and Broker form and my lookup field in the Client table but now am going backwards now that I found out this is not a good idea and want to do it right from the beginning.

Please help!!!

(Here is the error message when doing the query: "The sql statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the furst join then include that query in your SQL statement.”

Here is the relationship chart of my database...I added the Registration ID field in the Client and Broker form but I don't know if I really needed or if I should link it with the registration table or what...Thank you again for your help!!! - Click link for Relationship chart.


http://motojourney.com/presentation1.jpg

 

[HiTechCoach]

Hello,

Ok, I am hitting a wall with my simple database that I need to create. I need to register in my real estate company clients who might be associated with a broker, many brokers or no brokers. Also, I need to register brokers who may have one client, many clients or no clients at all. The following is the relationship I came up with my tables, having two major tables, Client and Broker tables and a Registration Table to take care of the Many-to-Many relationship between the two.

Initially, I did not use the Registration table and instead created a lookup field in my Client table to be able to pick a broker and associated with the client. Several people have said this is not recommened, that I need to use my Registration table instead. Furthermore, I have read too that I need to create a Query using the fields of all the tables I need using the Registration table Primary Key. Now, I tried this and I am not sure how to do it, which fields I can add, if I can add the name, last name, company, etc. of the broker and the client into the same query (I get an error when doing this which I'll post below) or I I should only include the primary key of the registration table and the Client and Broker table or what.

Basically what I want to achieve is to have a form where the receptionist can enter first all the Broker's information (that would be one form, very straight forward - no problem). The second form, which is the Clients form, seems to be more tricky because it would be quite similar to the Broker form but in addition, it would include a combo box where it will list all the brokers registered already and from which I would pick the correct one or correct ones if more than one that are associated with that particular client. This I don't know how to achieve, I don't know how to see in two tables in one form - apparently I need to do the query but that I can't get to work either. I am a little lost and I know this is basic stuff but I think I might just need a little push in the right direction and perhaps I can take it from there. I have spent many hours already, read quite a bit, and had it all working between my Client and Broker form and my lookup field in the Client table but now am going backwards now that I found out this is not a good idea and want to do it right from the beginning.

Please help!!!

(Here is the error message when doing the query: "The sql statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the furst join then include that query in your SQL statement.”

Here is the relationship chart of my database...I added the Registration ID field in the Client and Broker form but I don't know if I really needed or if I should link it with the registration table or what...Thank you again for your help!!! - Click link for Relationship chart.


http://motojourney.com/presentation1.jpg

You probably should only have a single table in your query. For form that I use for data entry, I rarely have more than one table in the form's record source.

You are making progress. To help you get bast this hurdle, are you would be willing to attach a copy of your database? I will be gald to create a sample form for you. This hopefully will help you see how to do what you want.

 

[eurojourney]

Hello,

Here is a copy of the file with the database. The data is made up for now just to test if it works or not so it can be changed if needed, I don't mind. Let me know your thoughts!

Thank you for showing interest in helping me!!!

Jorge/Eurojourney

 

[HiTechCoach]

All I did was take a few minutes to throw together a couple of sub forms to give you an idea.

I would recommend that you look into using the Tab control to help with the form layout.

Some important thins that really jumped out:

1) Again, avoid spaces in object names!

2) All the phone numbers should be considered repeating fields which violates the rules of data normalization and should be avoided. It would be much better to have then is a child table with a one-to-many relationship.

I normally use a "Contact Methods" table that holds phone numbers, email addresses, web sites, etc.

Example:
PeopleContactMethods table
- PeopleID - link to people table
- ContactMethodType - Home Phone, Home fax, Bus Phone, home email, etc ...
- ContactMethodData (text - length 255)

This will allow you to create any combination of contact methods without lots of blank fields in a record. It is much easier to add additional types as needed without any table design changes.


3) The Broker and client tables really should be combined into a single people table. As you have already noticed, they are almost identical. That is a major clue that they should be combined.

Following your logic of splitting the tables into clients and broker, why would you not also want to split the broker table intp BrokersMale and BrokersFemale and the client table into ClientsMail and ClientsFemale. Now you have four basically identical table where the table name is part of the data to describe the record. Wait a moment, why not break the tables apart again by Company name? Doe not really make sense does it. It make no mode sense to do than it is to split out Clients and Brokers into separate tables. The gaol should be just the opposite. Also what is a Broker becomes a client? Now you have to duplicate the data! That is exactly what my recommendations are trying to avoid.


Once you understand these concepts, you can apply it to other areas of your database.

 

[eurojourney]

Hello HiTech,

I agree that it would be easier perhaps to have just one table with contacts for both brokers and clients but how can I associate them in a given record for a client which broker, if any, is involved? Do I need to create a query to pull contacts for the Brokers only and then do a subform that will look into that query to select the broker associated with the client similar to the way you did it? Also, I understand what you said about the "peoplecontactsmethod" table but how can I incorporate that into the form in a way that is easy to follow similar to what I did? (Remember, this is to be used by a receptioninst with no clue of Access and little in general about computers - except for updating her facebook profile!) Sorry with these questiosn, may be quite simple and basic but I really don't know. About the fields, I want to be able to export this information rather easily to Outlook, that is why I used the exact name of the fields use in Outlook so when it comes to "mapping" the files when exporting to Outlook it is automatically done. Is there a better way to go about that? Also, are subforms the only way to enter information in two or more tables from one form and if so, do you recommend eliminating the "record navigator" tool on the bottom to avoid confusion to unfamiliar Access users (not that I am too familiar with it, hehehe)? Please let me know when you get a chance.

Thank you for taking time in answering my questions, is highly appreciated.

Jorge/eurojoruney

 

[mwtex]

Have you tried a subform or multi-select?

------------

 

[eurojourney]

I am not too familiar with multi-select combo boxes...what are the advantages, what would it allow me to do in my case.

 

[HiTechCoach]

Hello HiTech,

I agree that it would be easier perhaps to have just one table with contacts for both brokers and clients but how can I associate them in a given record for a client which broker, if any, is involved?

Basically jsut like I did with the example sub form, it will just use the same table for both. basically you are relating records together in the same table.

Also, I understand what you said about the "peoplecontactsmethod" table but how can I incorporate that into the form in a way that is easy to follow similar to what I did?

Since you were not using a sub form, you will need to rethink your design to work better with a relational database.

Note: Outlook is not a relational database so do not use it as a model.

(Remember, this is to be used by a receptioninst with no clue of Access and little in general about computers - except for updating her facebook profile!)

Using any type of database type application (Accounting systems, sales/lead management, CRM, etc) require a lot more training and human decision making to make sure that the data is entered correctly.

Training really is a must and should NOT be avoided!

You will also need to create good manuals and/or help system.

Sorry with these questiosn, may be quite simple and basic but I really don't know. About the fields, I want to be able to export this information rather easily to Outlook, that is why I used the exact name of the fields use in Outlook so when it comes to "mapping" the files when exporting to Outlook it is automatically done. Is there a better way to go about that?

Why would you want to duplicate the data into Outlook?

How are you going to keep them synced?

Also, are subforms the only way to enter information in two or more tables from one form

Yes.

... and if so, do you recommend eliminating the "record navigator" tool on the bottom to avoid confusion to unfamiliar Access users (not that I am too familiar with it, hehehe)?

Yes. Otherwise you will need to create your own.

Again, using any software application requires more skills and training. Even Facebook requires learn. See comments above on training.


Any tool is only as powerful/useful as the person's skills that is using it.

Software is only a tool.

 

[eurojourney]

Hello HighTechCoach,

Thank you again for taking time in answering my questions. I am starting to have a better idea of what to do next. I will revamp my forms and tables to follow your advise to the best of my abilities. By doing this I will eliminate any "many-to-Many" relationship I had before. This is my plan now:

One form to enter contact information only. This form will have a combo box to choose between Client or Broker. I'll try to integrate your idea of having a separate contact information table (Child table - PeopleContactMethods table) similar to how you created the subform in the Database you fixed. By the way, how can I get the database to generate a new entry like you did in the subform so I can continue entering data categories and associated data based on my requirements for a given record.

Once I have my contact information form ready that can be used for brokers as well as clients, I am going to have a query to discriminate based on brokers only so I can associate that broker to a client on another form using also subforms. In that form I will also include all the information pertaining to the client's visit similar to the way I have it in my current for on the bottom.

What do you think? is this the right approach now? I wanted to keep the name of the field the same as Outlook's because if I want to export to Outlook is easy when it comes to mapping the fields - maybe not worthwhile?

I will work on this and post it again for your review if you have time. Thank you again.

 

[eurojourney]

Hello,

I am trying to work on this again. This is my new relatioship chart based on your comments. What do you think? Also, I am having a little bit of an issue setting up my subform. when I enter the data it goes into both tables but it looks like there is no relation between the data because when I do a query combining the information of both tables the query comes blank. I have the master link and child link set to People ID. any ideas? Please let me know...should I set my master link to a control and have a control in the main form "hidden" for the People ID data and link that to the master link? What about the child link? should that be PopleContactMethods ID? I don't think I even have that choice.

Thank you!!!

 

[LAM]

All I did was take a few minutes to throw together a couple of sub forms to give you an idea.

3) The Broker and client tables really should be combined into a single people table. As you have already noticed, they are almost identical. That is a major clue that they should be combined. ...

Hope it is okay to jump in to this thread. I am having the exact issue of trying to relate one company with another company. One example is a parent company that can have many child companies. I need to show the two companies and how they are related, Company 1 is the Parent Company, Company 2 is the Child Company. (There are other relationships as well, mergers, purchases and sales.)

You mention combining the tables into a single table, which I am trying to accomplish. But how can you associate one company with one or more other companies in the same table?

Any help would be appreciated.

LAM

 

[HiTechCoach]

Here is a great example on how to properly design (normalize) a database to handle contact information.

See:

Contacts -- Names, Addresses, Phones, eMail, Websites, Notes

This example also shows how to set up a relationshisp between contact records in the same table.

 

[LAM]

Here is a great example on how to properly design (normalize) a database to handle contact information.

See:

Contacts -- Names, Addresses, Phones, eMail, Websites, Notes

This example also shows how to set up a relationshisp between contact records in the same table.

Thanks HiTechCoach,

Great resource, this is exactly what I am trying to accomplish.

LAM

 

[HiTechCoach]

Thanks HiTechCoach,

Great resource, this is exactly what I am trying to accomplish.

LAM

LAM,

Glad that was helpful.