Simple Database (atleast I *think* it should be)

[CloudFuel]

I am an apartment locator (real estate agent) and contact many people, so I've gone to a paperless form and have been using excel to keep track of all of their information. I'm seeing that this really isn't the best approach since I cannot easily keep track of this information or easily run reports.

I've started building a database and I think I'm missing an important link. Essentially what I want is a table that contains all of my clients information and since I will have clients with the same name I'm using an auto-numbering ClientID field as my primary key. I also have a table that I'm hoping will be able to keep a log of every contact I have with these clients.



Ultimately I just want to be able input a client's information (which will never really change) and then add details of every contact I have with them while still keeping the previous contact details, so I would have a log of all contacts over time.

I figured that I would need 2 tables and have the same primary key (ClientID - autonumbered) in each table so it would link them together.

I am definitely a beginner with Access, literally just picking up a for dummies book and teaching myself, so any advice is MUCH appreciated! Thanks!!!

[pbaldy]

Your first table looks okay, but the second would have ClientID as a foreign key, not as a primary key (it would typically have its own autonumber field as a primary key). This is what's called a one-to-many relationship; there can only be one instance of a given ClientID in the client table, but many in the "contacts" table. A common way of presenting this type of data is a form with a subform. The form is bound to the clients table, the subform to the contacts table. Master/child links keep them in sync with each other.

[CloudFuel]

Thanks for the reply!

That makes sense to a degree, so I would have two tables like below -

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

Table:Client

Field:InitialContactDate
Field:ClientID <--- Primary Key
Field:LeadSource
Field:FirstName
Field:LastName
Field:Email
Field:Phone
Field:AltPhone
Field:Area
Field:Size
Field:Price
Field:MoveDate
Field:LookDate
Field:OccupantAmount
Field:MovingReason
Field:CurrentHomeLikes
Field:CurrentHomeDislikes
Field:BrokenLease
Field:Eviction
Field:Misdemeanor
Field:Felony
Field:Amenities
Field:Properties
Field:ClientNotes


Table:Contact

Field:ContactID <---Primary Key
Field:ClientID <---Foreign Key
Field:FirstName <---Foreign Key as well?
Field:LastName <---Foreign Key as well?
Field:ContactDate
Field:ContactTime
Field:ContactType
Field:ContactNotes

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

Now by doing this, would it allow me to easily pick the client I want to add contact notes to? I doubt I'll be able to remember all of their ContactID's, so I'm really looking for something that will allow me to do 3 things. Create and edit Client Data and then also easily add contact notes as it happens. Then I would want to be able to run reports on say the MoveDate or Price or Area or IntialContactDate, etc...

Thanks again!

[jzwp11]

There is no need to repeat the client's name in the contact table. You only need it once, so your contact table should look like this:

Table:Contact

Field:ContactID <---Primary Key
Field:ClientID <---Foreign Key
Field:ContactDate
Field:ContactTime
Field:ContactType
Field:ContactNotes

Looking at your client table, I think there may be some fields where it might be beneficial to break out the data into other related tables as well.

For example, these 2 fields imply multiple items
Field:CurrentHomeLikes
Field:CurrentHomeDislikes

If a person's preferences include 3 bedrooms, 2 full baths, back yard etc. That would be best to have in a related table with each specific item as a record. You could actually have a field that distinguishes likes from dislikes within that table. I would think also that you might repeat the same item for multiple clients; if so, it would be best to have a table of these items and join them to the client. An example of that table structure would look like this:

tblClientPreferences
-pkClientPreferenceID primary key, autonumber
-fkClientID foreign key to your client table
-txtPreferenceType (i.e. like or dislike)
-fkItemID foreign key to tblItems

tblItems
-pkItemID primary key, autonumber
-txtItemName

Then looking at the following fields, I wonder if these should be records in a related table of client "issues"

Field:BrokenLease
Field:Eviction
Field:Misdemeanor
Field:Felony

[CloudFuel]

My only issue with that is that I want to be able to enter ALL client data in one form... and then all client contact info in another form. Can I do that with multiple tables?

Would I be able to view a log of all contacts with said client that way?

I'm having a new issue now... I tried doing the primary key / foreign key thing, but it's saying that I can't have multiple autonumbers in the same table (I had ClientID autonumber foreign key & ContactID autonumber primary key within the Contact table). I'm basically trying to make it to where I can have either a drop down to choose the client I want to include contact info on or be able to type the name in and it finds the client, don't want to have a possibility of spelling the name or whatever wrong and then not having the stuff match up... make sense?

[pbaldy]

In your contacts table, ClientID is a Number/Long Integer, not an autonumber. You can have combos that display both ClientID and the name, allowing the user to select either, but as jzwp11 mentioned, you should only store ClientID in the contacts table.

[jzwp11]

In any relational database (not just Access), the table structure is of utmost importance. Forms should be designed around the tables not the other way around. With that said, you can still do this with 1 form. That form will have many subforms to add the related data.

I tried doing the primary key / foreign key thing, but it's saying that I can't have multiple autonumbers in the same table

Yes, only 1 autonumber field in a table. The autonumber should be used for the primary key field; the foreign key will need to be a long number datatype (it is the equivalent datatype to the autonumber datatype).

Having the combo box to select a client is not a problem. You need to work out your table structure first, before working on forms.

It might be beneficial for you to read up on normalization which is the main principle (i.e. "the rules") for setting up your tables. This link has some info on normalization.

You might also find the tutorials on this site helpful.